Caly
Board Regular
- Joined
- Jul 19, 2015
- Messages
- 160
- Office Version
- 365
- 2013
- 2011
- 2010
- 2007
- Platform
- Windows
Hello I have 5 prices from 5 different customers and looking to take the Minimum between all 5 but only where the margin is good.
Only way I can think of is to evaluate each of the 5 sets Of margins but that is very long
Is there a different way where if the formula will provide the lowest price based on a good margin?
Example data
In the below is the current price and margin along with teh acceptable margin floor
Underneath it shows the various prices to compare against
Under that is the expected result showing Price 2 since that margin is ok and is the lowest price point. Price 1 is lower but the margin is no good.
But how can I take the min price where the margin is good between 5 different values in a Formula?
Only way I can think of is to evaluate each of the 5 sets Of margins but that is very long
Is there a different way where if the formula will provide the lowest price based on a good margin?
Example data
In the below is the current price and margin along with teh acceptable margin floor
Underneath it shows the various prices to compare against
Under that is the expected result showing Price 2 since that margin is ok and is the lowest price point. Price 1 is lower but the margin is no good.
But how can I take the min price where the margin is good between 5 different values in a Formula?
Current Price | Current margin | margin Floor | |||||||
$33.50 | 30% | 15% | |||||||
Price 1 | margin | Price 2 | Margin | Price 3 | margin | Price 4 | margin | price 5 | Margin |
$20 | 10% | $30 | 15% | $40 | 20% | $50 | 30% | $36 | 40% |
Expected Result | |||||||||
Price | margin | Source | |||||||
30 | 15% | Price 2 | |||||||