Variable Multiplier Based on a Value in another Column

[ellistyle]

In each of 5 columns of data there is a different unit sell price that is based on a quantity discount. The discount buckets are 1-24, 25-49, 50-199, 200-999 and 1000+.


In another column is the quantity. I want to calculate an extended price (quantity * sell price) that is accurate based on the quantity.


The Value would be Quantity (B) * Column C, D, E, F OR G depending on what discount bucket B falls into.

 

[buddyvail]

Hi ellistyle. It may be rudimentary, but could you not use a nested IF function to make the calculations?

 

[ellistyle]

How do you set up an if function to when you are trying to get in a range? For example, does this value fall in the range of 25-49?

 

[Sajan]

Hello ellistyle,


Following is one approach you could use:


I have assumed the following for the formula:

qty is in column A

price for 1st tier (1-24) is in column B

price for 2nd tier (25-49) is in column C

price for 3rd tier (50-199) is in column D

price for 4th tier (200-999) is in column E

price for last tier (1000+) is in column F

Value is in column G


Assuming that the Excel Name "DiscountTiers" is referring to {1;25;50;200;1000}

(You could set it up as an actual range somewhere in your workbook, or type in those values directly for the Name. You could also put in the above array directly into the formula below, in place of DiscountTiers.)


Then the Value can be calculated as (shown for cell G2)

=CHOOSE(MATCH(A2,DiscountTiers),B2,C2,D2,E2,F2)*A2


Here A2 is the quantity,

B2, C2, ... F2 are the unit prices


In this case, MATCH() returns the largest value that is smaller than or equal to the value in A2

You can then use the CHOOSE() function to select the corresponding unit price.


Cheers,

Sajan.

 

[ellistyle]

Thanks, Sajan. I worked it out with several helper columns. I'll go back and try this!