Nested IF or Vlookup?

**zhills63** · 06-20-2013, 03:15 PM

I am trying to take a number and fit it in a group and multiply all preceding groups and the existing group by a multiplier and all other numbers in the table be 0.

For instance if my number is 313 then I will have 150 in the summary field for that row and 350 in the next and then I will take the minimum of 201 which is 112 and multiply it by 1.35 and the next two results will be zero.

6-19-2013 7-31-55 AM.jpg

this is the formula I was using where column H is the first column you see.

Please Login or Register  to view this content.

Any help would be appreciated as I don't do this often and I had no success with the Vlookup but I think it might be able to work with that formula as well.

Jeremy

**Ace_XL** · 06-20-2013, 03:51 PM

Try

=SUMPRODUCT(--(A1>{0,75,200,500,1000,9999}), (A1-{0,75,200,500,1000,9999}), {0,2,-0.25,-0.40,-0.35,-0.75})

http://www.mcgimpsey.com/excel/variablerate.html

**zhills63** · 06-20-2013, 05:11 PM

I can't seem to make this work. I am attaching a sample of the file so you see my problem. I want to compare $c$9 to Column H and Column I. If the number is higher than column j then I want to multiply the amount of $C$9 by the value in column Q by K. If the number falls in the range of H to I then I want to multiply the amount over H by column K. All other rows should return a zero.

Hope this gets me a bit closer to a solution.

savings calc.xlsx

**Ace_XL** · 06-20-2013, 05:17 PM

In N3

=MIN(J3,$C$9)*($C$9>=H3)*K3

Copy down

**Tankstir** · 06-20-2013, 05:24 PM

Jeremy, try the following formula: =IF(AND(C7/12>=$H$3,C7/12<=$I$3),C7/12*$K$3,IF(AND(C7/12>=$H$4,C7/12<=$I$4),C7/12*K4,"")) IF this works, you will need to continue inserting "if"s were the ,"" starts.