standard deviation of a range to only include values >0 and <100

[bonisko]

Hi!

I have Rows and Rows of data that include data of interest.
These rows also have blank cells, cells with value 0, cells with value 100, and some that have #DIV/0!
I want to calculate the standard deviation of the range (1 row), but only of the data of interest.
I have found ways to calculate the average and the the number of data points:
=AVERAGEIFS(J7:J228, J7:J228, "<99.99", J7:J228, ">0")
=COUNTIFS(J7:J228,"<99.99",J7:J228,">0")
Both work as expected.
Thanks in advance!
Bruce

​

 

[maabadi]

Use this and Press Ctrl+Shift+Enter:

=STDEV.S(IF(J7:J228="","",IFS(J7:J228<100,J7:J228,J7:J228>0,J7:J228)))

 

[maabadi]

This is corrected Version (Press Ctrl+Shift+Enter):

=MIN(STDEV.S(IF(J7:J228="","",IF(J7:J228<100,J7:J228))),STDEV.S(IF(J7:J228="","",IF(J7:J228>0,J7:J228))))

 

[bonisko]

Thanks. However, I enter and see the {} around the txt as expected for an array formula, but I get output of #DIV/0!

Help again please?

Thanks,

Bruce

 

[AhoyNC]

You need to get rid of the #DIV/0! error in your data. Use something like the IFERROR function to remove the errors as in the example below (cell J11 has an error).
Then try the formula below, about the same as maabadi had.
It is an array formula and must be entered with CTRL-SHIFT-ENTER.

Book2
	I	J
6	Std. Dev.	25.87792
7		-45
8		65
9
10		82
11
12		22
13		0
14
15		45
16
Sheet2

Cell Formulas
Range	Formula
J6	J6	=STDEV.S(IF(J7:J228>0,IF(J7:J228<99.99,J7:J228)))
J11	J11	=IFERROR(L11/M11,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bonisko]

Works perfectly. Thanks!

 

[AhoyNC]

You're welcome. Thanks for the feedback.