Conditional Lookup? Area Discount Table

MonkeyBoyATL

New Member
Joined
Mar 23, 2004
Messages
2
:rolleyes:

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
ABCDEFGHIJK
2
3ParametersUserInput
4BasePrice(1Sq.Ft)$40.00Qty15
5Height6
6AreaDiscountTableQtyDisc.TableWidth6
7SqFtDisc.%Price/SqFt.QtyDiscount
810.00%$40.0010%SqFt.0.25
9237.50%$25.0025%
10873.00%$10.80515%UnitPrice$10.00w/oQtyDiscount
112481.00%$7.601025%
12$-1530%QtyDiscount30%
13$-2035%UnitPrice$7.00w/QtyDiscount
14$-
15$-Subtotal$105.00
16$-
Sheet2



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!
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Do you want the minimum of 2 qualifying discounts, or the max? [Hey, BfM!]
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi MonkeyBoyATL:

Let us have a look at this ...
Book1
ABCDEFGHIJ
1
2
3UserInput
4BasePrice(1sft)$40.00Qty15
5Height6
6AreaDisc.TableQtyDisc.TableWidth6
7sftDisc.%PricepsfQtyDiscount
800 10sft0.25
910$40.0025%
10237.50%$25.00515%UnitPrice$10.00w/oqtyDisc.
11873%$10.801025%
122481%$7.601530%QtyDisc30%
13 2035%DiscPrice$7.00withqtyDisc.
14 
15 Subtotal$105.00
16 
Sheet3


Is this what you are looking for? I have used VLOOKUP formula with use of the size and quantity discounting. Please post back if you need to discuss this further.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,326
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top