Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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.. |
#2
|
|||
|
|||
=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.. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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.. |
#5
|
|||
|
|||
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)) |
#6
|
|||
|
|||
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.. |
#7
|
|||
|
|||
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.. |
#8
|
|||
|
|||
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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|