One for the Excel genii - odd ceiling request 1

[stussy]

Hi

I need to do some price rounding - I usually use the CEILING function, but this is a little more complicated. The 4 acceptable price points are .25 / .49 / .75 and .99

The price should just jump to the next highest price point, so a couple of examples:

.45 would go to .49; .76 go to .99

Is this possible without too much messing around? Could I do something with 'IF the rightmost 2 characters are less than x then ceiling blah blah'?

TIA

Mike

 

[GlennUK]

How about ...

=CEILING(A1,0.25)-IF(MOD(2*CEILING(A1,0.25),1)=0,0.01,0)

I'm sure there's a much simpler way, but that's all I can come up with at the moment.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[stussy]

Bloody hell this site is exceptional sometimes.

Thanks, works perfectly.