Solved

# Average excluding spikes

Posted on 2010-01-02
680 Views
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
Question by:Saqib Husain, Syed

LVL 10

Expert Comment

assuming that above 60 is considered a spike...

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

LVL 81

Expert Comment

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

LVL 81

Expert Comment

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

LVL 10

Expert Comment

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

LVL 45

Expert Comment

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

LVL 81

Expert Comment

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

LVL 10

Expert Comment

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

LVL 10

Expert Comment

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

LVL 50

Accepted Solution

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

## Join & Write a Comment Already a member? Login.

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

#### 745 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!