Using IF & AND command in conjunction
 

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..

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

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))

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..

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..

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..