Rounding problem
Posted by Stefanie on September 21, 2000 7:33 AM
I need a formula that will round up if the result is greater than 3 and round down if the result is less than 3. Can anyone help?
Posted by Tim Francis-Wright on September 21, 2000 8:09 AM
I'll assume that A1 has the original formula.
If you need to round to the nearest whole
number, use in B1
=IF(A1<3,ROUNDUP(A1,0),ROUNDDOWN(A1,0))
Of course, if the formula in A1 is short, you
could modify A1 to have the IF(..., ROUNDUP, ROUNDDOWN) logic instead.
HTH
Posted by Stefanie on September 21, 2000 8:25 AM
That worked ...Thank you! Now I have another problem...the user wants it to round up to the nearest $5?? eg. 53 would round to 55, 62 would round to 60.
How could I do that?
Posted by JAF on September 21, 2000 9:02 AM
Assuming that the number you are rounding up has no decimal places (i.e. an integer), the following will work...
=IF((RIGHT(A1,1)*1)<3,A1-(RIGHT(A1,1)*1),A1+(5-(RIGHT(A1,1)*1)))
JAF
Posted by Tim Francis-Wright on September 21, 2000 10:50 AM
To round up to the nearest $5, use
roundup(a1/5,0)*5
To round 53 to 55 and 62 to 60, use
round(a1/5,0)*5
[You could also use the Analysis Tool Pak
function mround(a1,5) to round to the nearest $5. Unfortunately, MROUND doesn't have a succinct ROUNDUP analog.]
Posted by MJ on October 05, 2000 11:57 AM
=IF((RIGHT(A1,1)*1)<3,A1-(RIGHT(A1,1)*1),A1+(5-(RIGHT(A1,1)*1)))
I am trying to use the advice given, the problem I am running into is that there is already a Sum formula in the cell. Is there a way to total the sum and run a roundup function...after it has been totaled. What I have is a spreadsheet that is totaling dollar amounts from other cells on the page, then I would like each total to roundup to the nearest $5. Thanks in advance for any help.
Posted by Celia on October 05, 2000 5:40 PM
MJ
To round up to 5 :-
=CEILING(A1,5)
So to round up a sum formula to 5 :-
=CEILING(SUM(range),5)
Celia
Posted by MJ on October 06, 2000 5:38 AM
Worked perfectly! Thanks.
