Calculating Increase Based on a Rate Table

[NoviceAlicia]

Excel Ninjas,

I need to calculate the amount of increase based on a sum obtained from previous cells. The rate of increase is dependent on the value of the figure from a given cell, e.g.K5.

The rate table looks something like this:

[pre]

Col. A        Col. B Amt.     Col. C Increase on Amt.   Col. D Rate of increase on
Amount Over    Not Over	      In Col. A.                Excess Over Amt. in Col. A
$0	        50,000	       0	                 10%
50,000	        75,000	       5000	                 11%
75,000	        100,000	       5500	                 12%
100,000	        125,000	       9000	                 13%
125,000	        150,000	       13,000	                 14%
150,000	        175,000	       17,500	                 15%
175,000	        200,000	       22,500	                 16%
200,000	        225,000	       28,000	                 17%
225,000	        ---	       34,000                    20%

[/pre]
If the amount in K5 is $340,000, the total increase would be:

(340,000-225,000) * 20% + 34,000= $57,000


If the amount in K5 is 60,000, the total increase would be:

(60,000 -50,000) * 11% + 5000= $6,100.


I have tried using Nested IF statements but I think I have too many ranges to properly calculate. This gets me close but when I add another IF statement, I get an error that


I have too many arguments.


=IF(K5<50000,K5*0.1,IF(K5<75000,K5>50000,((K5-50000)*0.11)+(5000)))


I have considered using a table and the VLOOKUP function. I am very inexperienced with Excel so I am not certain what the most efficient way to accomplish this calculation would be.

 

[jason]

=(B8-VLOOKUP(B8,E9:H17,1,TRUE))*VLOOKUP(B8,E9:H17,4,TRUE)+VLOOKUP(B8,E9:H17,3,TRUE)


B8 is your K5

E9:h17 is the following data you provided:

$0--------50,000----0--------10%

50,000----75,000----5,000----11%

75,000----100,000---5,500----12%

100,000---125,000---9,000----13%

125,000---150,000---13,000---14%

150,000---175,000---17,500---15%

175,000---200,000---22,500---16%

200,000---225,000---28,000---17%

225,000--- --- -----34,000---20%


one issues/question: your value ranges overlap. 0-50k uses these values while 50k-75k uses a diff. set of values. obviously this isnt possible. either it needs to be 0-49,999 / 50,000 - 74,999 etc... or 0-50,0000 / 50,001-75,000 etc.


the formula that i provided above will 'round down' so-to-speak on exact mactches. meaning if b8 = 50,000; it uses the values from the previous set(0-50,000)

 

[NoviceAlicia]

Thanks Jason. I used a variation of this formula and got the needed calculation. Thanks for your response.

 

[jason]

Glad it helped!!