在我的单元格中，我有三个可能的不同值，1,0或ND.我想获得1到0之间出现0的平均次数. 但是我似乎无法理解，我一直在玩=AVERAGE(COUNTIF(A60:C60, 0)).看起来很简单，但我无法弄清楚. 因此，我希望整个行中1到0之间出现0的平均次数.
想象每个单元格是一天. 1表示失败，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) '<-- = averageBecause 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.