I have a sales incentive grid with tenure of loan on one axis & loan size on
another axis. For e.g., for a loan size between $6000 to $9999 at a tenor of
24 months, teh incentive payable is $24 whereas for a loan size between
$10000 to &19999 at a tenor of 36 months, the incentive payable is $65. There
are a total of 7 loan bands on one axis and 5 tenor bands on the other axis.

How do i create a formula so that excel picks the correct incentive amount
depending upon the loan amount and tenor combination.

Its so confusing. Pls help..

=INDEX(B2:M20,MATCH(tenure,A2:A20,0),MATCH(loan,B1 :M1,0))

you might (will!) need to adjust to your data

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KDD" wrote in message
...
I have a sales incentive grid with tenure of loan on one axis & loan size
on
another axis. For e.g., for a loan size between $6000 to $9999 at a tenor
of
24 months, teh incentive payable is $24 whereas for a loan size between
$10000 to &19999 at a tenor of 36 months, the incentive payable is $65.
There
are a total of 7 loan bands on one axis and 5 tenor bands on the other
axis.

How do i create a formula so that excel picks the correct incentive amount
depending upon the loan amount and tenor combination.

Its so confusing. Pls help..

Thanks Bob.

Pardon my ignorance:

B2:M20 , A2:A20 and B1:M1 refer to which data?




--
KDDXB


"Bob Phillips" wrote:

=INDEX(B2:M20,MATCH(tenure,A2:A20,0),MATCH(loan,B1 :M1,0))

you might (will!) need to adjust to your data

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KDD" wrote in message
...
I have a sales incentive grid with tenure of loan on one axis & loan size
on
another axis. For e.g., for a loan size between $6000 to $9999 at a tenor
of
24 months, teh incentive payable is $24 whereas for a loan size between
$10000 to &19999 at a tenor of 36 months, the incentive payable is $65.
There
are a total of 7 loan bands on one axis and 5 tenor bands on the other
axis.

How do i create a formula so that excel picks the correct incentive amount
depending upon the loan amount and tenor combination.

Its so confusing. Pls help..

B2:M20 is the incentive matrix

A2:A20 is the tenure list

B1:M1 is the loan value list

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KDD" wrote in message
...
Thanks Bob.

Pardon my ignorance:

B2:M20 , A2:A20 and B1:M1 refer to which data?




--
KDDXB


"Bob Phillips" wrote:

=INDEX(B2:M20,MATCH(tenure,A2:A20,0),MATCH(loan,B1 :M1,0))

you might (will!) need to adjust to your data

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KDD" wrote in message
...
I have a sales incentive grid with tenure of loan on one axis & loan
size
on
another axis. For e.g., for a loan size between $6000 to $9999 at a
tenor
of
24 months, teh incentive payable is $24 whereas for a loan size
between
$10000 to &19999 at a tenor of 36 months, the incentive payable is
$65.
There
are a total of 7 loan bands on one axis and 5 tenor bands on the other
axis.

How do i create a formula so that excel picks the correct incentive
amount
depending upon the loan amount and tenor combination.

Its so confusing. Pls help..

Set up up table to look similar to this and between the term of the loans and
the table value add a column and insert a row number (1,2,3,4....). Do the
same thing for the loan amount. You will use the row numbers in the formula
below:

=INDEX(C4:G8,VLOOKUP(C11,A4:B8,2),HLOOKUP(D11,C2:G 3,2))

C11 and D11 are your value that you are going to lookup in the grid.

Loan
6,000 10,000 14,000 18,000 22,000
Term 1 2 3 4 5
24 1 24 40 56 72 88
36 2 30 65 100 135 170
48 3 36 90 144 198 252
60 4 42 115 188 261 334
72 5 48 140 232 324 416


HTH

"KDD" wrote:

I have a sales incentive grid with tenure of loan on one axis & loan size on
another axis. For e.g., for a loan size between $6000 to $9999 at a tenor of
24 months, teh incentive payable is $24 whereas for a loan size between
$10000 to &19999 at a tenor of 36 months, the incentive payable is $65. There
are a total of 7 loan bands on one axis and 5 tenor bands on the other axis.

How do i create a formula so that excel picks the correct incentive amount
depending upon the loan amount and tenor combination.

Its so confusing. Pls help..

This solution allows a non-standard loan amount, one that does not
necessarily appear in the header. To accommodate that, enter the
numbers you see in row 1 (eg 2 - 8) and color the font white so it
doesn't show up. I mocked up the data like this:

A.....B.......C.......D.......E...........F....... ..G.........H..........I
1...............2.......3.......4............5.... ......6..........7..........8
2...............0.......5000...10000...15000...200 00...25000...30000
3..............4999...9999...14999...19999...24999 ...29999...34999
4.....12......1.......3.........5...........7..... ....9..........11........13
5.....24......15......17......19.........21....... .23........25.........27
6.....36......29......31......33.........35....... .37........39.........41
7.....48......43......45......47.........49....... .51........53.........55
8.....60......57......59......61.........63....... .65........67.........69
9
10
11............Loan....Term....Incentive
12............12000...36......33

The values in C12 and D12 are the loan amount and loan duration. The
formula to determine the incentive in cell E12 is
=INDEX(B1:I8,MATCH(D12,B4:B8,0)+3,SUMPRODUCT(--(C12=$C$2:$I$2),--(C12<=$C$3:$I$3),$C$1:$I$1))

Outstanding. It works. Thanks mate.

1 last question though :

The loan amounts are not always rounded off to 6000, 10000 etc and neither
are the tenors. Tenors can be 32 months, 40 months etc. How do i tide over
this problem?
--
KDDXB


"StinkeyPete" wrote:

Set up up table to look similar to this and between the term of the loans and
the table value add a column and insert a row number (1,2,3,4....). Do the
same thing for the loan amount. You will use the row numbers in the formula
below:

=INDEX(C4:G8,VLOOKUP(C11,A4:B8,2),HLOOKUP(D11,C2:G 3,2))

C11 and D11 are your value that you are going to lookup in the grid.

Loan
6,000 10,000 14,000 18,000 22,000
Term 1 2 3 4 5
24 1 24 40 56 72 88
36 2 30 65 100 135 170
48 3 36 90 144 198 252
60 4 42 115 188 261 334
72 5 48 140 232 324 416


HTH

"KDD" wrote:

I have a sales incentive grid with tenure of loan on one axis & loan size on
another axis. For e.g., for a loan size between $6000 to $9999 at a tenor of
24 months, teh incentive payable is $24 whereas for a loan size between
$10000 to &19999 at a tenor of 36 months, the incentive payable is $65. There
are a total of 7 loan bands on one axis and 5 tenor bands on the other axis.

How do i create a formula so that excel picks the correct incentive amount
depending upon the loan amount and tenor combination.

Its so confusing. Pls help..

Simple to amend for changing conditions later, I would use two helper
columns, the first
=If(and(loan=5999,loan<10000),"A",if(and(loan=10 000,loan<20000),"B",if(
etc to decide the group of the loan amount.

For the second choice, I would ask can 36 months also include 30 months
or 42 months?, if so use a second helper column to decide the period
group.

With two groups decided, the test is


=if(and(loanGP="A",period=24),24,if(and(loanGP="B" ,period=36),65,if(and(
~~)))
etc to a depth of 7 statements only.

Hope this helps



KDD Wrote:
I have a sales incentive grid with tenure of loan on one axis & loan
size on
another axis. For e.g., for a loan size between $6000 to $9999 at a
tenor of
24 months, teh incentive payable is $24 whereas for a loan size
between
$10000 to &19999 at a tenor of 36 months, the incentive payable is $65.
There
are a total of 7 loan bands on one axis and 5 tenor bands on the other
axis.

How do i create a formula so that excel picks the correct incentive
amount
depending upon the loan amount and tenor combination.

Its so confusing. Pls help..


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=399012