Returning Debtors Balance if Student is youngest in family

[Stef56]

I would like to only return the Family Debtor Balance against the youngest student, so that the total database = my Debtors Control account. For Example:

FAMILY CODE	Class	STUD ORDER	BALANCE
STEFJ	P1	3	1000
DOCHM	P1	1	2000
WEARA	S2	1	1000
MCARS	S3	2	1500
LAIRD	P2	1	1800
STEFJ	P5	1	1000
LAIRD	P3	2	1800
STEFJ	P2	4	1000
MCARS	S4	1	1500
STEFJ	P6	2	1000

<tbody>
</tbody>

Do I need to write a formula to look up the family code for multiples and if there are multiple children, only return the "balance" in another column if it is the highest number e.g. 3rd child is youngest? Any ideas would be appreciated. Thank you

 

[Stef56]

I've got to this stage =INDEX($D$2:$D$10,MATCH(MAX(ISNUMBER(SEARCH(B2,$C$2:$C$10))*RIGHT($C$2:$C$10,1)),ISNUMBER(SEARCH(B2,$C$2:$C$10))*RIGHT($C$2:$C$10,1),0) (with array curly brackets), which gives me the correct Balance returning against the highest number ie. MAX. I now need the other children to be zero.

I've added a column which includes Family Code and Stud Order which is C2-C10 in above formula. I think I need an if statement !!

 

[skywriter]

Let me know if this is what you want. If not tell me what results you want and the criteria to arrive at that result.

Excel 2010
	A	B	C	D
1	FAMILY CODE	Class	STUD ORDER	BALANCE
2	STEFJ	P1	3	100
3	DOCHM	P1	1	200
4	WEARA	S2	1	300
5	MCARS	S3	2	400
6	LAIRD	P2	1	500
7	STEFJ	P5	1	600
8	LAIRD	P3	2	700
9	STEFJ	P2	4	800
10	MCARS	S4	1	900
11	STEFJ	P6	2	1000
12
13	Family Code to Lookup:	MCARS
14	Balance:	400
Sheet2

Cell Formulas
Range	Formula
B14	{=INDEX(D2:D11,SUMPRODUCT(((A2:A11=B13)*(C2:C11=MAX(IF(A2:A11=B13,C2:C11)))*(ROW(C2:C11)-ROW(C2)+1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Stef56]

Let me know if this is what you want. If not tell me what results you want and the criteria to arrive at that result.

Excel 2010

	A	B	C	D
1	FAMILY CODE	Class	STUD ORDER	BALANCE
2	STEFJ	P1	3	100
3	DOCHM	P1	1	200
4	WEARA	S2	1	300
5	MCARS	S3	2	400
6	LAIRD	P2	1	500
7	STEFJ	P5	1	600
8	LAIRD	P3	2	700
9	STEFJ	P2	4	800
10	MCARS	S4	1	900
11	STEFJ	P6	2	1000
12
13	Family Code to Lookup:	MCARS
14	Balance:	400

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

Sheet2​

Array Formulas Cell Formula B14 {=INDEX(D2:D11,SUMPRODUCT(((A2:A11=B13)*(C2:C11=MAX(IF(A2:A11=B13,C2:C11)))*(ROW(C2:C11)-ROW(C2)+1))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thank you for trying to help me. I wasn't clear enough and I've changed the data to help clarify it. This is the result I would like: Only the Balance coming across to a new column if it relates to the youngest child (which is the highest number in Student Order). Hence my formula worked to make sure the highest balance came across, but I need a statement saying if the Student Order is less than the Max number returned, then Zero. Unfortunately it isn't just a data extraction of a record, but the whole column which needs to be recalculated for further manipulation by Class. There are 1100 children and 800 families. Thank you

A	B	C	D	E	Family	Balance
1	FAMILY CODE		Class	STUD ORDER	BALANCE	Youngest Student
2	STEFJ	STEFJ3	P1	3	100	0
3	DOCHM	DOCHM1	P1	1	200	200
4	WEARA	WEARA1	S2	1	300	300
5	MCARS	MCARS2	S3	2	400	400
6	LAIRD	LAIRD1	P2	1	500	0
7	STEFJ	STEFJ1	P5	1	100	0
8	LAIRD	LAIRD2	P3	2	500	500
9	STEFJ	STEFJ4	P2	4	100	100
10	MCARS	MCARS1	S4	1	900	0
11	STEFJ	STEFJ2	P6	2	100	0
12

<colgroup><col span="6"><col></colgroup><tbody>
</tbody>

 

[skywriter]

You can enter the formula once with Control Shift Enter and then copy down. Make sure the ranges reflect the actual ranges on your spreadsheet.

Excel 2010
	A	B	C	D	E	F
1	FAMILY CODE		Class	STUD ORDER	FAMILY BALANCE	Youngest
2						Student
3	DOCHM	DOCHM1	P1	1	200	200
4	WEARA	WEARA1	S2	1	300	300
5	MCARS	MCARS2	S3	2	400	400
6	LAIRD	LAIRD1	P2	1	500	0
7	STEFJ	STEFJ1	P5	1	100	0
8	LAIRD	LAIRD2	P3	2	500	500
9	STEFJ	STEFJ4	P2	4	100	100
10	MCARS	MCARS1	S4	1	900	0
11	STEFJ	STEFJ2	P6	2	100	0
Sheet3

Cell Formulas
Range	Formula
F3	{=IF($D3=MAX(IF($A$3:$A$11=$A3,$D$3:$D$11)),$E3,0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Stef56]

You can enter the formula once with Control Shift Enter and then copy down. Make sure the ranges reflect the actual ranges on your spreadsheet.


Excel 2010

	A	B	C	D	E	F
1	FAMILY CODE		Class	STUD ORDER	FAMILY BALANCE	Youngest
2						Student
3	DOCHM	DOCHM1	P1	1	200	200
4	WEARA	WEARA1	S2	1	300	300
5	MCARS	MCARS2	S3	2	400	400
6	LAIRD	LAIRD1	P2	1	500	0
7	STEFJ	STEFJ1	P5	1	100	0
8	LAIRD	LAIRD2	P3	2	500	500
9	STEFJ	STEFJ4	P2	4	100	100
10	MCARS	MCARS1	S4	1	900	0
11	STEFJ	STEFJ2	P6	2	100	0

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula F3 {=IF($D3=MAX(IF($A$3:$A$11=$A3,$D$3:$D$11)),$E3,0)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Works perfectly. Thank you so much. I've learnt heaps trying to solve this and got carried away with lesson 1001 on youtube.

 

[skywriter]

My pleasure.

 

[Peter_SSs]

If you did prefer a non Ctrl+Shift+Enter formula and you are using Excel 2010 or later ..

Excel Workbook
	A	B	C	D	E	F
1	FAMILY CODE		Class	STUD ORDER	FAMILY*BALANCE	Youngest
2						Student
3	DOCHM	DOCHM1	P1	1	200	200
4	WEARA	WEARA1	S2	1	300	300
5	MCARS	MCARS2	S3	2	400	400
6	LAIRD	LAIRD1	P2	1	500	0
7	STEFJ	STEFJ1	P5	1	100	0
8	LAIRD	LAIRD2	P3	2	500	500
9	STEFJ	STEFJ4	P2	4	100	100
10	MCARS	MCARS1	S4	1	900	0
11	STEFJ	STEFJ2	P6	2	100	0
Youngest Only