Selective Calculation of Mean & Mode

[stevekanc]

I am trying to calculate average (Mean & Mode) values for a range of
numbers but ignoring numbers that are out of character for the
dataset.

Example:

Sales per time period: 2,2,4,5,3,76,2,45

How can I identify 76 and 45 as being out of anticpated range of
numbers?

Hope you can help,
thanks Steve

 

[Guest]

Well, with all your #s in row 1, in A1:H1, this calculates the average for
those #s less than 45. I don't know how you will determine that 45 is your
upper bound, but....

This is an array formula, meaning you commit it by pressing Shift-Ctrl-Enter:

=AVERAGE(IF(A1:H1<45,A1:H1))

 

[stevekanc]

Well, with all your #s in row 1, in A1:H1, this calculates the averag
for
those #s less than 45. I don't know how you will determine that 45 i
your
upper bound, but....

This is an array formula, meaning you commit it by pressin
Shift-Ctrl-Enter:

=AVERAGE(IF(A1:H1<45,A1:H1))

Thanks Duke, I tried this before but did not know about th
shift-cntrl-enter bit. Can you explain this please?

Much appreciated, Stev

 

[David Biddulph]

Did you look up "array formula" in Excel help?