Function or UDF

[leal72]

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]

=STDEV(IF($A$1:$A$15="No",>2 and <9))

untested

[leal72]

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]

Hi leal

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

[VBA]
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
[/VBA]

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

[Bob Phillips]

Try this array formula

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

[leal72]

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]

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

entered as an array function

[Bob Phillips]

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

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