Excel - 在没有帮助列的情况下低于累积阈值的条目数 [英] Excel - Count of entries below cumulative threshold without helper column
本文介绍了Excel - 在没有帮助列的情况下低于累积阈值的条目数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有下面的小桌子.我想要一个公式来查找累积总数(B 列)小于某个阈值 (tx) 的值的数量.
我试过了
{=MIN((SUM(OFFSET(B1,0,0,A1:A17))>tx)*A1:A17)-1}
但 OFFSET 似乎不像那样可排列.显然,这对于辅助列来说是微不足道的,但由于某些原因,这是不可能的.
所以这里的正确答案应该是 10.
tx = .8
A B1 0.1121064652 0.1109816983 0.0919592164 0.0821634415 0.0732920666 0.0724075297 0.0716462898 0.0616467979 0.0601144810 0.05756638111 0.05034197812 0.04822706113 0.04320733514 0.0394046215 0.01291419416 0.00760344617 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屋!
查看全文