Rounding up to a price

Hi

I'm trying to come up with a solution that will allow to a price to be either rounded up to a 4 or 9 pence end figure,
E.g. Cell A1 - ｣2.21 rounding upto ｣2.24
but if the value is ｣2.25 it will round up to ｣2.29


Working on afew formulas to exract the last char and either take it from either the 4 or 9 and add the variance, if someone has a working solution that would finish it before i figure it out that would be a great time saver!

thanks
Adi

Please Login or Register  to view this content.

Hiya Adi

There has to be an easier way than this, but here you go; it works.

=IF(A1-ROUNDDOWN(A1,1)<0.05,A1-(RIGHT(A1,1)/100)+0.04,A1-(RIGHT(A1,1)/100)+0.09)

Hi try

=IF(CEILING(A1,0.05) = CEILING(A1, 0.1), CEILING(A1, 0.05)-0.01, CEILING(A1, 0.05))

Hi

thanks for the help, SixthSense yours worked but only if it was ending in a ｣?.?1
Roger Govier yours did the trick nicely, just need to figure out how it works!!!
Adi

kenny.fsw, thanks your route would have been the way i would have taken, i had partially written in the same structure

thanks All

in a COLUMN type any value just like 50 on a1 and series start value 2( sheet 1)
(sheet 2) ai cell
=IF(OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1))+Sheet1!A1
try this
but one prob. the value is add but when i seved and reopen change a1 value in sheet 1; but that can't add
plz solved my prob...