Rounding Numbers into Retail Amounts

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

caveman.savant - 30 Sep 2009 14:22 GMT

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?

Reply to this Message

Pete_UK - 30 Sep 2009 14:48 GMT

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

Hope this helps.

Pete

> Trying to find a way to find "good" retail numbers based on cost of
> goods.
[quoted text clipped - 9 lines]
>
> I've tried CEILING and some conditional IF's. Is there a better way?

Reply to this Message

caveman.savant - 30 Sep 2009 14:55 GMT

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

works

Now I must create a UDF

> Try CEILING to multiples of 5, and then subtract 5 cents.
>
[quoted text clipped - 15 lines]
>
> > I've tried CEILING and some conditional IF's. Is there a better way?

Reply to this Message

caveman.savant - 30 Sep 2009 15:22 GMT

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

Reply to this Message

Pete_UK - 30 Sep 2009 16:17 GMT

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

> I found
> =((CEILING(a1,5))-1)+0.95
[quoted text clipped - 24 lines]
>
> - Show quoted text -

Reply to this Message