excelexcel-formula

Calculate standard deviation using if condition but ignoring blank cells

I need to calculated the standard deviation in a similar way we calculate the average using the function "averageif".

I used the function described below to do it (as proposed by the users Scott Craner and @nbayly in https://stackoverflow.com/a/41174620/31207248)

=STDEV.S(IF((C:C="alpha")*(D:D="S14"),B:B))

But this function considers blank cells as zero. Is there a way to make it ignore blank cells?

I tried to use if(isblank;"") and isnumber but it did not work. It says i have entered too many arguments

Solution

  • Try using one of the following formulas:

    =STDEV.S(FILTER(B:.B, (C:.C="alpha")*(D:.D="S14")))

    Or,

    =STDEV.S(IF((C:.C="alpha")*(D:.D="S14"),B:.B))

    Or,

    =STDEV.S(TOCOL(B:.B/((C:.C="alpha")*(D:.D="S14")), 2))

    All the above formulas uses TRIMRANGE() refs operators to exclude trailing empty rows, if you don't have access to it, then use the followings:

    =STDEV.S(IF((C:C="alpha")*(D:D="S14")*(B:B<>""),B:B))

    Or,

    =STDEV.S(FILTER(B:B, (C:C="alpha")*(D:D="S14")*(B:B<>"")))

    Or,

    =STDEV.S(TOCOL(B:B/((C:C="alpha")*(D:D="S14")*(B:B<>"")), 2))