Excel Spreadsheet Help ... Rounding Up

[joggerjayne]

Can anyone tell me how to tell a cell to round up?

Suppose my simple formula is ...

Cell C = Cell A x Cell B

If the number in Cell B is not a whole number, I would like Cell C to round up to the next whole number.

[factor-fiction]

So you would for example round 2.1 to 3?

[Buenchico]

http://spreadsheets.about.com/od/roundingnumberfunctions/ss/2011-07-04-excel-2010-round-up.htm

[andrew1707]

Right click on the cell and choose Format Cell. Then choose number and you will be given an option of how many decimal places yo want.

A

[Buenchico]

Andrew1707's method will round to the nearest integer (assuming 0 decimal places is chosen), rather than always rounding up. You need to use the 'ROUNDUP' command, with 0 decimal places. (See link).

[joggerjayne]

Thank you, guys.

Yes, I would have to round up 2.1 to 3

If I need 2.1 units of the item in Cell C, then I have to order 3 of them. Anything over 2 would have to become 3.

[Retrochic]

Not quite sure what you mean here. Do you want cell C to round up the final value of AxB? or do you want the cell B rounded up before the calculation is done? Andrews answer is correct . You can formulate any cell to round the number by highlighting the cell right clicking and choosing 'number' and 'no decimal place.

[Old_Geezer]

I would also vote for

=Roundup(2.1,0)

[factor-fiction]

So is it cell B you want to round up, cell C or both?
Can you give an example of what the values (unrounded) could be for cells A, B and C and then which figures you'd want to round up?

[Buenchico]

More examples here:
http://www.techonthenet.com/excel/formulas/roundup.php

[andrew1707]

As usual, Chris is right. If you want to always round up (rather than just round up or down) you need to use roundup.

And to think I'm always telling my kids to read the *** question properly!!

A

[Old_Geezer]

Oh ok you have a calculation too.

=Roundup((cellA*CellB),0)

The reason being you are rounding up the final answer to a whole number of units. Not changing the view. That said in practice it may make no odds to you.

[joggerjayne]

Okay, so ...

Suppose C are printed programmes, in boxes of 100

I have five events (Cell A1 = 5)

Each event needs half a box of programmes (Cell B1 = 0.5)

At the moment my formula in Cell C1 is ... =A1*B1

Which tells me to order 2.5 boxes

But I can't order half boxes.

For Cell C1 to be useful in further calculation, I need it to automatically round up to the next whole number, each time I change the numbers in A1 and B1.

I've tried ... =ROUNDUP A1*B1

But that hasn't done it.

[joggerjayne]

Just trying OG's formula.

[Buenchico]

The ROUNDUP command needs to be told the number of decimal places that you want to use when rounding up. (In your case, since you want to round to an integer, you need to set that to zero).

It might be simplest to do the unrounded calculation first and then round afterwards. So C1=A1*B1 and D1=ROUNDUP(C1,0), where D1 is the rounded answer you require.

[factor-fiction]

So in your example you'd want an answer of 3. Roundup should work, but you'd need the brackets to show it's (A1*B1) that you are rounding up. You may have just rounded up A1 and then multiplied it by B1

[andrew1707]

Try using the syntax as shown by OldGeezer =Roundup((cellA*CellB),0)

I just tried it and then used the output for a further calculation and it used the rounded number rather than the un-rounded number.

[joggerjayne]

andrew, thanks, I haven't yet tried to incorporate it into a further calculation.

But, if it works using the rounded number, I'll go with OG's formula.

Thanks, guys ... you've all been fab ... big HUG to everyone x x x

[Old_Geezer]

Cheers, but it was just the one Chris has previously highlighted, just with some data filled in.

[joggerjayne]

It was, OG ... but you have me the "spoon feed" version of the formula that Chris found.

And Chris's answers are always so brilliant. Giving Best Answer to Chris would be like awarding the Ballon d'Or to Christiano Ronaldo ... an obvious choice, but a bit of a kop out.

[Buenchico]

I'm feeling all embarrassed now!
;-)