Calculating basic averages...a question
 

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...


--
stew1901
------------------------------------------------------------------------
stew1901's Profile: http://www.excelforum.com/member.php...o&userid=29164
View this thread: http://www.excelforum.com/showthread...hreadid=488880

Calculating basic averages...a question
 

Hi!

To average values that are 25 but <50:

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

=AVERAGE(IF(A1:A1025,IF(A1:A10<50,A1:A10)))

Biff

"stew1901" wrote in
message ...

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...


--
stew1901
------------------------------------------------------------------------
stew1901's Profile:
http://www.excelforum.com/member.php...o&userid=29164
View this thread: http://www.excelforum.com/showthread...hreadid=488880

Calculating basic averages...a question
 

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

Calculating basic averages...a question
 

Thanks a lot!

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


--
stew1901
------------------------------------------------------------------------
stew1901's Profile: http://www.excelforum.com/member.php...o&userid=29164
View this thread: http://www.excelforum.com/showthread...hreadid=488880

Calculating basic averages...a question
 

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

"stew1901" wrote in
message ...

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...

--
stew1901

Calculating basic averages...a question
 

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.


--
stew1901
------------------------------------------------------------------------
stew1901's Profile: http://www.excelforum.com/member.php...o&userid=29164
View this thread: http://www.excelforum.com/showthread...hreadid=488880