exclude value from formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column of data from A3:A20. I have an average function in cell A22 averaging the numbers in the column. If one of the numbers in the column is extremely higher than the others, it skews the average. Is there a way I can exclude certain numbers from the average calculation if they are not a good sample of data?
 
Take a look at the TRIMMEAN function which may help you

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



bedwards97 said:
I have a column of data from A3:A20. I have an average function in cell A22
Click to expand...
averaging the numbers in the column. If one of the numbers in the column is
extremely higher than the others, it skews the average. Is there a way I can
exclude certain numbers from the average calculation if they are not a good
sample of data?
 
To exclude the max. number:

=SUM(A3:A20,-LARGE(A3:A20,1))/(COUNT(A3:A20)-1)

To exclude the 2 largest numbers:

=SUM(A3:A20,-LARGE(A3:A20,{1,2}))/(COUNT(A3:A20)-2)

HTH
Jason
Atlanta, GA
-----Original Message-----
I have a column of data from A3:A20. I have an average
Click to expand...
function in cell A22 averaging the numbers in the column.
If one of the numbers in the column is extremely higher
than the others, it skews the average. Is there a way I
can exclude certain numbers from the average calculation
if they are not a good sample of data?
 
Back
Top