Excel - 没有辅助列的累积阈值以下的条目数 [英] Excel - Count of entries below cumulative threshold without helper column

问题描述

我有以下的小桌子。我想要一个公式，找到累积总数（B列）小于某个阈值（tx）的值的数量。

我试过

  {= MIN（（SUM（OFFSET B1,0,0，A1：A17））> tx）* A1：A17）-1} 
  

但是OFFSET似乎不像这样排列。显然，这是一个帮忙的列，但是由于某些原因是不可能的。

所以这里的正确答案应该是10。

tx = .8

  AB 
 1 0.112106465 
 2 0.110981698 
 3 0.091959216 
 4 0.082163441 
 5 0.073292066 
 6 0.072407529 
 7 0.071646289 
 8 0.061646797 
 9 0.06011448 
 10 0.057566381 
 11 0.050341978 
 12 0.048227061 
 13 0.043207335 
 14 0.03940462 
 15 0.012914194 
 16 0.007603446 
 17 0.004417003 
  

解决方案

你不是真的在寻找一个

I have the following little table. I'd like a formula that finds the number of values where the cumulative total (of column B) is less than some threshold (tx).

I tried

{=MIN((SUM(OFFSET(B1,0,0,A1:A17))>tx)*A1:A17)-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.

tx = .8

A   B
1   0.112106465
2   0.110981698
3   0.091959216
4   0.082163441
5   0.073292066
6   0.072407529
7   0.071646289
8   0.061646797
9   0.06011448
10  0.057566381
11  0.050341978
12  0.048227061
13  0.043207335
14  0.03940462
15  0.012914194
16  0.007603446
17  0.004417003

解决方案

You're not really looking for a MIN; rather it should be MAX that follows your condition.

In E7 as a standard (non-array) formula,

=AGGREGATE(14, 6, ROW(1:17)/(SUBTOTAL(9, OFFSET(B1, 0, 0, ROW(1:17), 1))<D7), 1)

      

这篇关于Excel - 没有辅助列的累积阈值以下的条目数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！