average with some conditions

[ditchy]

Hi there excelers, can you help me with this please
colA colB colC colD colE colF colG colH colJ
row1 2 3 4 6 2 8 2 3 average
A:H
row2 6 4 9 5 1 8 1 etc
etc 3 1 2 4 3 etc
etc 2 7 etc

what I need is the average for each row, problem is I would like to
ignore the highest and lowest numbers in that row. Another problem is
that some rows may only have data in 1,2 or 3 cells. Is there a way to
do this?
T.I.A.
regards Ditchy

 

[J.E. McGimpsey]

Your problem statement is ambiguous - when you say out want to
ignore the highest and lowest numbers in the row, do you mean that
in row 1, say, all three 2's should be ignored or just one?

If just one:

=TRIMMEAN(A1:H1,2/COUNT(A1:H1))

If all, one way (array-entered:CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(IF(A1:H1=MAX(A1:H1),"",IF(A1:H1=MIN(A1:H1),"",A1:H1)))/(COUNTIF(
A1:H1,"<"&MAX(A1:H1))-COUNTIF(A1:H1,MIN(A1:H1)))

If the row has only 1 or 2 entries, do you want to ignore them or
not?

 

[ditchy]

Your problem statement is ambiguous - when you say out want to
ignore the highest and lowest numbers in the row, do you mean that
in row 1, say, all three 2's should be ignored or just one?

If just one:

=TRIMMEAN(A1:H1,2/COUNT(A1:H1))

If all, one way (array-entered:CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(IF(A1:H1=MAX(A1:H1),"",IF(A1:H1=MIN(A1:H1),"",A1:H1)))/(COUNTIF(
A1:H1,"<"&MAX(A1:H1))-COUNTIF(A1:H1,MIN(A1:H1)))

If the row has only 1 or 2 entries, do you want to ignore them or
not?

Thank you for the quick reply

I would like the lowest number(if there are more than one of the same
value ignore them all) same with the highest number.
If the row has one, two or three entries, ignore otherwise I will not
get an average.
Thank you
Ditchy

 

[Jerry W. Lewis]

=AVERAGE(IF((A1:H1=MIN(A1:H1))+(A1:H1=MAX(A1:H1)),"",IF(ISNUMBER(A1:H1),A1:H1,"")))

returns 4 for line 1 (ignores 8 and all 2's) and 5.75 for line 2
(ignores 9, all 1's and the blank cell).

Jerry

 

[ditchy]

=AVERAGE(IF((A1:H1=MIN(A1:H1))+(A1:H1=MAX(A1:H1)),"",IF(ISNUMBER(A1:H1),A1:H1,"")))

returns 4 for line 1 (ignores 8 and all 2's) and 5.75 for line 2
(ignores 9, all 1's and the blank cell).

Jerry

Thank you for the quick response,
I have one more question, will it work if the numbers are of a time format?
many thanks
Ditchy

 

[Jerry W. Lewis]

Sure. Time is a formatted decimal fraction of a day. As far as this
formula is concerned, it is just a number.

Jerry

 

[ditchy]

Sure. Time is a formatted decimal fraction of a day. As far as this
formula is concerned, it is just a number.

Jerry
Thanks for that Jerry,

tried it with times 0:03:25 as such but could not get it to work, came
up with error in cell, any clues
regards Ditchy
T.I.A.

 

[Jerry W. Lewis]

Sorry, my crystal ball is cluoudy. What error are you getting? How
many time values are you averaging? ...

If you are getting #DIV/0, then I would guess that these are text
strings rather than Excel time values. An Excel time value of
3 minutes and 25 seconds, when formatted as General, would display as
0.00237268518518519 (= 3/24/60+25/24/60/60)
Alternately, if these are text instead of time, then COUNT() of the the
cells containing "times" would be zero.

Assuming that they are text, instead of times, copy a numeric zero,
select the cells containing "times", and Edit|Paste Special|Add to
convert to (numeric) Excel time values.

Jerry

 

[ditchy]

Sorry, my crystal ball is cluoudy. What error are you getting? How
many time values are you averaging? ...

If you are getting #DIV/0, then I would guess that these are text
strings rather than Excel time values. An Excel time value of
3 minutes and 25 seconds, when formatted as General, would display as
0.00237268518518519 (= 3/24/60+25/24/60/60)
Alternately, if these are text instead of time, then COUNT() of the the
cells containing "times" would be zero.

Assuming that they are text, instead of times, copy a numeric zero,
select the cells containing "times", and Edit|Paste Special|Add to
convert to (numeric) Excel time values.

Jerry
Thanks again Jerry, but I am still having trouble

I have rows of 8 cells formatted as h:mm:ss and I need the average of
these times, but ignoring the highest and lowest time. Some of the
cells are empty and in some cases there may only be two cells in a row
with times in them.
Is it possible to do, or am I asking to much?
hope this removes the cloud
Regards
Ditchy

 

[Jerry W. Lewis]

Two replies back, I told you how to do it if they are Excel time values.
My previous reply told you how to convert text that just looks like
time (note that formatting just changes the display, not the value, so
if they were text before, they will still be text after you format them
to time). You previously said that my suggestion was not working for
you, but still have not given deatils as to how it is failing. Without
those details, there is not much more that I can say.

You do raise two new unanswered questions: What result do you want when
there are only two values in the range? Do you want to trim both of
them off and get a #DIV/0 error because there is no data or do you want
to average all values in that case? Same question with three values
where two are equal ...

Jerry

 

[ditchy]

Two replies back, I told you how to do it if they are Excel time values.
My previous reply told you how to convert text that just looks like
time (note that formatting just changes the display, not the value, so
if they were text before, they will still be text after you format them
to time). You previously said that my suggestion was not working for
you, but still have not given deatils as to how it is failing. Without
those details, there is not much more that I can say.

You do raise two new unanswered questions: What result do you want when
there are only two values in the range? Do you want to trim both of
them off and get a #DIV/0 error because there is no data or do you want
to average all values in that case? Same question with three values
where two are equal ...

Jerry
Thank you again Jerry

Sorry I am a bit slow on the uptake, have just figured out the the
formula needs to be entered as an array and it works perfectly as you
previously explained. Unanswered Questions:
When there are only two values in the range, average all values in
that case. With three values where two are equal ...average all values
in that case. Thank you for your perserverance, your help is greatly
appreciated
Regards Ditchy

 

[Jerry W. Lewis]

Sorry, I usually point out where array entry is required, but failed to
do so here. Glad you figured it out.

If the array formula from my 10/14 reply is
=AVERAGE( conditional )
then
=IF( COUNT( conditional )=0, AVERAGE(A1:H1), AVERAGE( conditional ) )
will switch to a simple average if there are only two observations, or 3
observations with two duplicates. (The spaces in the above formula are
optional, but included here for easier reading.)

Jerry

 

[ditchy]

Sorry, I usually point out where array entry is required, but failed to
do so here. Glad you figured it out.

If the array formula from my 10/14 reply is
=AVERAGE( conditional )
then
=IF( COUNT( conditional )=0, AVERAGE(A1:H1), AVERAGE( conditional ) )
will switch to a simple average if there are only two observations, or 3
observations with two duplicates. (The spaces in the above formula are
optional, but included here for easier reading.)

Jerry
Thank you again for your help, it has solved some problems for me

How do I connect these two formulas, this is the one from 10/14 and
works perfectly for me if there are more than 3 cells with data and no
data the same
=AVERAGE(IF((A1:H1=MIN(A1:H1))+(A1:H1=MAX(A1:H1)),"",IF(ISNUMBER(A1:H1),A1:H1,"")))
and the latest one
=IF( COUNT( conditional )=0, AVERAGE(A1:H1), AVERAGE( conditional ) )
I have data in columns down to A100:H100
Thanks again for your help
Regards
Ditchy

 

[Jerry W. Lewis]

As I said, replace "conditional" with

IF((A1:H1=MIN(A1:H1))+(A1:H1=MAX(A1:H1)),"",IF(ISNUMBER(A1:H1),A1:H1,""))

which was the conditional formula inside the AVERAGE() from my post of
10/14.

Jerry

 

[ditchy]

As I said, replace "conditional" with

IF((A1:H1=MIN(A1:H1))+(A1:H1=MAX(A1:H1)),"",IF(ISNUMBER(A1:H1),A1:H1,""))

which was the conditional formula inside the AVERAGE() from my post of
10/14.

Jerry

ditchy wrote:
Thank you for that

Problem now solved
Regards
Ditchy