如何计算Excel中的每一行的五分位数？ [英] How do you calculate the Quintile for every row in Excel?

问题描述

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中的每一行的五分位数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！