Discarding values

[Freddie Fish]

I am sure this is probably simple but here goes:

I have put together a sheet that records my blood sugar levels over a
month. I am using the sheet to calculate averages for different times
of the day and under different circumstances.

What I want to be able to do is in any one column (which may contain
any number of values up to 20 or so values) - discard the highest
value and the lowest value and to calculate the average based on
what's left. This will allow me to filter out abnormal spikes
automatically.

Any help gratefully received.

John

 

[Pete_UK]

Assuming your data values are in A1:A20, try this array* formula:

=AVERAGE(IF((A1:A20<>MAX(A1:A20))*(A1:A20<>MIN(A1:A20)),A1:A20))

The formula discards any values that are maximum or minimum in the
range, then averages the remainder.

* As this is an array formula, then once you have typed it in (or
subsequently amend it) you must use CTRL-SHIFT-ENTER to commit it
rather than the usual ENTER. If you do this correctly, then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - you must not type these yourself.

Hope this helps.

Pete

 

[Niek Otten]

Hi John,

Look in HELP for the TRIMMEAN() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am sure this is probably simple but here goes:
|
| I have put together a sheet that records my blood sugar levels over a
| month. I am using the sheet to calculate averages for different times
| of the day and under different circumstances.
|
| What I want to be able to do is in any one column (which may contain
| any number of values up to 20 or so values) - discard the highest
| value and the lowest value and to calculate the average based on
| what's left. This will allow me to filter out abnormal spikes
| automatically.
|
| Any help gratefully received.
|
| John

 

[Freddie Fish]

Thanks for both of these replys I will try them.

Thanks