standard deviation returning #NA Hi, I\'m trying to create a scatter chart with

Question

standard deviation returning #NA

Hi, I'm trying to create a scatter chart with standard deviation.

Dates A2:A400

Data   B2:BK400

Formulas for the data in B2:B400

I tried the following formulas

B2:=IF(Sheet1!$B2=0,NA(),Sheet1!$B2) returns in the BN2:BN4 formulas #NA

B2=IF(Sheet1!$B2=0,"",Sheet1!$B2) returns in the BN2:BN4 formulas #DIV/!

B2=IF(ISBLANK(Sheet1!$B2),"",0) returns in the BN2:BN4 formulas #DIV/!

BN2=AVERAGE(B2:B32)-STDEV(B2:B32)   

BN3=AVERAGE(B2:BK32)                            

BN4=AVERAGE(B2:BK32)+STDEV(B2:BK32)

Looking for formulas to ignore the empty cells when computing stdev

Thanks for you help!!!~!

Explanation / Answer

Or array-enter formulas with the following expressions (press ctrl+shift+Enter instead of just Enter):

AVERAGE(IF(ISNUMBER(B2:BK32),B2:BK32))

STDEV(IF(ISNUMBER(B2:BK32),B2:BK32))

Caveat: If it is possible that zero or only one numeric value exists in the range, it would be prudent to array-enter formulas with the following expressions instead, if you do not require Excel 2003 compatibility (i.e. do not save as ".xls"):

IFERROR(AVERAGE(IF(ISNUMBER(B2:BK32),B2:BK32)),"")

IFERROR(STDEV(IF(ISNUMBER(B2:BK32),B2:BK32)),"")

Replace the null string ("") with whatever you want in case of an error.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.