Odd rounding question indead

[tbowland]

This question comes in relation to the retail industry.
We have what are call nice numbers in reference to Sell prices.
You will have noted that you rarely see items for sale for $24.72
We would round this number up to $24.75

So the question...after calculating a sell price from cost and Margin I need to round it up to the nearest "nice" decimal point
They are, and this is the crazy challenging part, .15, .25, .35, .50, .65, .75, .85, .90.
There is no pattern here so all I can think of is a ceiling function and the maximum imbedded if functions.

Any help would be hugely appreciated
Thanks
Ted

 

[Biz]

Can you post selling before in Col A and desired selling price in Col B?
This will assist everyone to understand your question properly.

 

[zarathustra]

Assuming you want to round up every time use: =CEILING(A1,0.05)

If you want to round up & down use: =ROUND(A1/0.05,0)*0.05

 

[James006]

Hello,

My understanding of your question would lead to following :

INT(A1)+(IF(MOD(A1,INT(A1))<=0.15,0.15,IF(MOD(A1,INT(A1))<=0.25,0.25,IF(MOD(A1,INT(A1))<=0.35,0.35,IF(MOD(A1,INT(A1))<=0.5,0.5,IF(MOD(A1,INT(A1))<=0.65,0.65,IF(MOD(A1,INT(A1))<=0.75,0.75,IF(MOD(A1,INT(A1))<=0.85,0.85,0.9))))))))

Hope this will help

 

[StephenCrump]

Or perhaps: =INT(A1)+INDEX({1,0.9,0.85,0.75,0.65,0.5,0.35,0.25,0.15,0},MATCH(ROUND(A1-INT(A1),15),{1,0.9,0.85,0.75,0.65,0.5,0.35,0.25,0.15,0},-1))

 

[tbowland]

That is so so great thanks everyone for all your help. StephenCrump, I used yours as it was the short and simplest, thanks.

BIZ, I built the Sell price formula in ' =INT(([@[Cost/ Unit]]/(1-$P$9)))+INDEX({1,0.95,0.85,0.75,0.65,0.5,0.35,0.25,0.15,0},MATCH(ROUND(([@[Cost/ Unit]]/(1-$P$9))-INT(([@[Cost/ Unit]]/(1-$P$9))),15),{1,0.95,0.85,0.75,0.65,0.5,0.35,0.25,0.15,0},-1)) '

I seems to work great.
Thank you again everyone
Ted

 

[Biz]

It would be easier using same formula but put the custom list ie 1,0.95,0.85,0.75,0.65,0.5,0.35,0.25,0.15,0 to column K1:K11.

=INT(A2)+INDEX($K$1:$K$11,MATCH(ROUND(A2-INT(A2),15),$K$1:$K$11,-1))

Visually the formula very compact plus if your custom list changes don't need to copy formula to multiple cells. Assuming adding extra row add on the second list row. Even better convert the range to table.