New to this forum, Need help :)

[walkonrain]

Hi All,
Need a help on excel, found this ocean of knowledge site while surfing and enrolled quickly

Here is my issue :

Type

Rank

SMID

Unit Dt

MID

MName

Loan ID

RD-Month

FD-Month

Date

Amt

Comm RD

Comm FD

Renewal

11

A

23/6

R2010

AB

LR001

12

-

1000

New

9

B

24/6

R2011

CD

LR002

15

5000

<tbody>
</tbody>

Table explanation:
Type : Either Renewal or New
Rank : position
SMID: Senior Member ID
Unitdate: Date
MID: Member ID
MName: Member Name
Loan ID: Loan ID
RD-Month: Here is the no# of month RD taken
FD-Month: Its the no of month FD taken
Date
AMount: total amount taken in the perticular month
Comm RD: Commission for RD
Comm FD: Commission for FD

What i am looking for : i am trying to calculate the commission ( last 2 columns)
Condition is : for example " If for a person the Rank is 11, and the tenure for the loan is 12 month ( RD-Month column), and i also have the commission table which is given below, i need to calculate the Commission for RD ( second last column) which i am unable to figure out.

In the same way i need to calculate the commission for FD ( the last coulmn) given the same condition above.

Below table is the rank ( horizontal ) and month in vertical (extreme left)
Rest are the commission for each rank month wise.

Rank	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
12	2.0	3.0	4.0	5.0	6.0	7.0	8.0	19.0	10.0	1.0	12.0	13.0	14.0	15.0	16.0
15	2.5	3.5	4.5	5.5.	6.5	7.5	8.5	9.5	10.5	11.5	12.5	13.5	14.5	15.5	16.5

<tbody>
</tbody>

Similarly for FD commission calculation here is the table below:

Rank	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
12	2.8	3.9	4.9	6.0	8.0	9.0	10.0	11.0	12.0	13.0	14.0	15.0	16.0	17.0
15	2.5	3.5	4.5	5.5.	6.5	7.5	8.5	9.5	10.5	11.5	12.5	13.5	14.5	15.5	16.5

<tbody>
</tbody>

I am unable to paste the exact Excel format, my first part is on one sheet and rest 2 tables in on ther sheet

I tried my level best to explain what help i need, If any further quaries, please let me know

Thanks,
Walkonrain

 

[bertie]

I have sent an email containing the updated file, subject line= "Bertie".

For future reference, don't post your email address in an open forum, send it via PM's if necessary.

If you have any response to my updates, please post back here. Others may be following the thread to get ideas for their own project.

Bertie

 

[walkonrain]

Hi Berti,
Sorry again...
I tried the same formula but its showing #N/A

i might have made mistake and not mentioning that :
IF the rank is ( whatever the user puts in ) and RD is the tenure ( this is also put by the user ) and Amount ( column M ) in my sheet ( this also user will put in ) then commission calculated automatically.

I know i am bugging you and really appriciate your help, please let me know how do i do this.
** amount column M is very imp because user will put the amount for the month and based on that commission get generated in both the columns ( N and O )

Thanks again..

 

[bertie]

I tried the same formula but its showing #N/A

Referring to the file you uploaded.
On the Ranjan sheet, the value in cell B3 =11:

Excel 2007
	A	B
2	Type	Rank
3	Renewal	11
Ranjan

This value is not present in the RD sheet, lookup range, hence the #N/A error:

Excel 2007
	A
1	Rank
2	12
3	15
4	18
5	21
6	24
7	36
8	48
9	60
RD

If you change the values B3=12 and J3=12

Excel 2007
	A	B	C	D	E	F	G	J
2	Type	Rank	Senior M.ID	Unit Date	Member ID	M. Name	Loan ID No	RD
3	Renewal	12	Ramendra Mondal	23/6/2013	R2010	A Biswas	LR001	12
Ranjan

Then the formula will return the value at the intersect, i.e., 13 in our example.

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Rank	1	2	3	4	5	6	7	8	9	10	11	12
2	12	2.0	3.0	4.0	5.0	6.0	7.0	8.0	9.0	10.0	11.0	12.0	13.0
RD

To summarize. you will get an #N/A error if there are no values in column B or J in the Ranjan sheet. We could use IF statements to handle this, but the formula will get messy. It is best you understand what it is doing before we go down that road.

 

[bertie]

[edit] post deleted - ExcelToHtnl didn't parse formula correctly.

N2 messy formula

=IF(B4="",0,IF(J4="",0,IF(ISERROR(MATCH(B4,RD!$A$1:$A$9,0)),0,INDEX(RD!$A$1:$P$9,MATCH(B4,RD!$A$1:$A$9,0),MATCH(J4,RD!$A$1:$P$1,0)))))

O2 messy formula

=IF(B4="",0,IF(K4="",0,IF(ISERROR(MATCH(B4,FD!$A$1:$A$15,0)),0,INDEX(FD!$A$1:$P$15,MATCH(B4,RD!$A$1:$A$9,0),MATCH(K4,FD!$A$1:$P$1,0)))))

 

[walkonrain]

Hi,
We missed out one vital information in the sheet. I have send a mail to you Please do check ..

thanks..

 

[walkonrain]

Oops... sorry.. i missed out what you said.. "Others may be following the thread to get ideas for their own project."

We missed out the Amount part which is main calculation based on % and an i am unable to do that.. please help sir..

 

[bertie]

Hi Dvuti,

Excel 2007

	L	M	N	O
1	June 13		Commission
2	Date	Amount	RD	FD
4		50	6.5	9.6

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Ranjan​

Let me see if I understand what you are looking for using the above sample data.
M4 - user enters AMOUNT value
N4 - formula to pull through commission percentage PERC from RD sheet.

If you want the commission amount, PERC*AMOUNT, then just add; *(M4/100) onto the end of the formula in N4.

Excel 2007

	L	M	N	O
1	June 13		Commission
2	Date	Amount	RD	FD
4		50	3.25	9.6

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Ranjan​

Does this help?
See email for updated sheet.