KKaroullas
New Member
- Joined
- Feb 11, 2018
- Messages
- 1
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?
[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Name[/TD]
[TD="class: xl65, width: 64"]Divider[/TD]
[TD="class: xl65, width: 64"]Value[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-1.4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]-1.7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]-0.4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-0.1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-0.3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]-0.5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl66"]Allan[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]-0.5[/TD]
[/TR]
[TR]
[TD="class: xl65"]
10[/TD]
[TD="class: xl65"]
Brian[/TD]
[TD="class: xl65"]
11 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]11[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]-2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]-0.6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-0.1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]14[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-1.2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-1.6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]16[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]17[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]-1.56667[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]18[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]-0.2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]19[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]20[/TD]
[TD="class: xl66"]Brian[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]-0.06667[/TD]
[/TR]
[TR]
[TD="class: xl65"]
21
[/TD]
[TD="class: xl65"]
Charles[/TD]
[TD="class: xl65"]
8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]22[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]0.1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]23[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-0.4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]24[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-0.83333[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]25[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]-0.8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]26[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]-0.8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]27[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]-0.8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]28[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-2.2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]29[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-5.4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-1.4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]31[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]-1[/TD]
[/TR]
</tbody>[/TABLE]
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?
[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Name[/TD]
[TD="class: xl65, width: 64"]Divider[/TD]
[TD="class: xl65, width: 64"]Value[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-1.4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]-1.7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]-0.4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-0.1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-0.3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]-0.5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl66"]Allan[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Allan[/TD]
[TD="class: xl65"]-0.5[/TD]
[/TR]
[TR]
[TD="class: xl65"]
10[/TD]
[TD="class: xl65"]
Brian[/TD]
[TD="class: xl65"]
11 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]11[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]-2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]-0.6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-0.1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]14[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-1.2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-1.6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]16[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]17[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]-1.56667[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]18[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]-0.2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]19[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]20[/TD]
[TD="class: xl66"]Brian[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"]-0.06667[/TD]
[/TR]
[TR]
[TD="class: xl65"]
21
[/TD]
[TD="class: xl65"]
Charles[/TD]
[TD="class: xl65"]
8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]22[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]0.1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]23[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-0.4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]24[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]-0.83333[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]25[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]-0.8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]26[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]-0.8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]27[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]-0.8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]28[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-2.2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]29[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-5.4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-1.4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]31[/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]-2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Charles[/TD]
[TD="class: xl65"]-1[/TD]
[/TR]
</tbody>[/TABLE]