Search code examples
excelexcel-formulastandard-deviation

Excel: Standard deviation, ignoring #NA and blanks in the range

I want to calculate the standard deviation with STDDEV.P for a range, but I want to ignore #NA and blank cells.

#NA and blanks should NOT be included in the calculation as 0.

I've reached this solution for ignoring #NA:

=STDEV.P(IF(NOT(ISERROR(Trade!AI5:AI154));Trade!AI5:AI154))

How do I ignore blank cells as well?

Solution

  • I reached a conclusion myself, using ISNUMBER():

    =STDEV.P(IF(ISNUMBER(Trade!AI5:AI154);Trade!AI5:AI154))