Calculating basic averages...a question

S

stew1901

Hi all

First post! I hope this is the right section!

I need to calculate a simple average (arithmetic and median) of a set
of numbers, but excluding certain outlying points. I'm not by any
means a newbie to coding so I do know how I'd do this generally in a
programming language or "pseudo code" (so no need to waste your time on
long explanations!) but I am completely new to Excel so have no idea of
the syntax and don't really have the time to mess around with the
"guess the useful phrase" help function.

Thanks in advance...I hope...
 
B

Biff

Hi!

To average values that are >25 but <50:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(A1:A10>25,IF(A1:A10<50,A1:A10)))

Biff
 
J

John Michl

You could do this with array formulas. Instead of hitting Enter to
finish the formula, use Ctrl-Shift-Enter to add the curly braces to the
array formula. If the range of values is in cells A1 through A10 and
you want to disregard all values equal to or greater than 25, use:

{=AVERAGE(IF(A1:A10<25,A1:A10,""))}
{=MEDIAN(IF(A1:A10<25,A1:A10,""))}

For more info on array formulas, see
http://www.cpearson.com/excel/array.htm

Hope that helps.
- John
www.JohnMichl.com
 
S

stew1901

Thanks a lot!

That is SO FAR off what I was expecting you have probably saved me a
hour of frustration
 
M

Mike Middleton

stew1901 -

The median is relatively insensitive to the values of the extremes, so maybe
you don't have to exclude them.

For the arithmetic mean, you might try the TRIMMEAN worksheet function.

- Mike
www.mikemiddleton.com
 
S

stew1901

Good point on the median Mike. I was going to exclude it as I didn't
see an immediate way of using similar techniques (and it's not that
vital...this isn't anything particularly scientific)

TRIMMEAN I don't think would work...what I basically have is a list of
exam scores, but with a few absences (recorded as zero) and a very few
low scores from people who have since quit.

No doubt there are more sensible ways I could have organised the data!
But at least I've got a very small bit of experience with worksheet
functions now. The CTRL SHIFT ENTER thing in particular I had no idea
about and don't see how I'd have stumbled across by accident.
 
Top