Help with Pricing Formula

**Byoung42** · 10-31-2011, 04:14 PM

Hello,

I have been using excel for a while... but for some reason cannot write this seemingly simple formula. I'm hoping someone can help.

Here is what I need.

I need a formula to do the following:

If the old margin on a product is less than 70%, make the new margin 70%

If the old margin is 70% - 99%, make the new margin 100%

If the old margin is 100% or higher make the price of the product stay the same as before.

If the new margin is 100% and the old margin is higher than the new margin then make the new price the same as the old price.

I'm not sure this makes sense... perhaps that is why I can't figure it out.

Sorry for the mess. Ask questions to clarify if you need to.

I REALLY APPRECIATE your help.

**arthurbr** · 10-31-2011, 04:19 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Byoung42** · 10-31-2011, 04:31 PM

I have added the workbook I have been working on.

Thanks!

**6StringJazzer** · 10-31-2011, 09:23 PM

Your file looks fairly clear but I'm not sure where you are getting your logic from that you describe in your post. I think your problem is that your business rule creates a circular dependency. Your new margin is calculated using the new price, but your rules say

If the new margin is 100% and the old margin is higher than the new margin then make the new price the same as the old price.

That means that if you adjust the price based on the new margin, the new margin will change, and you might have to change the price again, and then the margin will change again, and there you are in an infinite loop, which Excel sort of frowns on.

I think you need to rethink your business logic. There are ways to actually take advantage of circular dependencies in Excel and manage them but I do not have experience doing that, and am not sure that the right solution for this problem anyway.

You may also need to use something like Solver, which will provide an iterative solution to converge on a desired outcome.