I am trying to count the number of cells above a certain value in a table consecutively from right to left. I need it to stop counting if above 85%
<tbody>
</tbody>
So for the above table, the correct response (in a column F not pictured) would be:
0 (1st value is above 85%)
3 (4th value is above 85%)
5 (none are above 85%)
I have been trying to amend the following formula with a ctrl, shift enter:
={IFERROR(COLUMN(P2)-COLUMN(INDEX(D2:P2,,MATCH(9.99E+307,IF(D2:P2=0,1,"")))),0)}
Any help is greatly appreciated, and if the above formula is not feasible with this issue, that's fine. I'll take anything that works. I have a feeling I'm overlooking something pretty basic.
.50 | .88 | .75 | .86 | .90 |
.50 | .90 | .70 | .75 | .80 |
.20 | .80 | .82 | .80 | .84 |
<tbody>
</tbody>
So for the above table, the correct response (in a column F not pictured) would be:
0 (1st value is above 85%)
3 (4th value is above 85%)
5 (none are above 85%)
I have been trying to amend the following formula with a ctrl, shift enter:
={IFERROR(COLUMN(P2)-COLUMN(INDEX(D2:P2,,MATCH(9.99E+307,IF(D2:P2=0,1,"")))),0)}
Any help is greatly appreciated, and if the above formula is not feasible with this issue, that's fine. I'll take anything that works. I have a feeling I'm overlooking something pretty basic.