rounding to next 0.50

[Luc]

Hi all,

i'm putting together a price list and need to round to the nearest 0.50. Is
this possible?

Thanks,

Luc

P.S. I know this questions has been answered in the past, but i can't
retrieve it as it's no longer on the server. sorry!

 

[Bob Phillips]

=ROUND(A1*2,0)/2

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Luc]

ah, so simple! thanks for that.

=ROUND(A1*2,0)/2

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Gary Thomson]

Hi,

Here is 1 way of doing it. I would expect there are many
more, and more than likely there will be a more processor-
friendly way of doing it.

This assumes the value you are targeting is in cell A1.
If your data is in column A, you could type this in cell
B1 and copy down through column B.

=IF(A1-FLOOR(A1,1)<0.5,IF(ABS(CEILING(A1,1)-0.5-A1)<ABS(A1-
FLOOR(A1,1)),CEILING(A1,1)-0.5,FLOOR(A1,1)),IF(ABS(CEILING
(A1,1)-A1)<ABS(A1-(FLOOR(A1,1)+0.5)),CEILING(A1,1),FLOOR
(A1,1)+0.5))

Happy to Help,

Gary Thomson

 

[Luc]

One more thing Bob, if i wanted to round to the nearest quarter, is it as
easy?

 

[Gary Thomson]

Bob's way is obviously easier.

To round to the nearest quarter simply amend the formula
with 4's in it:

=round(A1*4,0)/4

Happy to help,

Gary Thomson

 

[Luc]

of course! hopefully i'll wake up soon enough and be able to figure these
things out myself!!

thanks a lot.

Luc