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

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" wrote:


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




--
stevekanc

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

Much appreciated, Steve

Did you look up "array formula" in Excel help?
--
David Biddulph

"stevekanc" <s190468&lt;at)btinternet - dot - com wrote in message
...

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

Duke Carey;559221 Wrote:
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))