Price rounding - exception formula required

[Alex McCourty]

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: (e-mail address removed)

 

[Bernard Liengme]

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

 

[K.S.Warrier]

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