In Excel if you need to round up to the next whole number in a formula - how do you do it?

[KatyMac]

so 22/8 = 2.75 I want it to say 3; the formula looks like this

(((a1/8)-2)*31)=???

Roundup is the function you need I think.

That's what I thought but it's asking for 2 fields

Don't you just change the "Number" format of the cell so it doesn't show any decimal points?

It's not really rounding up, since the computer still "knows" the answe is 2.75 (e.g. If you use that cell as an input to a different formula) ... But the displayed answer you see is 3.

You don't need to include it in the formula - just format the cell to have no decimals. You can do this quickly using the icon on the tool bar (there are 2 together to increase and decrease the decimal points - they have an arrow and a decimal number). You can also right click on the cell select format and decrease the decimal there.

HTH

The second field in the function is The number of digits you want it to round to. You want to put in 0.

No

I am calculating a number within a formula

so if a1=24

(((a1/8)-2)31)=??? becomes 24/8-231 = 31

but if a1 =22

I want it to be

22/8 = 2.75 which is really 3 so ((22/8)-2)*31 = 31 not 0

Maybe?

Sorry I meant to say thanks for the help & that I am getting frustrated & thinking about throwing computer

I didn't mean to be rude

Try =Roundup((((a1/8)-2)*31),0)

I have as far as:

=(((ROUNDUP((C4/8-2),2)*31))) but it is giving a -ve number & I'm sure it shouldn't

I'm not near a computer do can't check that it works though!!

Just change cell format to no decimal points. No need to faff with formulas

Ah I see what you mean.

Yes - use the round up function - I would break the formula up into a couple of steps

So a1 is 22
B1 = a1/22
C1 = roundup (roundup,0)
D1 =(c1-2)*31

Then hide the b and c columns

Sorry not roundup,0 ..... Roundup (b1,0)

=ROUNDUP(((C4/8)-2)*31,0) is that the same? I'm going goggly eyed

.... It will give a -ve answer if the input is less than 16

Hadn't thought of hiding (it's rows but I guess it will work)

Yes, you have one less set of brackets than in my formula, but it should work in the same way. Did it work?

any -ves would need to be rounded to 0 - how do I do that?

no sorry it's -ve I was hoping for 0 (at that point)

Sorry last message was to trixymalixy

Put =max(round etc,0)

would

=round(2.75,0)

not do (where 0 indicates the numebr of decimal points)?

oh no you always want to round up - so 2.1 would be 3, yes?

=max(ROUNDUP(((C4/8)-2)*31,0),0)

WOW - streamers and celebrations

trixymalixy that was right - Thank you

Thanks so much to everyone

Himalaya the hiding columns will help me with another problem thanks