[Webinar] Streamline your web hosting managementRegister Today

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

# 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
1 Solution

Commented:
assuming that above 60 is considered a spike...

=SUMIF(C:C,"<60")/COUNTIF(C:C,"<60")
0

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

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

Commented:
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

Commented:
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

ConsultantCommented:
Half way between the peak and the average seems pretty simple and reasonable to me ;-)
0

Commented:
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

Commented:
agreed Kevin - its a nice simple way for most instances.  I just spend way too much time normalising data
0

Commented:
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

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