按百分比系列计算1-5级的等级 [英] Calculate rank on a scale of 1-5 on a percentage series

问题描述

我是一名新秀，而且必须以1-5的成绩来计算表现，而我已经以下列方式获得了50位候选人的最终成绩：

I am a newbie in ms excel and have to calculate performance on a scale of 1-5 whereas I have been given a final score of 50 candidates in % like below.

124.48%
124.35%
122.20%
120.17%
118.61%
116.25%
115.01%
114.66%
111.32%
111.16%
110.39%
109.77%
108.99%
108.79%
108.07%
107.21%
104.83%
103.97%
103.41%
103.00%
102.83%
102.75%
101.50%
101.29%
101.18%
101.09%
101.05%
99.97%

我如何评价他们之间的评级为1-5。我相信我必须为平均表现设定一个基准。这个时刻我已经变得无知了。请帮助

How am i suppose to rate them between a rating of 1-5. I believe that I have to set a bench mark for average performance. I have become clueless this moment of time. Please help

推荐答案

您可以在数组公式中使用 PERCENTILE 一次得到结果，例如在A2中的数据：A29在B2中尝试此数组公式

You can use PERCENTILE function in an array formula and get the result in one go, e.g. with data in A2:A29 try this array formula in B2

= MATCH（A2，PERCENTILE（A $ 2：A $ 29，{5,4 ，3,2,1} / 5）， - 1）

确认与 CTRL + SHIFT + ENTER 并复制

confirm with CTRL+SHIFT+ENTER and copy down

这篇关于按百分比系列计算1-5级的等级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！