Hi - I am trying to find the value of a tiered range of numbers that are in a
VLookup table. There are three ranges in the lookup table with a percentage
value as the return value (COL 2). I have a number, say 2025 and my lookup
table is the following:
1 5%
1000 6%
2000 7%

From the return value, I need to find the total value of ((999*5%*E5) +
(1000*6%*E5) + (25*7%*E5)) using a formula that knows what to do for any
value above or below 2025 (for example). E5 is a static value. I know
there's got to be something already built into Excel, just can't put my
fingers on it. Your help is greatly appreciated!

Thanks in advance,
-pb100

I'm confused, but maybe I can give you some pointers. Do you want something
like this:

=999*vlookup(c1,a:b,2,true)*e5+1000*vlookup(c2,a:b ,2,true)*e5+25*vlookup(c3,a:b,2,true)*e5

Where your lookup table is in columns a:b, and c1,c2,c3 are the values you
are lookin up (like 2025).

Regards,
Fred.

"pb100" wrote in message
...
Hi - I am trying to find the value of a tiered range of numbers that are
in a
VLookup table. There are three ranges in the lookup table with a
percentage
value as the return value (COL 2). I have a number, say 2025 and my
lookup
table is the following:
1 5%
1000 6%
2000 7%

From the return value, I need to find the total value of ((999*5%*E5) +
(1000*6%*E5) + (25*7%*E5)) using a formula that knows what to do for any
value above or below 2025 (for example). E5 is a static value. I know
there's got to be something already built into Excel, just can't put my
fingers on it. Your help is greatly appreciated!

Thanks in advance,
-pb100

Here is one way. Assuming your lookup table is in A1:B3 and the number to be
evaluated (2025) is in F1, enter this formula in C1 and copy it down through
C3:

=IF(AND($F$1=A2,LEN(A2)0),(A2-A1)*B1*$E$5,IF($F$1<A1,0,($F$1-A1+1)*B1*$E$5))

Adjust cell references as needed. The answer is the sum of C1:C3. Enter this
formula wherever on the same sheet you want the answer returned:
=SUM(C1:C3)

This gives a slightly different answer than you indicated. Your example only
accounted for 2024 of the 2025 (999+1000+25). If the number is 1999 the
answer would be (999*5%)+(1000*6%). If the number is 2000 then the answer
must be (999*5%)+(1000*6%)+(1*7%). Therefore, for 2025 the answer must be
(999*5%)+(1000*6%)+(26*7%).

Hope this helps,

Hutch

"pb100" wrote:

Hi - I am trying to find the value of a tiered range of numbers that are in a
VLookup table. There are three ranges in the lookup table with a percentage
value as the return value (COL 2). I have a number, say 2025 and my lookup
table is the following:
1 5%
1000 6%
2000 7%

From the return value, I need to find the total value of ((999*5%*E5) +
(1000*6%*E5) + (25*7%*E5)) using a formula that knows what to do for any
value above or below 2025 (for example). E5 is a static value. I know
there's got to be something already built into Excel, just can't put my
fingers on it. Your help is greatly appreciated!

Thanks in advance,
-pb100

Thank you to all who responded! Ashish's solution worked but I needed an
open ended range at the top so I used Tom's solution with a slight change -
see below:
The lookup table (A1:B3):
1 5%
1001 6%
2001 7%

With the input value in cell F1, I used this formula in C1 then copy down to
C3:
=IF(AND($F$1=A2,LEN(A2)0),(A2-A1)*B1,IF($F$1<A1,0,($F$1-(A1-1))*B1))

The total value is SUM(C1:C3). So, if the input value is 2025, the total is
111.75.
Thanks for the help!!
-pb100


"Tom Hutchins" wrote:

Here is one way. Assuming your lookup table is in A1:B3 and the number to be
evaluated (2025) is in F1, enter this formula in C1 and copy it down through
C3:

=IF(AND($F$1=A2,LEN(A2)0),(A2-A1)*B1*$E$5,IF($F$1<A1,0,($F$1-A1+1)*B1*$E$5))

Adjust cell references as needed. The answer is the sum of C1:C3. Enter this
formula wherever on the same sheet you want the answer returned:
=SUM(C1:C3)

This gives a slightly different answer than you indicated. Your example only
accounted for 2024 of the 2025 (999+1000+25). If the number is 1999 the
answer would be (999*5%)+(1000*6%). If the number is 2000 then the answer
must be (999*5%)+(1000*6%)+(1*7%). Therefore, for 2025 the answer must be
(999*5%)+(1000*6%)+(26*7%).

Hope this helps,

Hutch

"pb100" wrote:

Hi - I am trying to find the value of a tiered range of numbers that are in a
VLookup table. There are three ranges in the lookup table with a percentage
value as the return value (COL 2). I have a number, say 2025 and my lookup
table is the following:
1 5%
1000 6%
2000 7%

From the return value, I need to find the total value of ((999*5%*E5) +
(1000*6%*E5) + (25*7%*E5)) using a formula that knows what to do for any
value above or below 2025 (for example). E5 is a static value. I know
there's got to be something already built into Excel, just can't put my
fingers on it. Your help is greatly appreciated!

Thanks in advance,
-pb100