Finding the last cell with value, with multiple criteria

[KKaroullas]

Hello All,

I need a formula that is able to find to the last value (Excluding 0 and empty cells) and then divide it by the divider next to it.
For example Allan's value must be -0.5 (row 8) because at the row 9 there is a 0.
Brian's value must be -0.06667 because row 20 is zero and row 19 is empty so the value must be tracked in row 18 and then divide it by the Divider. (-0.2/3=-0.06667).
Charles's value is the last row dividing it by the divider.

How can I built such a formula with this criteria?

	Name	Divider	Value
1	Allan	5	-1.4
2	Allan	4	-1.7
3	Allan	3	-0.4
4	Allan	2	-0.1
5	Allan	2	0
6	Allan	2
7	Allan	2	-0.3
8	Allan	1	-0.5
9	Allan	1	0		Allan	-0.5
10	Brian	11
11	Brian	8	-2
12	Brian	6	-0.6
13	Brian	5	-0.1
14	Brian	5	-1.2
15	Brian	5	-1.6
16	Brian	4
17	Brian	3	-1.56667
18	Brian	3	-0.2
19	Brian	2
20	Brian	2	0		Brian	-0.06667
21	Charles	8
22	Charles	7	0.1
23	Charles	5	-0.4
24	Charles	5	-0.83333
25	Charles	4	-0.8
26	Charles	3	-0.8
27	Charles	3	-0.8
28	Charles	2	-2.2
29	Charles	2	-5.4
30	Charles	2	-1.4
31	Charles	2	-2		Charles	-1

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

Try these, copied down.

Book1
	A	B	C	D	E	F
1	Name	Divider	Value
2	Allan	5	-1.4
3	Allan	4	-1.7
4	Allan	3	-0.4
5	Allan	2	-0.1
6	Allan	2	0
7	Allan	2
8	Allan	2	-0.3
9	Allan	1	-0.5
10	Allan	1	0		Allan	-0.5
11	Brian	11
12	Brian	8	-2
13	Brian	6	-0.6
14	Brian	5	-0.1
15	Brian	5	-1.2
16	Brian	5	-1.6
17	Brian	4
18	Brian	3	-1.56667
19	Brian	3	-0.2
20	Brian	2
21	Brian	2	0		Brian	-0.066666667
22	Charles	8
23	Charles	7	0.1
24	Charles	5	-0.4
25	Charles	5	-0.83333
26	Charles	4	-0.8
27	Charles	3	-0.8
28	Charles	3	-0.8
29	Charles	2	-2.2
30	Charles	2	-5.4
31	Charles	2	-1.4
32	Charles	2	-2		Charles	-1
Last Value

Cell Formulas
Range	Formula
E2	=IF(A2=A3,"",A2)
F2	=IF(E2="","",INDEX(C$2:C2,AGGREGATE(14,6,(ROW(C$2:C2)-ROW(C$2)+1)/((A$2:A2=A2)*(C$2:C2<>0)),1))/INDEX(B$2:B2,AGGREGATE(14,6,(ROW(C$2:C2)-ROW(C$2)+1)/((A$2:A2=A2)*(C$2:C2<>0)),1)))

 

[Aladin Akyurek]

E2 like Peter suggests...

=IF(A2=A3,"",A2)

In F2 enter and copy down:

=IF($E2="","",LOOKUP(9.99E+307,1/($A$2:$A$32=$A2)/$C$2:$C$32,$C$2:$C$32/$B$2:$B$32))