I have a spreadsheet which is doing what I need it to with Static, pre-determined values, but I would like to create a formula which will look at a table of percentages and depending on what the user input is, the formula will look at what range that value falls between, will assign a certain percentage discount. Here is the an example of what I am doing with everything assigned with pre-determined values...
|Progressive Area Pricing Calc2.xls|
What I want the new formula to do is look at the user input and then from the Area Discount Table, determine that if the value is between 2 and 7.99 that it should return 37.5%, between 8 and 23.99 then return 73%, 24 and higher then return 81%.
Does this make sense? Basically the user needs to have control of the parameters and set up the tables as they see fit depending on the situation- then they plug in the sizes and qty and test the pricing calculations. Is there a way to use VLOOKUP, MATCH or OFFSET to determine if a value falls within a range, then return a value based on that logic?
Seems like this might be similar to a Tax Table...
Thanks in advance!