获取列中值的前1/3，后1/3和后1/3的平均值 [英] Getting average of top 1/3, second 1/3, and last 1/3 of values in column

问题描述

我有一列包含数字和一个参考列.我正在尝试将数字列分为前三分之一，后三分之一和后三分之一，并取每个的平均值.

I have a column with numbers and a reference column. I'm trying to separate the numbers column into first third, second third, and last third and take the average of each.

Values  Ref column
 1.7     cow
 2.3     cow
 2.6     cow
 1.8     sheep
 1.3     sheep
 2.2     sheep
 1.5     sheep
 1.2     sheep
 2.3     sheep
 1.5     goose
 2.5     goose

例如，绵羊"的前两个值，后两个和后两个的平均值.换句话说，我要取绵羊"附近每1/3个单元的平均值.

So, for example, the average of the first two values for "sheep", second two, and last two. In other words, I want to take the average of each 1/3 of cells adjacent to "sheep".

推荐答案

假设这里有7个羊值，并且您想进行加权均值(例如，第一个均值将从第一个均值中计算出)两只羊加上第三只的三分之一)?

Well supposing there were 7 sheep values and you wanted to do a weighted mean (e.g. the first mean would be calculated from the first two sheep plus a third of the third one)?

我已经尝试了一种通用解决方案，可以将任意数量的动物分为任意数量的分数，并求出它们的平均值.我的方法是使用@Barry Houdini优雅的重叠公式，如

I have attempted a general solution for this dividing any number of animals into any number of fractions and finding their average values. My approach is to use the elegant overlap formula from @Barry Houdini as used here and work out the overlap between the intervals (in the case of 7 animals divided into 3):

0 to 2.33

2.33 to 4.67

4.67 to 7

和动物的数量

0 to 1

1 to 2

2 to 3

以此类推.

在H4中

=IF(ROWS($1:1)<=$H$2,ROWS($1:1)/$H$2*COUNTIF(B$2:B$16,$G$2),"")

在G4中

=IF(H4="","",H4-COUNTIF(B$2:B$16,$G$2)/$H$2)

I4的主要公式是

=IF(H4="","",SUM(TEXT(IF(C$2:C$16<H4,C$2:C$16,H4)-IF((C$2:C$16-1)>G4,C$2:C$16-1,G4),"general;\0")
*A$2:A$16*(B$2:B$16=$G$2))/(COUNTIF(B$2:B$16,$G$2)/$H$2))

输入为数组公式.

通过更改H2中的数字，可以将分数更改为一半，四分之一等.

The fractions can be changed to halves, quarters etc. by changing the number in H2.

这篇关于获取列中值的前1/3，后1/3和后1/3的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！