Price rounding - exception formula required
 

At present, I'm using a tried and trusted formula for rounding prices in
Excel (from the G2 cell) to either .50 or .95 as follows:

=ROUNDDOWN(G2+0.041,0)+IF(G2-ROUNDDOWN(G2+0.041,0)=0.5,0.95,0.5)

I've now been asked if an exception can be made to the formula for prices of
100+ which rounds down to the nearest whole unit, i.e. 101.95 becomes 101,
503.59 becomes 503 and so on.

Any help appreciated.

Regards
Alex McCourty
E-mail:

=IF(G2=100, rounddown(G2,0),
ROUNDDOWN(G2+0.041,0)+IF(G2-ROUNDDOWN(G2+0.041,0)=0.5,0.95,0.5) )


--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Alex McCourty" wrote in message
...
At present, I'm using a tried and trusted formula for rounding prices in
Excel (from the G2 cell) to either .50 or .95 as follows:

=ROUNDDOWN(G2+0.041,0)+IF(G2-ROUNDDOWN(G2+0.041,0)=0.5,0.95,0.5)

I've now been asked if an exception can be made to the formula for prices
of 100+ which rounds down to the nearest whole unit, i.e. 101.95 becomes
101, 503.59 becomes 503 and so on.

Any help appreciated.

Regards
Alex McCourty
E-mail:

Hi,
If you want to round a number in G2 to the nearest 0.50,then use the formula
as
=if(G2+0.50int(g2)+1,int(g2)+1,int(g2))
Thank you,
K.S.Warrier

"Alex McCourty" wrote:

At present, I'm using a tried and trusted formula for rounding prices in
Excel (from the G2 cell) to either .50 or .95 as follows:

=ROUNDDOWN(G2+0.041,0)+IF(G2-ROUNDDOWN(G2+0.041,0)=0.5,0.95,0.5)

I've now been asked if an exception can be made to the formula for prices of
100+ which rounds down to the nearest whole unit, i.e. 101.95 becomes 101,
503.59 becomes 503 and so on.

Any help appreciated.

Regards
Alex McCourty
E-mail: