Three dimensional tables

[N Prakash]

I would like to know the formulae to calculate the tiered interest on the amount for a day. I tried nesting if condition and it becomes too complicated.

My data is like this. In A3 to A52 I am having amount from 1000 to 50000 incremented by 1000. I have slab of 10000 to 50000 in B1 to F1 columns. I have interest rate in B2 to F2 starting from 5% to 9% incremented by 1%. I want to have 3 dimensional tables depending on the amount the interest per day should populate. For 1000 only in column B3, interest amount should populate. If the amount is 15000, up to 10000 interest at 5% to be calculated and above 10000 i.e. for next 5000 interest at 6% to be calculated. If amount is greater than 50000, for first 10000 at 5%, for amount >10000 up to 20000 at 6% and so on.

Any help please.

Regards,
Prakash

 

[Dryver14]

I don't really understand the benefit of a three dimentional table with the information you are looking for, for instance 1000 will never be 5000 so no need to go accross the table from c to f or am I missing something?

 

[Dryver14]

if you were to list 5% in A1 with 1000 to 10,000 beneath it then in col B2
list results of Numbers*5%, then col c1 6% 11,000 - 20,000 and use formula in d2 (D2-10000)*.06+500 (500 =last result of first row B)

 

[N Prakash]

Re: Three dimensional tables/Tiered index

I think I was not clear. My requirement is Tiered index rate calculation.

I need A3 amount on which the interest is calculated looking for the step in B1 and interest in B2. Since the amount in A3 is only 1000 which is less than 10000 step the rate will be 5%. When the mount crosses the 10000 say amount in A13 which is 11000, so for this up to 10000 the rate will be at 5% and for 1000 the rate will be at 6%. Similarly in A52 the amount is 50000 so here for first 10000 it is 5% and next 10000 it is at 6% and third 10000 it is at 7% , fourth 10000 it is at 8% and last 10000 it is at 9%. If amount is over 50000 then the excess amount more than 50000 also gets interest at 9%.

Hope I have clarified

Regards,
Prakash