Discarding values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Freddie Fish - 13 Feb 2008 12:41 GMT

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

Reply to this Message

Pete_UK - 13 Feb 2008 12:53 GMT

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

On Feb 13, 12:41 pm, Freddie Fish <johnnpicton-co...@yahoo.co.uk>
wrote:

> I am sure this is probably simple but here goes:
>
[quoted text clipped - 11 lines]
>
> John

Reply to this Message

Niek Otten - 13 Feb 2008 12:56 GMT

Hi John,

Look in HELP for the TRIMMEAN() function

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am sure this is probably simple but here goes:
|
[quoted text clipped - 11 lines]
|
| John

Reply to this Message

Freddie Fish - 13 Feb 2008 13:22 GMT

Thanks for both of these replys I will try them.

Thanks

>Hi John,
>
>Look in HELP for the TRIMMEAN() function

Reply to this Message