ShiftyThor
New Member
- Joined
- Mar 4, 2013
- Messages
- 14
Hi all,
I am trying to work a formula where I can get the best 5 values out of the last 10 positive values. e.g.
<colgroup><col><col span="12"><col><col></colgroup><tbody>
</tbody>
Therefore I need the formula in N2 to work find the last 10 positive values in the row A2:L2 (F2 and K2 will not be seleced), and then select the top 5 of those values being A2, C2, E2, G2, L2.
I think I have to use the Offset, Match and Max formula but am not sure.
Thanks all, hope the above makes sense.
I am trying to work a formula where I can get the best 5 values out of the last 10 positive values. e.g.
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
1 | Positive | Positive | Positive | Positive | Positive | Null | Positive | Positive | Positive | Positive | Null | Positive | This will be the Value | |
2 | 100.00 | 50.00 | 90.00 | 40.00 | 90.00 | 0.00 | 90.00 | 80.00 | 75.00 | 80.00 | 0.00 | 80.00 | 450.00 | |
3 | Count | Leave | Count | Leave | Count | Count | Leave | Leave | Leave | Count | I need formula above |
<colgroup><col><col span="12"><col><col></colgroup><tbody>
</tbody>
Therefore I need the formula in N2 to work find the last 10 positive values in the row A2:L2 (F2 and K2 will not be seleced), and then select the top 5 of those values being A2, C2, E2, G2, L2.
I think I have to use the Offset, Match and Max formula but am not sure.
Thanks all, hope the above makes sense.