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

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

Bob Phillips

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

Bob Phillips

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.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.