[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1153
  • Last Modified:

Average excluding spikes

I have a file attached with two typical ranges with uniform data but having occasional spikes. I would like to have the average of the data excluding the spikes preferably without VBA.

Saqib
Avg-excludng-spikes.xls
0
Saqib Husain, Syed
Asked:
Saqib Husain, Syed
1 Solution
 
MakriniCommented:
assuming that above 60 is considered a spike...

=SUMIF(C:C,"<60")/COUNTIF(C:C,"<60")
0
 
zorvek (Kevin Jones)ConsultantCommented:
Try this array formula:

=AVERAGE(IF($A$2:$A$95>AVERAGE($A$2:$A$95)+(MAX($A$2:$A$95)-AVERAGE($A$2:$A$95))/2,"",$A$2:$A$95))

To enter press CTRL+SHIFT+ENTER.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
My formula dynamically determines the cutoff as any value greater than the point half way between the max and the average. That way you don't have to worry about a hardcoded cutoff in the event the spread changes.

Kevin
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
MakriniCommented:
Well if you want to go dynamic - here is an alternative avoiding using an array formula - and using 4 times the standard deviation as the cut off point.

=SUMIF(C:C,"<" & AVERAGE(C:C)+(STDEV(C:C)*4))/COUNTIF(C:C,"<" & AVERAGE(C:C)+(STDEV(C:C)*4))
0
 
patrickabCommented:
3 x StdDev is close to 100%, so I believe that 4 x StdDev would certainly include all the values. The StdDev multiplier needs to be a variable in a cell so that it can be changed to exclude the peaks as determined at that time.

Patrick
0
 
zorvek (Kevin Jones)ConsultantCommented:
Half way between the peak and the average seems pretty simple and reasonable to me ;-)
0
 
MakriniCommented:
You are correct 3 x Stdev is probably enough - and a cell reference for that is a good idea (just change the 4 for a cell reference)
0
 
MakriniCommented:
agreed Kevin - its a nice simple way for most instances.  I just spend way too much time normalising data
0
 
barry houdiniCommented:
Excel has a specific function for averaging excluding outliers, TRIMMEAN, although you need to decide which percentage to exclude
For example this formula will exclude the bottom 5% and top 5%
=TRIMMEAN(A2:A95,10%)
regards, barry
 
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now