For the following Excel table:
A B C
1 Score Category VariancePerCategory(Calculated)
2 0.3146 Orange 0.034214
3 0.5711 Orange 0.034214
4 0.7129 Apple 0.0
5 0.8426 Lemon 0.000215
6 0.1194 Orange 0.034214
7 0.8719 Lemon 0.000215
I need column C to automatically have the variance of all scores (column A), but only for that row's category (column B). Meaning that rows 2,3,6 will all have the same value (they all share the Orange
category), which is VAR.P(A2, A3, A6)
.
I would like all the cells in column C to have the same formula, which will have VAR.P
always run only on the cells in Column A that have a column B that is equal to the current row's column B. How can I write this formula?
Ideally I would have some equivalent of SUMIF()
and AVERAGEIF()
for variance, but since it doesn't exist, I am looking for a more generic solution (which could work with any function that receives a set of cells). I tried to do it with IF()
, LOOKUP()
, etc., but nothing fit.
if
condition. Otherwise you'll need to utilize a column to generate the numbers.