我需要返回电子表格中某个类别的中位数.下面的示例
I need to return a median of only a certain category on a spread sheet. Example Below
Airline 5 Auto 20 Auto 3 Bike 12 Airline 12 Airline 39等
如何编写公式以仅返回航空公司类别"的中位数.如果仅适用于中位数,则类似于平均值".我无法重新排列值.谢谢!
How can I write a formula to only return a median value of the Airline Categories. Similar to Average if, only for median. I cannot re-arrange the values. Thank you!
推荐答案假设类别位于A1:A6单元格中,相应的值位于B1:B6中,则可以尝试在另一个单元格中键入公式=MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,"")),然后按CTRL+SHIFT+ENTER.
Assuming your categories are in cells A1:A6 and the corresponding values are in B1:B6, you might try typing the formula =MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,"")) in another cell and then pressing CTRL+SHIFT+ENTER.
使用CTRL+SHIFT+ENTER告诉Excel将公式视为数组公式".在此示例中,这意味着IF语句返回6个值的数组(范围为$A$1:$A$6的每个单元格之一),而不是单个值.然后,MEDIAN函数返回这些值的中值.有关类似内容,请参见 www.cpearson/excel/arrayformulas.aspx 使用AVERAGE而不是MEDIAN的示例.
Using CTRL+SHIFT+ENTER tells Excel to treat the formula as an "array formula". In this example, that means that the IF statement returns an array of 6 values (one of each of the cells in the range $A$1:$A$6) instead of a single value. The MEDIAN function then returns the median of these values. See www.cpearson/excel/arrayformulas.aspx for a similar example using AVERAGE instead of MEDIAN.
更多推荐
在Excel中进行中位数If所需的帮助
发布评论