Excel AVERAGE questions.
August 5, 2009 12:38 PM Subscribe
Trying to get some modified AVERAGE results in Excel.
Right now I've got a formula in my workbook 'AVERAGE(Data!Z:Z)', pooling turnaround time data from a single column on a different sheet. Most of the values are in the five minute range (3.5, 4.2, 7.1, 5.0, etc), however there are also very occasionally (say 5-10 cells in an array of 3000 or so) values that are closer to 1-2 days (1500 minutes, 2000 minutes, etc).
Is there a way to modify or append the AVERAGE function to either a) weed out values outside of a certain range, or b) get a median average that drops something like the 10 lowest and highest values in the range, or the bottom and top 1%, or something similar?
Right now I've got a formula in my workbook 'AVERAGE(Data!Z:Z)', pooling turnaround time data from a single column on a different sheet. Most of the values are in the five minute range (3.5, 4.2, 7.1, 5.0, etc), however there are also very occasionally (say 5-10 cells in an array of 3000 or so) values that are closer to 1-2 days (1500 minutes, 2000 minutes, etc).
Is there a way to modify or append the AVERAGE function to either a) weed out values outside of a certain range, or b) get a median average that drops something like the 10 lowest and highest values in the range, or the bottom and top 1%, or something similar?
I'm sure someone will give the exact answer but in the meantime, take a look at the averageif function in help. It basically gives the averages if cells fit your condition.
posted by caelumluna at 12:47 PM on August 5, 2009
posted by caelumluna at 12:47 PM on August 5, 2009
Response by poster: Bwa-ha! That's exactly what I'm looking for, I think. Thanks!
posted by FatherDagon at 12:48 PM on August 5, 2009
posted by FatherDagon at 12:48 PM on August 5, 2009
Another option, just for reference, is the trimmed mean:
=trimmean(Z:Z, percent)
Where "percent" is some number (.1, for example) of values on either end of the spectrum that you want to include. So if you were to use that function on a dataset with 100 rows, and .1 (10%) as your percent, it would exclude the 5 highest and 5 lowest (5% on each side).
posted by toomuchpete at 1:26 PM on August 5, 2009 [1 favorite]
=trimmean(Z:Z, percent)
Where "percent" is some number (.1, for example) of values on either end of the spectrum that you want to include. So if you were to use that function on a dataset with 100 rows, and .1 (10%) as your percent, it would exclude the 5 highest and 5 lowest (5% on each side).
posted by toomuchpete at 1:26 PM on August 5, 2009 [1 favorite]
Ya I would use the standard IF function (but you will get pretty nested and convoluted) or AVERAGEIFS as Nanukthedog pointed out
posted by schindyguy at 6:05 PM on August 5, 2009
posted by schindyguy at 6:05 PM on August 5, 2009
« Older I need health insurance (me and everyone else...) | Do bacteria survive freezing and thawing? Newer »
This thread is closed to new comments.
=AVERAGEIFS(DATA!Z:Z,DATA!Z:Z,"<100")
posted by Nanukthedog at 12:46 PM on August 5, 2009