building a complex formula with the IF statement and Mround function

Hi there,


I work as a salesman and i use my excel sheets to track what i have sold, our company wants me to put prices with 2 decimals (we dont have fixed prices) here are some examples of the things i am struggeling with:

all prices that end between the range 0.96 and 0.24 should become 0.25 ( 10.21 becomes 10.25 and 10.96 becomes 11.25)

all prices that end between the range 0.26 and 0.49 should become 0.50 (10.26 becomes 10.50 and so on)

all prices that end between the range 0.51 and 0.74 should become 0.75 (10.51 becomes 10.75 and so on)

and finally all prices that end between the range 0.76 and 0.94 should become 0.95 (10.76 should become 10.95 and so on)



can this be done in 1 formula or does it need separate ones to function correctly
im at the end of my knowledge here so any help would be more then welcome.


thanks upfront

Try this formula
=IF(OR(MOD(A1,1)={0.25,0.5,0.75,0.95}),A1, IF(MOD(A1,1)>0.95, CEILING(A1+0.05, 0.25), IF(MOD(A1,1)>0.75,INT(A1)+0.95,CEILING(A1+0.01, 0.25)))) (replacing commas with semicolons depending on local settings)
Does that work for you?

Yes, I know ChemistB's formula works, but I worked out a different method and wanted to share. This assumes your cost is in H1:
=INT(H1+0.04)+INDEX({0.25,0.5,0.75,0.95},MATCH(MOD(H1+0.04,1),{0,0.2999,0.54999,0.799}))