Round numbers 1.01 through 1.49 to 1.50
 

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

Round numbers 1.01 through 1.49 to 1.50
 

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

Round numbers 1.01 through 1.49 to 1.50
 

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

Round numbers 1.01 through 1.49 to 1.50
 

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

Round numbers 1.01 through 1.49 to 1.50
 

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

Round numbers 1.01 through 1.49 to 1.50
 

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

Round numbers 1.01 through 1.49 to 1.50
 

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

Round numbers 1.01 through 1.49 to 1.50
 

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

Round numbers 1.01 through 1.49 to 1.50
 

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

Round numbers 1.01 through 1.49 to 1.50
 

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

Round numbers 1.01 through 1.49 to 1.50
 

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