Calculating downside deviation ignoring blank cells

[burryumy]

I have created a formula to calculate the downside deviation of data set. The formula basically takes the standard deviation only of values within the data set that are below a certain threshold value. The formula works fine if there are no blank cells in the array containing the data. However, if there are blank cells the formula will assume these have a value of zero and the downside deviation calcultion will be different than if there were no blanks. I would like to adjust the formula so that it yields the same result whether or not there an any blank cells within the array.

The current formula I am using is :

=STDEV(IF(B2:B8<E3,B2:B8))

where B2:B8 is the array containing the data and E3 is the threshold value. I have attached spreadsheet with a sample data set and corresponding calculations to illustrate the issue.

Many thank in advance.

[burryumy]

I have still been unable to solve the problem described above and perhams this is not something that can be solved by using formulas. I would like to ask whether the problem could perhaps more easily be tackled using a macro solution?

[:) Sixthsense :)]

Array formula should be entered with Ctrl+Shift+Enter

Try this in E5 cell

Please Login or Register  to view this content.

Hope that helps!

[burryumy]

Many thanks Sixthsense. That works perftectly! A very elegant solution indeed.