Hi
Mental block. Using Excel to cost out a project. One example, weed contrail fabric. Spreadsheet works out that I need 1.25 rolls at £7.99, so cost is £9.99 (rounded up).
In reality I'd have to buy 2 rolls, so cost would be £15.98.
I can format the cell to 'no decimal places', which in this case shows 1 roll & still £9.99.
How do I get Excel to work in whole numbers, rounded up?
use the CEILING() function.
ceiling(a1,1)
Fell off the back of a 'plane did it ;-)
Use the =roundup(number, decimal_places) function.
e.g.
A1 contains 1.5 A2 contains =roundup(a1,0)
=(B2*IF(INT(A2)=A2,A2,INT(A2)+1))
Where cell 'B2' is the cost, and cell 'A2' is the quantity
If you're doing that, just spray the roundup on the weeds
I'll fetch my coat. :-)
add one half and round down.
err, no, 1.25 + 0.5 is 1.75. Round down is still 1, he wants 2. Add 0.5 and round (up or down) is what you meant.
He should add 0.5 and round to nearest integer (not sure possible in excel) or, easier, add 0.99 and truncate.
In Excel, number in A1 (say) - B(1) is set to "=INT(A1+0.99)" then use B1
John Rumm has given the answer, short and easy.
Use the =roundup(number, decimal_places) function.
e.g.
A1 contains 1.5 A2 contains =roundup(a1,0)
Yes, that actually changes the value rather than just formatting it. The ceiling function is a new one on me, but I guess it does the same thing
Ah yes, same effect, same number of cells but somewhat more elegant!
I stand corrected.
That is the way to round off, rather than up (as in add 0.5 and truncate to an integer)
Have something to add? Share your thoughts — no account required.
Ask the community — no account required