Calculating average and standard deviation in non-contiguous columns excluding certain values

Occasional Visitor


I am trying to calculate the average and standard deviation of values in two non-contiguous columns excluding any values that are outside 80-120.


In the sheet attached I would like to calculate the average and standard deviation for columns F and I. 



1 Reply


Here is an example file where you can adapt the formulas in your file.
Be careful with the standard deviation, the formula can be written differently depending on the Excel version.


Standard deviation, mean, and variance are important parameters that provide useful information about the distribution of data. Their calculation is not as complicated as it appears at first glance. Excel is exactly the right program to determine these values quickly and easily.


Average: The mean value provides an average value. For example, you can determine the average number of hours your employees need for certain tasks.

Standard deviation: The standard deviation is a key figure that shows how far the respective values spread around the mean (average). Thus, the standard deviation calculates the spread.

Variance: The variance, on the other hand, calculates the degree of variation. The aim here is to determine how much the results of a survey scatter around the mean.


I would be happy to know if I could help.



I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.