Calculate Optimal Price

[JaneeBaker]

I need a formula to find the optimal sales price based on cost and a maximum gross margin within a range. This formula is beyond my Excel skills. Can someone please help me?

In the screenshot, the optimal price is .166 ea as the gross margin calculation keeps the item within the acceptable range. Using .170 ea takes the gross margin into the next range which causes the calculated amount to exceed that level's range. I found these amounts by manually plugging numbers until I reached a max price without going over the maximum gross margin in the sales $ range.

 

[offthelip]

Put the gross margin percent that you want into O5 and the put this equation into F5:
=L5/(1-O5)