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

 

[walkonrain]

BUMP

 

[jamtay317]

hello, welcome to the forum, is there away that you could upload a copy of this to the forum here where we can have a better look at the sheet. you can change any sensitive information. use google docs or dropbox or something like that and past the link here

 

[bertie]

Hi and Welcome to the forum.

You can use the INDEX, MATCH functions to return the value you want.

The MATCH function will return the ROW and COLUMN reference for the value we want returned.

Consider Sheet1:

Excel 2007
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Rank	SMID	Unit Dt	MID	MName	Loan ID	RD-Month	FD-Month	Date	Amt	Comm RD	Comm FD	Row	Column
2	11	A	23-Jun	R2010	AB	LR001	12	-		1000	13.5		3	13
Sheet1

Cell Formulas
Range	Formula
L2	=INDEX(RD!$A$1:$P$3,N2,O2)
N2	=MATCH(B2,RD!$A$1:$A$3,0)
O2	=MATCH(H2,RD!$A$1:$P$1,0)

Here is the Lookup Sheet, named RD.

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Rank	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
2	9	2	3	4	5	6	7	8	19	10	1	12	13	14	15	16
3	11	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
RD

I used columns N and O to help explain how the MATCH function works.
We can incorporate the formula in these cells into the formula in L2.

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Type	Rank	SMID	Unit Dt	MID	MName	Loan ID	RD-Month	FD-Month	Date	Amt	Comm RD	Comm FD
2	Renewal	11	A	23-Jun	R2010	AB	LR001	12	-		1000	13.5
Sheet1

Cell Formulas
Range	Formula
L2	=INDEX(RD!$A$1:$P$3,MATCH(B2,RD!$A$1:$A$3,0),MATCH(H2,RD!$A$1:$P$1,0))

Hope this helps,
Bertie

 

[walkonrain]

Hi and Welcome to the forum.

You can use the INDEX, MATCH functions to return the value you want.

The MATCH function will return the ROW and COLUMN reference for the value we want returned.

Consider Sheet1:
Excel 2007

	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Rank	SMID	Unit Dt	MID	MName	Loan ID	RD-Month	FD-Month	Date	Amt	Comm RD	Comm FD	Row	Column
2	11	A	23-Jun	R2010	AB	LR001	12	-		1000	13.5		3	13

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

Sheet1​

Worksheet Formulas Cell Formula L2 =INDEX(RD!$A$1:$P$3,N2,O2) N2 =MATCH(B2,RD!$A$1:$A$3,0) O2 =MATCH(H2,RD!$A$1:$P$1,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Here is the Lookup Sheet, named RD.
Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Rank	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
2	9	2	3	4	5	6	7	8	19	10	1	12	13	14	15	16
3	11	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

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

RD​

I used columns N and O to help explain how the MATCH function works.
We can incorporate the formula in these cells into the formula in L2.

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Type	Rank	SMID	Unit Dt	MID	MName	Loan ID	RD-Month	FD-Month	Date	Amt	Comm RD	Comm FD
2	Renewal	11	A	23-Jun	R2010	AB	LR001	12	-		1000	13.5

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

Sheet1​

Worksheet Formulas Cell Formula L2 =INDEX(RD!$A$1:$P$3,MATCH(B2,RD!$A$1:$A$3,0),MATCH(H2,RD!$A$1:$P$1,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hope this helps,
Bertie

Hi Berti,
Thank for your tremendous help, thank you very much.
I tried on my sheet and shome how i am unable to get the result. Is there anyway i can upload the excelsheet here !.. if possible let me know the way.
secondly, as per the formula as you said ( indexing) its giving me the result 0... moreover my critaria is if the "RanK" changes commission automatically changes. My moto is the user will fill the rank on the first sheet and rest data including the amount and commission automatically calculated and shown in commission column mentioned in the first thread.

Eagerly waiting for your reply as fire is on my back

Thanks,
Walkonrain

 

[jamtay317]

use google docs or dropbox or something like that and past the link here

you can do this

 

[bertie]

You will probably have to play about with the ranges/sheet names in the example I posted.

There is no facility to upload files to the MrExcel forum.
As Jamtay mentioned in post #3, you can use GoolgleDocs or Dropbox to store the file and paste the link back here.
Just post a sample of you workbook layout.
NB - remove any sensitive data.

 

[walkonrain]

Thanks,
here you go..
https://docs.google.com/file/d/0Bz1a9ZsjJ_BMM3puVDlyeGpkcVE/edit?usp=sharing

You can find the file here.
You will see the rank column in the beginning and there is RD and FD sheet on the bottom, if rank changes and the month , with given amount commission need to be calculated.

Hope i am able to explain in simple way
Thanks all for your effor sir.

Regards,
Walkonrain

 

[walkonrain]

One more request, you can mail me the file after correcting the formula

walkonrain@gmail.com

thanks again !!
Cheers..


https://docs.google.com/file/d/0Bz1a9ZsjJ_BMM3puVDlyeGpkcVE/edit?usp=sharing

again shared for downloading and editing..

 

[bertie]

Here is the amended formula:

Excel 2007
	A	B	C	D	E	F	G	J	K	L	M	N	O
1										June 13		Commission
2	Type	Rank	Senior M.ID	Unit Date	Member ID	M. Name	Loan ID No	RD	FD	Date	Amount	RD	FD
3	Renewal	12	Ramendra Mondal	23/6/2013	R2010	A Biswas	LR001	12	12		100	13	14
Ranjan

Cell Formulas
Range	Formula
N3	=INDEX(RD!$A$1:$P$9,MATCH(B3,RD!$A$1:$A$9,0),MATCH(J3,RD!$A$1:$P$1,0))
O3	=INDEX(FD!$A$1:$P159,MATCH(B3,FD!$A$1:$A$15,0),MATCH(K3,FD!$A$1:$P$1,0))

N3 formula
The range you are looking up on the RD sheet is RD!$A$1:$P$9
To get the row the lookup value is on, the range you are looking over is: MATCH(B3,RD!$A$1:$A$9,0)
If you add more data you will need to edit these ranges.

O3 formula
Lookup range FD!$A$1:$P159
Row lookup range MATCH(B3,FD!$A$1:$A$15,0
If you add more data, edit these ranges

If the rank on sheet Ranjan is not present on the lookup sheets, the formula will return a #N/A error.
i.e., change the value in cell B3 to 11, then 12.

Bertie