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

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.

"Thomas Prescott" wrote:

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

Do

Ceiling(Cell Reference, .5)

Works like a charm

"Thomas Prescott" wrote:

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

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


"AKphidelt" wrote:

Do

Ceiling(Cell Reference, .5)

Works like a charm

"Thomas Prescott" wrote:

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

Try this:

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


"Thomas Prescott" wrote:

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

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

"AKphidelt" wrote:

Do

Ceiling(Cell Reference, .5)

Works like a charm

"Thomas Prescott" wrote:

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

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.

"Teethless mama" wrote:

Try this:

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


"Thomas Prescott" wrote:

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

You said, "round 1.50 through 1.99 to 2.00"

His formula doesn't round the value 1.50 to 2.00


"Thomas Prescott" wrote:

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

"AKphidelt" wrote:

Do

Ceiling(Cell Reference, .5)

Works like a charm

"Thomas Prescott" wrote:

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

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Thomas Prescott" <Thomas wrote in
message ...
Round the number range from 1.01 through 1.49 to 1.50; and to round 1.50
through 1.99 to 2.00

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.
--
David Biddulph

"Arvi Laanemets" wrote in message
...
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)?
--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

"Thomas Prescott" <Thomas wrote in
message ...
Round the number range from 1.01 through 1.49 to 1.50; and to round 1.50
through 1.99 to 2.00

Hi

Obviously it was too early morning for me :-))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"Arvi Laanemets" wrote in message
...
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)?
--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

"Thomas Prescott" <Thomas wrote in
message ...
Round the number range from 1.01 through 1.49 to 1.50; and to round 1.50
through 1.99 to 2.00