|
Help with Averaging Numbers within Standard Deviation
I'm trying to average only the numbers that fall within the standard
deviation. Say you have 10 numbers listed and only 7 fall in the range of the standard deviation of the list. I need the average of only those number that fit. I've tried several different ways but nothing is working. Any help would be greatly appreciated!! Thanks...mitch |
Help with Averaging Numbers within Standard Deviation
lawdoggy;526336 Wrote: I'm trying to average only the numbers that fall within the standard deviation. Say you have 10 numbers listed and only 7 fall in the range of the standard deviation of the list. I need the average of only those number that fit. I've tried several different ways but nothing is working. Any help would be greatly appreciated!! Thanks...mitch This is for 1 SD either side of the mean, where K7 contained the mean, K8 the SD, and the sample data was in range F8:F31 : Code: -------------------- =(SUM(--(IF(F8:F31(K7-K8),1,0)*IF(F8:F31<(K7+K8),1,0)*F8:F31)))/(COUNTIF(F8:F31,"=" & K7-K8)-COUNTIF(F8:F31,"" & K7+K8)) -------------------- alternatively, this can all be put in one cell: Code: -------------------- =(SUM(--(IF(F8:F31(AVERAGE(F8:F31)-STDEV(F8:F31)),1,0)*IF(F8:F31<(AVERAGE(F8:F31)+STD EV(F8:F31)),1,0)*F8:F31)))/(COUNTIF(F8:F31,"=" & AVERAGE(F8:F31)-STDEV(F8:F31))-COUNTIF(F8:F31,"" & AVERAGE(F8:F31)+STDEV(F8:F31))) -------------------- for the same sample range. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144559 |
Help with Averaging Numbers within Standard Deviation
On Oct 15, 2:31*pm, p45cal wrote:
lawdoggy;526336 Wrote: I'm trying to average only the numbers that fall within the standard deviation. Say you have 10 numbers listed and only 7 fall in the range of the standard deviation of the list. I need the average of only those number that fit. I've tried several different ways but nothing is working. Any help would be greatly appreciated!! Thanks...mitch This is for 1 SD either side of the mean, where K7 contained the mean, K8 the SD, and the sample data was in range F8:F31 : Code: -------------------- * * =(SUM(--(IF(F8:F31(K7-K8),1,0)*IF(F8:F31<(K7+K8),1,0)*F8:F31)))/(COUNTIF(F*8:F31,"=" & K7-K8)-COUNTIF(F8:F31,"" & K7+K8)) -------------------- alternatively, this can all be put in one cell: Code: -------------------- * * =(SUM(--(IF(F8:F31(AVERAGE(F8:F31)-STDEV(F8:F31)),1,0)*IF(F8:F31<(AVERAGE(*F8:F31)+ST DEV(F8:F31)),1,0)*F8:F31)))/(COUNTIF(F8:F31,"=" & AVERAGE(F8:F31)-STDEV(F8:F31))-COUNTIF(F8:F31,"" & AVERAGE(F8:F31)+STDEV(F8:F31))) -------------------- *for the same sample range. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=144559 Wow!! no wonder i never figured it out. Thank you. I will mess with it and see if I can get it to work. Thank you! mitch |
Help with Averaging Numbers within Standard Deviation
lawdoggy;526880 Wrote: On Oct 15, 2:31*pm, p45cal wrote: lawdoggy;526336 Wrote: I'm trying to average only the numbers that fall within the standard deviation. Say you have 10 numbers listed and only 7 fall in the range of the standard deviation of the list. I need the average of only those number that fit. I've tried several different ways but nothing is working. Any help would be greatly appreciated!! Thanks...mitch This is for 1 SD either side of the mean, where K7 contained the mean, K8 the SD, and the sample data was in range F8:F31 : Code: -------------------- * * =(SUM(--(IF(F8:F31(K7-K8),1,0)*IF(F8:F31<(K7+K8),1,0)*F8:F31)))/(COUNTIF(F*8:F31,"=" & K7-K8)-COUNTIF(F8:F31,"" & K7+K8)) -------------------- alternatively, this can all be put in one cell: Code: -------------------- * * =(SUM(--(IF(F8:F31(AVERAGE(F8:F31)-STDEV(F8:F31)),1,0)*IF(F8:F31<(AVERAGE(*F8:F31)+ST DEV(F8:F31)),1,0)*F8:F31)))/(COUNTIF(F8:F31,"="& AVERAGE(F8:F31)-STDEV(F8:F31))-COUNTIF(F8:F31,"" & AVERAGE(F8:F31)+STDEV(F8:F31))) -------------------- *for the same sample range. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:'The Code Cage Forums - View Profile: p45cal' (http://www.thecodecage.com/forumz/member.php?userid=558) View this thread:'Help with Averaging Numbers within Standard Deviation - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=144559) Wow!! no wonder i never figured it out. Thank you. I will mess with it and see if I can get it to work. Thank you! mitch I notice there's no user defined function in the original message above which I added to the post later. Go to thecodecage.com thread to see it - hang on a mo, Ill paste here too: --- Automerged consecutive post before response --- or a user defined function which you can use in vba or call from the worksheet thus: =SDAverage(F8:F31,1) where the second parameter is the number of standard deviations either side of the mean to include in the calculation. Happily, it gives the same result as the maga-formula given earlier: Function SDAverage(TheRange, NoOfSDs) TheMean = Application.Average(TheRange.Value) SD = Application.WorksheetFunction.StDev(TheRange.Value ) For Each cll In TheRange If cll.Value TheMean - (SD * NoOfSDs) And cll.Value < TheMean + (SD * NoOfSDs) Then mySum = mySum + cll.Value divisor = divisor + 1 End If Next cll If divisor 0 Then SDAverage = mySum / divisor Else SDAverage = "Divide by 0" End Function -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144559 |
Help with Averaging Numbers within Standard Deviation
Hi Mitch,
Another option - =(SUM(--(IF(H2:H11=(AVERAGE(H2:H11)-STDEV(H2:H11)),1,0)*IF(H2:H11<=(AVERAGE(H2:H11)+ST DEV(H2:H11)),1,0)*H2:H11)))/(COUNTIF(H2:H11,"="&AVERAGE(H2:H11)-STDEV(H2:H11))-COUNTIF(H2:H11,"="&AVERAGE(H2:H11)+STDEV(H2:H11)) ) data is in H2:H11 enter the formula as an array formula - CTRL+SHIFT+ENTER Peggy "lawdoggy" wrote in message ... On Oct 15, 2:31 pm, p45cal wrote: lawdoggy;526336 Wrote: I'm trying to average only the numbers that fall within the standard deviation. Say you have 10 numbers listed and only 7 fall in the range of the standard deviation of the list. I need the average of only those number that fit. I've tried several different ways but nothing is working. Any help would be greatly appreciated!! Thanks...mitch This is for 1 SD either side of the mean, where K7 contained the mean, K8 the SD, and the sample data was in range F8:F31 : Code: -------------------- =(SUM(--(IF(F8:F31(K7-K8),1,0)*IF(F8:F31<(K7+K8),1,0)*F8:F31)))/(COUNTIF(F*8:F31,"=" & K7-K8)-COUNTIF(F8:F31,"" & K7+K8)) -------------------- alternatively, this can all be put in one cell: Code: -------------------- =(SUM(--(IF(F8:F31(AVERAGE(F8:F31)-STDEV(F8:F31)),1,0)*IF(F8:F31<(AVERAGE(*F8:F31)+ST DEV(F8:F31)),1,0)*F8:F31)))/(COUNTIF(F8:F31,"=" & AVERAGE(F8:F31)-STDEV(F8:F31))-COUNTIF(F8:F31,"" & AVERAGE(F8:F31)+STDEV(F8:F31))) -------------------- for the same sample range. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=144559 Wow!! no wonder i never figured it out. Thank you. I will mess with it and see if I can get it to work. Thank you! mitch |
Help with Averaging Numbers within Standard Deviation
On Oct 15, 6:50*pm, "Peggy Shepard" wrote:
Hi Mitch, Another option - =(SUM(--(IF(H2:H11=(AVERAGE(H2:H11)-STDEV(H2:H11)),1,0)*IF(H2:H11<=(AVERAG*E(H2:H11)+S TDEV(H2:H11)),1,0)*H2:H11)))/(COUNTIF(H2:H11,"="&AVERAGE(H2:H11*)-STDEV(H2:H11))-COUNTIF(H2:H11,"="&AVERAGE(H2:H11)+STDEV(H2:H11)) ) data is in H2:H11 enter the formula as an array formula - CTRL+SHIFT+ENTER Peggy "lawdoggy" wrote in message ... On Oct 15, 2:31 pm, p45cal wrote: lawdoggy;526336 Wrote: I'm trying to average only the numbers that fall within the standard deviation. Say you have 10 numbers listed and only 7 fall in the range of the standard deviation of the list. I need the average of only those number that fit. I've tried several different ways but nothing is working. Any help would be greatly appreciated!! Thanks...mitch This is for 1 SD either side of the mean, where K7 contained the mean, K8 the SD, and the sample data was in range F8:F31 : Code: -------------------- =(SUM(--(IF(F8:F31(K7-K8),1,0)*IF(F8:F31<(K7+K8),1,0)*F8:F31)))/(COUNTIF(F**8:F31,"=" & K7-K8)-COUNTIF(F8:F31,"" & K7+K8)) -------------------- alternatively, this can all be put in one cell: Code: -------------------- =(SUM(--(IF(F8:F31(AVERAGE(F8:F31)-STDEV(F8:F31)),1,0)*IF(F8:F31<(AVERAGE(**F8:F31)+S TDEV(F8:F31)),1,0)*F8:F31)))/(COUNTIF(F8:F31,"=" & AVERAGE(F8:F31)-STDEV(F8:F31))-COUNTIF(F8:F31,"" & AVERAGE(F8:F31)+STDEV(F8:F31))) -------------------- for the same sample range. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=144559 Wow!! no wonder i never figured it out. Thank you. I will mess with it and see if I can get it to work. Thank you! mitch- Hide quoted text - - Show quoted text - The code works great Thank you!!! You guys rock! mitch |
Help with Averaging Numbers within Standard Deviation
Hello,
Array-enter: =AVERAGE(IF(ABS(AVERAGE(A1:A10)-A1:A10)<=ABS(ABS(AVERAGE(A1:A10))-STDEV (A1:A10)),A1:A10)) Regards, Bernd |
Help with Averaging Numbers within Standard Deviation
Wouldn't this be sufficient? =AVERAGE(IF(ABS(A1:A10-AVERAGE(A1:A10))<=STDEV(A1:A10),A1:A10)) confirmed with CTRL+SHIFT+ENTER -- barry houdini ------------------------------------------------------------------------ barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144559 |
Help with Averaging Numbers within Standard Deviation
Sure.
Regards, Bernd |
Help with Averaging Numbers within Standard Deviation
You can do this w/o VBA and with a (relatively) simpler formula.
Suppose your data are in A1:A13, the average is in D1 and the std. dev. in E1. Then, use the array formula =AVERAGE(IF(ABS(A1:A13-$D$1)<=$E$1,A1:A13)) To enter an array formula complete data entry not with the combination of SHIFT+CTRL+ENTER keys and not just the ENTER or TAB key. If done correctly, *Excel* will show the formula enclosed in curly brackets { and } On Thu, 15 Oct 2009 10:09:08 -0700 (PDT), lawdoggy wrote: I'm trying to average only the numbers that fall within the standard deviation. Say you have 10 numbers listed and only 7 fall in the range of the standard deviation of the list. I need the average of only those number that fit. I've tried several different ways but nothing is working. Any help would be greatly appreciated!! Thanks...mitch Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins |
| All times are GMT +1. The time now is 09:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com