问题描述
Excel中每行的结果值应为1,2,3 ,4或5。
一个值将是前20%,值为5的是底部的20%。
这是我目前使用SEEMS的公式,但是我很好奇,看看有没有人有更好的方法,UDF,或在我的公式中看到一个错误...
= ROUNDDOWN(RANK.AVG(A1,$ A $ 1:$ A $ 131,0)/((COUNT(A $ 1:A $ 131)+1 )/ 5),0)+1
A1到A131具有我放在五分位数
谢谢
....但我认为有几种方法可以做到这一点,例如使用 PERCENTILE
函数获取断点,然后与 MATCH
匹配,即
= MATCH(A1,PERCENTILE(A $ 1:A $ 131,{5,4,3,2,1} / 5), - 1)
在大多数情况下,与您的公式相同,但在边界之间可能会有一些差异
I'm trying to calculate the quintile for every row of a column in Excel.
The resulting value for each row in Excel should be 1, 2, 3, 4, or 5.
A value of one will be the top 20%, a value of 5 is the bottom 20%.
This is my current formula which SEEMS to work, but I'm curious to see if anyone has a better way, a UDF, or sees an error in my formula...
=ROUNDDOWN(RANK.AVG(A1,$A$1:$A$131,0)/((COUNT(A$1:A$131)+1)/5),0)+1
A1 through A131 has the values I'm placing in quintiles.
Thanks
Your suggested formula works for me......but I think there are several ways you could do this, e.g. use PERCENTILE
function to get the breakpoints and then match against those with MATCH
, i.e.
=MATCH(A1,PERCENTILE(A$1:A$131,{5,4,3,2,1}/5),-1)
In most cases that gives the same results as your formula but there might be some discrepancies around the boundaries
这篇关于如何计算Excel中的每一行的五分位数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!