Formula for Finding Retail

[wutzke]

To find a retail dollar amount based on cost and margin I use

Sum( COST /(( 100-MARG )*.01))
Where COST is the cost of an item and MARG is the desired margin.

So if an item is $50 at cost and I desire a 43% margin
Sum(50/((100-43)*.01))
gives me $87.72

I always want the cents to be 99, and that is easy enough.


But certain Retail amount are not desirable.
Generally we look for 9's and 5's and exclude 2's and 4's and 6's and
8's

Can anyone work out the logical for a formula to take my Cost to
Retail sum and round it to the desired Retail ending with 9's or 5's

 

[Jim Cone]

=IF(RIGHT(DOLLAR(SUM( COST /(( 100-MARG )*0.01)),2),1)/10<0.55,REPLACE(B5,LEN(B5),1,"5"),REPLACE(DOLLAR(SUM( COST /((
100-MARG )*0.01)),2),LEN(B5),1,"9"))
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"wutzke"
wrote in message
To find a retail dollar amount based on cost and margin I use
Sum( COST /(( 100-MARG )*.01))
Where COST is the cost of an item and MARG is the desired margin.
So if an item is $50 at cost and I desire a 43% margin
Sum(50/((100-43)*.01))
gives me $87.72
I always want the cents to be 99, and that is easy enough.
But certain Retail amount are not desirable.
Generally we look for 9's and 5's and exclude 2's and 4's and 6's and 8's
Can anyone work out the logical for a formula to take my Cost to
Retail sum and round it to the desired Retail ending with 9's or 5's