Round numbers 1.01 through 1.49 to 1.50

T

Thomas Prescott

Round the number range from 1.01 through 1.49 to 1.50; and to round 1.50
through 1.99 to 2.00
 
J

Joel

This may sound silly, but it works. Double the number and then Roundup().
This will give you either 3 or 4. The divide 3 or 4 by 2 to get 1.5 or 2.
 
T

Teethless mama

Hold that thought....Your formula "FAILED"

Let's say A1=1.50
B1 =CEILING(A1,.5) your result 1.50, OP wants 1.50 through 1.99 to 2.00
 
T

Teethless mama

Try this:

=IF(MOD(A1,1)=0,0,IF(AND(MOD(A1,1)>0,MOD(A1,1)<0.5),0.5,1))+INT(A1)
 
T

Thomas Prescott

Thanks. Oooops, didn't mean to start an Excel debate. Thank you all for the
help. I couldn't have figured this out without your help. Thanks again.
 
T

Teethless mama

You said, "round 1.50 through 1.99 to 2.00"

His formula doesn't round the value 1.50 to 2.00
 
A

Arvi Laanemets

Hi

??? (confused)
With number in A1
=ROUND(A1,2)

.... Or have they implemented the bankers rounding for worksheetfunction too
in later versions (I use Excel2000)?
 
D

David Biddulph

Yes, I think you *are* confused, Arvi. With 1.37 in A1, =ROUND(A1,2) will
leave it at 1.37 (with 2 decimal places displayed), not round to 1.50.
 
Top