Nested vlookup function with calculation

Question

Need to create formula that would calculate revenue based on two variables - increase in price and decrease in quantity.

Original Quantity:   51317         in cell B3

Price: $26.39                               in cell B5

Price Increase %: 5%                 in cell B6

Variable Table (cells A9:B15):

Price Increase	Quantity Decrease
5%	2%
8%	3%
10%	4%
12%	5%
15%	7%
18%	10%
20%	20%

I've created the following formula but am getting negative values vs the lower revenue $$ based on the price increase:

=(100%+VLOOKUP(B6,A9:B15,1,FALSE)*B5)*(100%-VLOOKUP(B6,A9:B15,2,FALSE)*B3)

so what I'm saying is find B6, which is the % of price increase in the table below and add that % to 100%, multiply it by the Price and find B6, the % of price increase, return the % of quantity decrease associated with the price increase, subtract that percentage from 100% and multiply by the original quantity to get the revenue $$.

Any advise?

Thank you.

Answer 1

Hi Monika,

A couple of different ways you could tackle a 0% price increase in B6:

1. Extend the lookup table so the first row is 0% (price inc)  0% (quant dec)

    And amend the lookup range in the formula to A9:B16

2. Amend the formula to check first for 0% in B6, something like:

     =IF(B6=0,B5*B3,(100%+VLOOKUP(B6,A9:B15,1,FALSE))*B5*(100%-VLOOKUP(B6,A9:B15,2,FALSE))*B3)

3. If you want to use IFERROR then amend the formula something like:

     =IFERROR((100%+VLOOKUP(B6,A9:B15,1,FALSE))*B5*(100%-VLOOKUP(B6,A9:B15,2,FALSE))*B3,B5*B3)

I should have added last time that the problem with the original formula was that the multiplication (of B5 and B3) was occurring before the addition/subtraction - for more information on calculation operators and their order of precedence see this help page: http://office.microsoft.com/en-us/excel-help/calculation-operators-and-precedence-HP010342223.aspx

********* EDIT **********

I also meant to add that in the original formula you don't really need the first of the 2 Vlookup's because it's just returning the same value as it's looking up - so the formula can be re-written as:

=(100%+B6)*B5*(100%-VLOOKUP(B6,A9:B15,2,FALSE))*B3

____________

Regards, Tom

Answer 2

murkaboris,

Change where the brackets are in the formula to this:

=(100%+VLOOKUP(B6,A9:B15,1,FALSE))*B5*(100%-VLOOKUP(B6,A9:B15,2,FALSE))*B3

________________

Regards, Tom

Answer 3

Hi,

Try this

1.  In cell B6, enter the following formula =B3*(1+C3)*B5*(1+C5).  WIth your data above the answer in this cell would be 13,54,256.  Cells C3 and C5 are blank cells

2.  Click on cell B9 and enter =B6

3.  In range B10:B16, enter 5%,8%,10%,12%,15%,18% and 20% respectively

4,  In range C9:I9, enter -2%,-3%,-4%,-5%,-%,-10% and -20% respectively

5. Select B9:I16 and go to Data > What-if Analyssis > Table
6. In the row input cell, specify C3 and in the column inpput cell, specify C5

The matrix should now show you the revenue under all combinations.

Hope this helps.

Answer 4

Hi Tom:

Thank you for the suggestions, all of them worked. I like the simplified verson so my final formula that I'm using is your last suggestion.

Thank you for all your assistance.

Monika

Answer 5

Thank you Tom, worked like a charm.

If you would want to add to it 'IFFERROR" to make sure that if the price increase is 0% then the formula multiplies the original price with the original quantity how would you incorporate that?

Thank you

Monika