round numbers in a formula
Posted by Carmen on December 14, 2000 2:51 PM
I want to round the results of a formula to .99 or .49.
example would be 1.63 in cell A1
if the formula was in A2 ... =A1*.35 (resulting in .57-I want .99)
I want the dollars that end in .01 to .48 to end in .49
and .50 and above to end in .99
Can anyone help? Is this possible?
Posted by marbel on December 14, 2000 4:31 PM
Try MROUND (you may need to install it from the office cd.) It is intended to round to a multiple (eg. .5), and then subtract .01.
specifically, to apply to your number in cell C4:
=MROUND(C4,0.5)-0.01
That should do it.
mb
mb
Posted by Celia on December 14, 2000 8:21 PM
MROUND will round to the NEAREST specified multiple.
To round UP, CEILING has to be used.
However, if the number to be rounded ends in .50 or .00 then CEILING will leave the number as is.
I have assumed that you want to round up numbers that end in .00 to the next .49.
Therefore, to round .00 to .49 up to .49 and .50 to .99 up to .99 the following does it (it's a bit long - perhaps there's a better way). The formula incorporates your calculation A1*0.35 :-
=IF(A1*0.35=0,0,IF(A1*0.35-INT(A1*0.35)=0,INT(A1*0.35)+0.49,IF(A1*0.35-INT(A1*0.35)=0.5,INT(A1*0.35)+0.99,CEILING(A1*0.35,0.5)-0.01)))
Celia
Posted by marbel on December 15, 2000 7:54 AM
Thanks for correcting me Celia- I misread the help file- it says "MROUND rounds up" so I just kind of stopped there, but it continues "away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple."
Posted by summer on February 08, 2001 1:42 PM
