Count of entries below cumulative threshold without helper column

[Dashnick]

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

{=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​

 

[Domenic]

Try...

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(G2:G18,,,ROW(G2:G18)-ROW(G2)+1))<=J2))

Hope this helps!

 

[Dashnick]

Thanks Domenic, but that doesn't seem to work - still getting 0...

 

[Dashnick]

Nevermind - it works! The rows were just misaligned... Thanks!!

 

[Dashnick]

Sorry - would you just mind explaining what SUMPRODUCT() and SUBTOTAL() are doing here that SUM() itself wouldn't normally do? (SUMPRODUCT() is only taking one argument...)

 

[Domenic]

Basically, OFFSET returns an array of references, where each reference refers to a range. In turn, each range is passed to the SUBTOTAL function. And, since the function number for SUBTOTAL is 9, it sums each range and returns an array of values. This array of values is then passed to SUMPRODUCT, which calculates how many times each sum is less than or equal to the target amount. Here are a few things to keep in mind...

ROW(G2:G18)-ROW(G2)+1 returns the following array of values:

1
2
3
'
'
'
17

OFFSET(G2:G18,,,ROW(G2:G18)-ROW(G2)+1) returns an array of references to the following ranges:

G2
G2:G3
G2:G4
'
'
'
G2:G18

SUBTOTAL(9,OFFSET(G2:G18,,,ROW(G2:G18)-ROW(G2)+1)) returns the following array of values:

0.112106
0.223088
0.315047
'
'
'
0.999998

And so it's evaluated as follows:

=SUMPRODUCT(--({0.112106;0.223088;0.315047 . . . 0.999998}<=0.8))

=SUMPRODUCT(--({TRUE;TRUE;TRUE . . . FALSE}))

=SUMPRODUCT({1;1;1; . . . 0})

Based on your sample data, SUMPRODUCT returns 10. In Excel, you can see for yourself the step by step evaluation of the formula...

1) Select the cell containing the formula.

2) On the ribbon, select Formulas > Formula Auditing > Evaluate Formula

3) Click on "Evaluate" for evaluating each part of the formula.

If you have any questions, don't hesitate to post back.

Hope this helps!