Using IF & AND command in conjunction

K

KDD

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

Bob Phillips

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

Bryan Hessey

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>=10000,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
 
S

StinkeyPete

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:G3,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
 
D

Dave O

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...20000...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))
 
K

KDD

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?
 
B

Bob Phillips

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