Round numbers 1.01 through 1.49 to 1.50

[Guest]

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

 

[Guest]

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.

 

[Guest]

Do

Ceiling(Cell Reference, .5)

Works like a charm

 

[Guest]

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

 

[Guest]

Try this:

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

 

[Guest]

Sweet! Just what I wanted to do. Many thanks. You are most kind

 

[Guest]

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.

 

[Guest]

You said, "round 1.50 through 1.99 to 2.00"

His formula doesn't round the value 1.50 to 2.00

 

[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)?

 

[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.

 

[Arvi Laanemets]

Hi

Obviously it was too early morning for me )