Hello All,
I could use a hand with building a formula. I'm teetering between two distinct IF statement, or perhaps a nested IF (?). Here is the issue.
We are given the sheet with product cost and MSRP. Our prices are 40% margin above cost (cell/0.6), which gives us our web retail price. We then look at the result of the web retail formula and sell at the higher of the two numbers. The catch is we don't want to sell anything that is over (greater than) 20% of the MSRP. The attached file has some sample data and hand written results of the formula.
In case it helps here is an analogy. Imagine three lines going from left to right and you are on the far left with a ball in your hand. If you kick the ball and it lands between lines 2 (MSRP) and 3 (Max price is 20% of MSRP) you are in the sweet spot. Falling short of Line 2 is acceptable, going beyond line 3 can't happen.
Love to get some feedback on this one.
Thanks,
Dave
I could use a hand with building a formula. I'm teetering between two distinct IF statement, or perhaps a nested IF (?). Here is the issue.
We are given the sheet with product cost and MSRP. Our prices are 40% margin above cost (cell/0.6), which gives us our web retail price. We then look at the result of the web retail formula and sell at the higher of the two numbers. The catch is we don't want to sell anything that is over (greater than) 20% of the MSRP. The attached file has some sample data and hand written results of the formula.
In case it helps here is an analogy. Imagine three lines going from left to right and you are on the far left with a ball in your hand. If you kick the ball and it lands between lines 2 (MSRP) and 3 (Max price is 20% of MSRP) you are in the sweet spot. Falling short of Line 2 is acceptable, going beyond line 3 can't happen.
Love to get some feedback on this one.
Thanks,
Dave