Rounding Numbers into Retail Amounts

[caveman.savant]

Trying to find a way to find "good" retail numbers based on cost of
goods.

Right now if I want a 40% margin on cost I use the following
=ROUND(A1/((100-$40)*0.01),0)+0.95

A1 is the cost. I Round the result (and add 95 cents). So $55 cost
returns $92.95

But I consider certain numbers like $90,$91,$92,$93,$95,$96,$97,$98 to
be "bad" at retail. "Good" numbers would be $94.95 or $99.95

I've tried CEILING and some conditional IF's. Is there a better way?

 

[Pete_UK]

Try CEILING to multiples of 5, and then subtract 5 cents.

Hope this helps.

Pete

 

[caveman.savant]

I found
=((CEILING(a1,5))-1)+0.95

works

Now I must create a UDF

 

[caveman.savant]

Also decided to offer an average of proposed margins between 40 & 55
percent. So the Function takes the cost and returns a suggested
retail.


Function FindRetail(cell As Range, _
Optional default_value As Variant)
myCost = cell
X = myCost
Factor = 5
'=ROUND(F8/((100-$G$7)*0.01),0)
Dim myArr(4)
myArr(1) = myCost / 0.6
myArr(2) = myCost / 0.55
myArr(3) = myCost / 0.5
myArr(4) = myCost / 0.45
myArrAvg = (myArr(1) + myArr(2) + myArr(3) + myArr
(4)) / 4

'
myCeiling = (Int(myArrAvg / Factor) - (myArrAvg / Factor - Int
(myArrAvg / Factor) > 0)) * Factor

'myRetail = ((Ceiling(myCost, 5)) - 1) + 0.95
myRetail = ((myCeiling) - 1) + 0.95
FindRetail = myRetail
End Function

 

[Pete_UK]

But isn't:

( X -1 ) + 0.95

the same as:

X - 0.05

where X is the CEILING function rounding up to multiples of $5 ?

I see you have done the same within your UDF.

Pete