Lookup as value grows

[jamtay317]

Hello, I'm trying to work on a calculator that will lookup values and change as the value grows. For example:

If I have something like this:

Our Rate table may look like:
0 .1
15 .2
25 .3

100 should be sum of 1* Rate Lookup all the way to 100 * Rate Lookup
200 should be sum of 101* Rate Lookup all the way to 200 * Rate Lookup
300 should be sum of 201* Rate Lookup all the way to 300 * Rate Lookup

When sum of value gets to RateNumber it should use next

 

[Eric W]

I'm not entirely clear on your requirements, but consider this:

Book1
	A	B	C	D	E	F	G
1	Value	Rate		Rate Table
2	375	85		Low range	High Range	Percent	Delta percent
3				0	100	0.1	0.1
4				100	200	0.2	0.1
5				200	300	0.25	0.05
6				300	500	0.4	0.15
7				500	1000	0.6	0.2
8				1000	9999999	0.65	0.05
Sheet9

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT((A2-D3:D8),G3:G8,--(A2>D3:D8))
G3:G8	G3	=F3-N(F2)

Create your table in D1:G8. Your values should be in D3:F8, then add the formula in G3 and drag down. Then you can use the formula in B2 to calculate the combined rate. In this example, you have 100*.1 + 100*.2 + 100*.25 + 75 * .4, giving 85. Let us know if that is what you're looking for.