Lookup table for rates?
 

Hi All! I'm still getting used to Excel2007 :-| Here's my question.

I'm trying to create a worksheet that will calculate interest on accounts on
a daily basis. The rate is tiered, based on the balance. However, the rates
change over time, too. I would like to create a lookup table that stores the
rates over time, so that the formula calculating the interest owed does not
have to be entered by hand. My lookup table would be in the form of:

Account limit 03/01/2009 03/15/2009
2500.00 Rate 1 Rate 2
10000.00 Rate 3 Rate 4
25000.00 Rate 5 Rate 6 etc...

Then on my calculation page, I would be inputing the dates that I'm
calculating for, and the lookup would choose the rate - i.e. if my balance
was 14247.63 on 03/10, my rate would be Rate3, if my balance was 9473.37 on
3/25, my rate would be Rate 2, etc.

Also, any assistance with setting this up to be less prone to "input error"
is appreciated!

Lookup table for rates?
 

Assumptions:
Top-left corner of table is C1
Current balance in A2
Current date in A3

=INDEX(D2:F4,MATCH(A2,C2:C4),MATCH(A3,D1:F1))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Wanda" wrote:

Hi All! I'm still getting used to Excel2007 :-| Here's my question.

I'm trying to create a worksheet that will calculate interest on accounts on
a daily basis. The rate is tiered, based on the balance. However, the rates
change over time, too. I would like to create a lookup table that stores the
rates over time, so that the formula calculating the interest owed does not
have to be entered by hand. My lookup table would be in the form of:

Account limit 03/01/2009 03/15/2009
2500.00 Rate 1 Rate 2
10000.00 Rate 3 Rate 4
25000.00 Rate 5 Rate 6 etc...

Then on my calculation page, I would be inputing the dates that I'm
calculating for, and the lookup would choose the rate - i.e. if my balance
was 14247.63 on 03/10, my rate would be Rate3, if my balance was 9473.37 on
3/25, my rate would be Rate 2, etc.

Also, any assistance with setting this up to be less prone to "input error"
is appreciated!

Lookup table for rates?
 

Assume your data in A1:C4 header in row 1
Criteria E2 holds balance, and F2 holds date

In G2: =INDEX(A1:C4,MATCH(E2,A1:A4),MATCH(F2,A1:C1))


"Wanda" wrote:

Hi All! I'm still getting used to Excel2007 :-| Here's my question.

I'm trying to create a worksheet that will calculate interest on accounts on
a daily basis. The rate is tiered, based on the balance. However, the rates
change over time, too. I would like to create a lookup table that stores the
rates over time, so that the formula calculating the interest owed does not
have to be entered by hand. My lookup table would be in the form of:

Account limit 03/01/2009 03/15/2009
2500.00 Rate 1 Rate 2
10000.00 Rate 3 Rate 4
25000.00 Rate 5 Rate 6 etc...

Then on my calculation page, I would be inputing the dates that I'm
calculating for, and the lookup would choose the rate - i.e. if my balance
was 14247.63 on 03/10, my rate would be Rate3, if my balance was 9473.37 on
3/25, my rate would be Rate 2, etc.

Also, any assistance with setting this up to be less prone to "input error"
is appreciated!

Lookup table for rates?
 

I'm planning on making my data in a table that would be "ever growing" my
data could be in a table that is perhaps A1:IV4. Would the formula below
still work (changing cell references accordingly, of course).

Thanks!

"Teethless mama" wrote:

Assume your data in A1:C4 header in row 1
Criteria E2 holds balance, and F2 holds date

In G2: =INDEX(A1:C4,MATCH(E2,A1:A4),MATCH(F2,A1:C1))


"Wanda" wrote:

Hi All! I'm still getting used to Excel2007 :-| Here's my question.

I'm trying to create a worksheet that will calculate interest on accounts on
a daily basis. The rate is tiered, based on the balance. However, the rates
change over time, too. I would like to create a lookup table that stores the
rates over time, so that the formula calculating the interest owed does not
have to be entered by hand. My lookup table would be in the form of:

Account limit 03/01/2009 03/15/2009
2500.00 Rate 1 Rate 2
10000.00 Rate 3 Rate 4
25000.00 Rate 5 Rate 6 etc...

Then on my calculation page, I would be inputing the dates that I'm
calculating for, and the lookup would choose the rate - i.e. if my balance
was 14247.63 on 03/10, my rate would be Rate3, if my balance was 9473.37 on
3/25, my rate would be Rate 2, etc.

Also, any assistance with setting this up to be less prone to "input error"
is appreciated!

Lookup table for rates?
 

see also my reply below to Toothless Mama

"Luke M" wrote:

Assumptions:
Top-left corner of table is C1
Current balance in A2
Current date in A3

=INDEX(D2:F4,MATCH(A2,C2:C4),MATCH(A3,D1:F1))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Wanda" wrote:

Hi All! I'm still getting used to Excel2007 :-| Here's my question.

I'm trying to create a worksheet that will calculate interest on accounts on
a daily basis. The rate is tiered, based on the balance. However, the rates
change over time, too. I would like to create a lookup table that stores the
rates over time, so that the formula calculating the interest owed does not
have to be entered by hand. My lookup table would be in the form of:

Account limit 03/01/2009 03/15/2009
2500.00 Rate 1 Rate 2
10000.00 Rate 3 Rate 4
25000.00 Rate 5 Rate 6 etc...

Then on my calculation page, I would be inputing the dates that I'm
calculating for, and the lookup would choose the rate - i.e. if my balance
was 14247.63 on 03/10, my rate would be Rate3, if my balance was 9473.37 on
3/25, my rate would be Rate 2, etc.

Also, any assistance with setting this up to be less prone to "input error"
is appreciated!