LinkBack URL
About LinkBacks
Is there anyway to do a standard dev w/o the max + min in a range of numbers?
Is there anyway to do a standard dev w/o the max + min in a range of numbers?
Last edited by snapa; 06-22-2009 at 09:59 AM.
If we assume values are potentially in A1:A10
=STDEV(LARGE($A$1:$A$10,ROW(INDIRECT("2:"&COUNT($A$1:$A$10)-1))))
committed with CTRL + SHIFT + ENTER
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Try:
=STDEV(IF(A1:A10<>MAX(A1:A10),IF(A1:A10<>MIN(A1:A10),A1:A10)))
where A1:A10 contain your number list
Confirmed with CTRL+SHIFT+ENTER not just ENTER... you will see { } brackets appear.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Worked! Much thanks!
If you don't mind, could you explain the formula to me?
NBVC, your method also worked. Thank you!
Odd, using the two formulas, I'm getting two different sets of numbers.
NBVCs omits multiple values if there are several that are the max or min.
Entia non sunt multiplicanda sine necessitate
If your values in A1:A10 are unique or you want to exclude all instances of the MAX / MIN number (ie if Max / Min value appears twice ignore all instances of said value) then I'd recommend NBVC's approach... my variant of the approach is to use the Largest x values... from 2nd largest to all but the smallest value.
Ah thanks for the explanation DonkeyOte, your formula is exactly the one I need then.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks