PDA

View Full Version : Function or UDF

leal72
04-26-2010, 11:48 AM
Trying to find if there is a standard deviation function similar to the average function [AVERAGEIFS]

the AVERAGEIFS will return the average of cells in a defined range if conditions are met. And it works just as I need it to. What I need now is to find a standard deviation function that will work with conditions.

So in range A1:A15, I only want to get the standard deviation if the value is range A1:A15 is >2 and <9

austenr
04-26-2010, 12:19 PM
=STDEV(IF(\$A\$1:\$A\$15="No",>2 and <9))

untested

leal72
04-26-2010, 12:52 PM
thanks but that one is not working for me. The way it is it does not work, but I did try it in some other variations and still come up with errors.

Thanks for trying though

Eds
04-26-2010, 01:28 PM
Hi leal

In case you settle for a UDF, here is an example:

Option Explicit

Function ConditionalStDev(target As Range, _
LowerBound As Double, _
UpperBound As Double) As Double

Dim Rng As Range
Dim SSD As Double
Dim lCount As Long

lCount = 0
SSD = 0
For Each Rng In target
If Rng.Value >= LowerBound And Rng.Value <= UpperBound Then
SSD = SSD + (WorksheetFunction.Average(target) - Rng.Value) ^ 2
lCount = lCount + 1
End If
Next Rng

ConditionalStDev = SSD / lCount
End Function

You may want to improve it with your own specifications.

By the way, if you want the Standard Deviation, you must take the square root of the returning function value. The function is now returning the variance. Also, if you are sampling, you may also want to divide the final result per (lCount + 1) instead.

Please review the results before apply.

Bye
Eduardo

xld
04-26-2010, 02:26 PM
Try this array formula

=STDEV(IF((\$A\$1:\$A\$15>2)*(\$A\$1:\$A\$15<9),\$A\$1:\$A\$15))

leal72
04-26-2010, 02:40 PM
Try this array formula

=STDEV(IF((\$A\$1:\$A\$15>2)*(\$A\$1:\$A\$15<9),\$A\$1:\$A\$15))

Worked perferctly. Thank you!

the "*" throws me off but it does work. Thanks again

Eds - Obrigado, the array formula is much easier to implement at this point but I will try the function you posted at a later time and to help me learn.. Thank you

bjacobowski
04-26-2010, 03:25 PM
=STDEV(IF(\$A\$1:\$A\$15>2,IF(\$A\$1:\$A\$15<9,\$A\$1:\$A\$15)))

entered as an array function

xld
04-26-2010, 03:47 PM
the "*" throws me off but it does work.

The * operator is effectively AND, so both conditions have to be met.