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 © 2024 vBulletin Solutions Inc. All rights reserved.