获取一个数字出现在其他数字之间的平均次数 [英] Getting the average number of times a number appears between other numbers
问题描述
在我的单元格中,我有三个可能的不同值,1,0或ND.我想获得1到0之间出现0的平均次数.
但是我似乎无法理解,我一直在玩=AVERAGE(COUNTIF(A60:C60, 0))
.看起来很简单,但我无法弄清楚.
因此,我希望整个行中1到0之间出现0的平均次数.
仅显示公式的公式的解决方案
此解决方案不需要VBA,但需要一个帮助行.
想象一下第1行中的以下数据.
第2行是用于计算平均值的助手行:
- A2为空.
- B2编写公式
=IF(B1=1,COUNTIF($A$1:B1,0)-SUM($A$2:A2),"")
并将其复制到右侧.
然后使用=AVERAGE(2:2)
第2行中的数字是第1行中1
前面的0
计数.所有ND
都将被忽略.
但这与
是相同的结果=COUNTIF(1:1,0)/COUNTIF(1:1,1) '<-- = average
因为=AVERAGE
实际上是零的总和除以1的数量.因此,您只需要这个公式,甚至不需要帮助列.
In my cells I have three possible different values, 1 ,0 or ND. I want to get the average number of times 0 appears between the 1s.
But I cannot seem to get it, I have been playing around with =AVERAGE(COUNTIF(A60:C60, 0))
. It seems simple but I cannot figure it out.
So I want the average number of times that 0 appears between 1s in the whole row.
imagine each cell is a day. 1s represent fail and 0 represent pass. If the cell has failed that day it is fixed that night. what i want to find out is when i fix a failed cell what is the average amount of days pass before that cell fails again
A formula only solution to show the idea of how to calculate it
This solution doen't need VBA but a helper row.
Imagine the following data in row 1.
Row 2 is a helper row to calculate the average:
- A2 is empty.
- B2 write the formula
=IF(B1=1,COUNTIF($A$1:B1,0)-SUM($A$2:A2),"")
and copy it to the right.
The average is then calculated with =AVERAGE(2:2)
The numbers in row 2 are the count of 0
that are in front of the 1
in row 1. All ND
get just ignored.
But this is the same result as
=COUNTIF(1:1,0)/COUNTIF(1:1,1) '<-- = average
Because the =AVERAGE
is actually the sum of zeros devided by the amount of ones. So this formula is all you need, even no helper column is needed.
这篇关于获取一个数字出现在其他数字之间的平均次数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!