Hi, I have the following little table. I'd like a formula that finds the number of values where the cumulative total (of column G) is less than some threshold, tx.
I tried
but OFFSET doesn't seem to be arrayable like that. Obviously, this would be trivial with a helper column, but for certain reasons that is not possible.
So the correct answer here should be 10.
<tbody>
</tbody>
I tried
Code:
{=MIN((SUM(OFFSET(G3,0,0,F3:F19))>tx)*F3:F19)-1}
but OFFSET doesn't seem to be arrayable like that. Obviously, this would be trivial with a helper column, but for certain reasons that is not possible.
So the correct answer here should be 10.
F | G | H | I | J | |
---|---|---|---|---|---|
2 | rank | % | |||
3 | 1 | 0.112106 | tx | 0.8 | |
4 | 2 | 0.110982 | |||
5 | 3 | 0.091959 | |||
6 | 4 | 0.082163 | |||
7 | 5 | 0.073292 | |||
8 | 6 | 0.072408 | |||
9 | 7 | 0.071646 | |||
10 | 8 | 0.061647 | |||
11 | 9 | 0.060114 | |||
12 | 10 | 0.057566 | |||
13 | 11 | 0.050342 | |||
14 | 12 | 0.048227 | |||
15 | 13 | 0.043207 | |||
16 | 14 | 0.039405 | |||
17 | 15 | 0.012914 | |||
18 | 16 | 0.007603 | |||
19 | 17 | 0.004417 |
<tbody>
</tbody>
Sheet1