0

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.

1
  • I think you're going to need VBA if you want to return a list of values based on an if condition. Otherwise you'll need to utilize a column to generate the numbers. Feb 25, 2015 at 13:42

1 Answer 1

0

Open macro editor, on the left tree under Modules insert a new module and in the right side paste this:

Function cond_Variance(Condition As String, Data As Range, Categories As Range) As Double
    Dim numbers() As Double
    DataRows = Data.Rows.Count
    CategoriesRows = Categories.Rows.Count
    firstDataRow = Data.Row
    firstDataColumn = Data.Column
    firstCategoriesRow = Categories.Row
    firstCategoriesColumn = Categories.Column
    ReDim numbers(DataRows)
    counter = 1
    Total = 0
    For i = firstCategoriesRow To firstCategoriesRow + CategoriesRows - 1
        selectedcell = Cells(i, firstCategoriesColumn)
        If selectedcell = Condition Then
            numbers(counter) = Cells(i, firstDataColumn)
            counter = counter + 1
            Total = Total + Cells(i, firstDataColumn)
        End If
    Next i
    counter = counter - 1
    mean_value = Total / counter
    vTotal = 0
    For i = 1 To counter
        nDiff = (numbers(i) - mean_value) * (numbers(i) - mean_value)
        vTotal = vTotal + nDiff
    Next i
    cond_Variance = vTotal / counter
End Function

Now, for your example, on cell C2 you write:

=cond_Variance(B2,$A$2:$A$7,$B$2:$B$7)

About the parameters:

  • The first one is the Condition, in this case B2 is Orange.
  • The next field is the range of numeric data, in this case $A$2:$A$7
  • The last one is the list of Categories, in this case $B$2:$B$7

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .