• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating the mean of values above or between standard deviations

David Sharp

New Member
Hi
I have a set of dat that is typically distributed. I can work out the means, medians and standard deviations. I want to be able to calculate the average value above certain points: namely - what is the average value of data above 1 standard deviation or what is the average value of data above 1.6 standard deviations

Any assistance greatly appreciated
 
Hi
I have a set of dat that is typically distributed. I can work out the means, medians and standard deviations. I want to be able to calculate the average value above certain points: namely - what is the average value of data above 1 standard deviation or what is the average value of data above 1.6 standard deviations

Any assistance greatly appreciated
Hi,

This array formula, see below for how to enter it, calculates the average of the cells in the range greater than 1 stdev

=AVERAGE(IF(A1:A10>STDEV(A1:A10),A1:A10))

so for other variations like 1.6 standard deviations you simply add a multiplication number in the formula like this

=AVERAGE(IF(A1:A10>STDEV(A1:A10)*1.6,A1:A10))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.
 
Back
Top