Anyone know how to do an Average Function, excluding values that are out-of-range
Bill Reese
452
380
420
413
400
470
654
how can I take =AVERAGE(A1:A7) that excludes all values that are 10% out of
range with the rest of the values..
such as have the average function do this =AVERAGE(A1,A3,A4,A5,A6) and
exclude the A2 and the A7 values which are too high and too low to be
included in the average that I want to obtaing.
452
420
413
400
470
thanks,
Billy R.
Norman Harker
It's going to be based upon how you define the parameter "10% out of range with
the rest of the values."
One way:
=SUMPRODUCT((A1:A7>AVERAGE(A1:A7)*0.9)*(A1:A7<AVERAGE(A1:A7)*1.1)*(A1:A7))/SUMPR
ODUCT((A1:A7>AVERAGE(A1:A7)*0.9)*(A1:A7<AVERAGE(A1:A7)*1.1))
Returns 438.75
Your example was a tad confusing and it may well be that I have not interpreted
the parameters correctly.
With your data:
452,380,420,413,400,470,654
Average is 455.5741
90% of average is 410.0143
This excludes data items 2,5
110% of average is 501.1286
This excludes data item 7
The formula is in two parts:
=SUMPRODUCT((A1:A7>AVERAGE(A1:A7)*0.9)*(A1:A7<AVERAGE(A1:A7)*1.1)*(A1:A7))
returns 1755
That's the sum of those exceeding your two parameters
=SUMPRODUCT((A1:A7>AVERAGE(A1:A7)*0.9)*(A1:A7<AVERAGE(A1:A7)*1.1))
returns 4
That's the count of those exceeding the two parameters.
If you define your parameters differently, you'll get a different answer.
For example you might mean to have a parameter based on:
=(MAX(A1:A7)+MIN(A1:A7))/2
midway point = 517
Your two parameters are then:
=((MAX(A1:A7)+MIN(A1:A7))/2)*.9
465.3
=((MAX(A1:A7)+MIN(A1:A7))/2)*1.1
568.7
Giving the formula:
=SUMPRODUCT((A1:A7>((MAX(A1:A7)+MIN(A1:A7))/2)*0.9)*(A1:A7<((MAX(A1:A7)+MIN(A1:A
7))/2)*1.1)*(A1:A7))/SUMPRODUCT((A1:A7>((MAX(A1:A7)+MIN(A1:A7))/2)*0.9)*(A1:A7<(
(MAX(A1:A7)+MIN(A1:A7))/2)*1.1))
Returns: 470
And that is because the only piece of data that meets the criteria is item 6
--
Regards
Norman Harker MVP (Excel)
Common Prayer Day (Denmark); Ploughing Ceremony (Thailand); Waisak Day
(Indonesia).
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Bill Reese" <Bill...@delanet.com> wrote in message
news:pe-cnX-S8cR...@fcc.net...
Ron Rosenfeld
=SUM(--(A1:A7>(0.9*AVERAGE(A1:A7)))*--(A1:A7<(1.1*AVERAGE(A1:A7)))*A1:A7)
/SUM(--(A1:A7>(0.9*AVERAGE(A1:A7)))*--(A1:A7<(1.1*AVERAGE(A1:A7))))
To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. Excel
will place braces {...} around the formula.
Note that '400' is excluded also. Average of A1:A7 is 455.5714. 10% less
would be 410.0143 so, unless I am missing something, the numbers included
should be 452,420,413 and 470
--ron
--ron
Harlan Grove
Try the following array formula.
=AVERAGE(IF(ABS(A1:A7/AVERAGE(A1:A7)-1)<=0.1,A1:A7))
However, this excludes 400. The average of all 7 numbers is 455.6 (rounded),
so 400 is less than 90% of this (.9 * 455.6 = 410.0). So what do you mean by
'values that are 10% out of range with the rest of the values'?
--
Public Service Announcements:
1. Don't attach files to postings in this newsgroup.
2. Learn how to snip unnecessary text from quoted material. Indiscriminate
quoting wastes more bandwidth than file attachments.
Daniel.M
Are you expecting the values to be part of a normal distribution? If so, it
would explain the two values you want to exclude from the AVERAGE's calc),
if you want to exclude values belonging to the 5% values on each side of a
normal curve (important), here's one ARRAY formula (Ctrl-Shift-Enter):
=AVERAGE(IF(ABS(ZTEST(A1:A7,A1:A7)-0.5)<=0.45,A1:A7))
0.45 comes from 5% from the middle (thus excluding values in both extremes,
2*5% = 10%).
Regards,
Daniel M.
"Bill Reese" <Bill...@delanet.com> wrote in message
news:pe-cnX-S8cR...@fcc.net...
Myrna Larson
Harlan Grove
>Nice one, Harlan!
>
>> =AVERAGE(IF(ABS(A1:A7/AVERAGE(A1:A7)-1)<=0.1,A1:A7))
Same goes for Daniel Maher who took a different spin on this.