Hi All,
I have this assignment at work, and i tried bunch of formulas but cannot get this (hopefully) easy formula right, i have tried INDEX, MATCH, COLUMN, COUNTIFS, SUMPRODUCT but all to no avail; i cannot comprehend the complexity and turn it into simple logic.
In the image attached, i have a table with product and prices, the idea is to develop a formula in Results cell (gray) that "lookup" the table for price (range) and returns the % on the 1st row of the table.
For Example:
1. If user selects product A then between 1019.9 the result should give 10%
2. If user selects product B then between 3242 the result should give 20%
3. If user selects product C then between 3444 the result should give 30%
Can you give me an example of a formula i can use? If i can get the colour it would help, if not its OK too as i can use conditional formatting based on the %.
Not VBA just plain old excel formulas.
Thanks a ton.
Aubin
I have this assignment at work, and i tried bunch of formulas but cannot get this (hopefully) easy formula right, i have tried INDEX, MATCH, COLUMN, COUNTIFS, SUMPRODUCT but all to no avail; i cannot comprehend the complexity and turn it into simple logic.
In the image attached, i have a table with product and prices, the idea is to develop a formula in Results cell (gray) that "lookup" the table for price (range) and returns the % on the 1st row of the table.
For Example:
1. If user selects product A then between 1019.9 the result should give 10%
2. If user selects product B then between 3242 the result should give 20%
3. If user selects product C then between 3444 the result should give 30%
Can you give me an example of a formula i can use? If i can get the colour it would help, if not its OK too as i can use conditional formatting based on the %.
Not VBA just plain old excel formulas.
Thanks a ton.
Aubin
Attachments

10.3 KB Views: 2