IF THEN do an AVERAGE

[pika.white]

Hi all, I usually can find the answers to what I need just by reading
this group but I have to actually request a little advice myself now.
I know it's simple but I have been in a brain fog trying to figure it
out.

I have a sheet that has 2 sets of data, say APPLES and ORANGES. Each
has it's own value. I want to find all the values for APPLES and
average them, then likewise with ORANGES. Which of the many functions
do I want to use?

A B
1 Apples 2:00
2 Oranges 3:00
3 Oranges 5:00
4 Apples 1:00
5 Oranges 4:00

So I need to find IF A:A=APPLES, then AVERAGE all times in Col. B for
APPLES (end result=1:30). ORANGES would be 4:00. I don't know how to
tell XL to find APPLES in A, then AVERAGE Col. B only for those values.


Any help would be greatly appreciated.

 

[Harlan Grove]

(e-mail address removed) wrote...
....

So I need to find IF A:A=APPLES, then AVERAGE all times in Col. B for
APPLES (end result=1:30). ORANGES would be 4:00. I don't know how to
tell XL to find APPLES in A, then AVERAGE Col. B only for those values.

....

If you really need to use entire column ranges, then for apples

=SUMIF(A:A,"APPLES",B:B)/COUNTIF(A:A,"APPLES")

Replace "APPLES" with "ORANGES" to average the col B values for oranges.

 

[pika.white]

Thanks - that indeed does work. (alternatively, I used an array formula
{=AVERAGE(IF(X2:X4="APPLES",Y2:Y4,""))} that worked too but I like your
better - it's just logical!).

Another side question based off of those results: There are some
values that are either going to be blank or have the ubiquitous #### in
it because there are a few records that will have errors. How can I
suppress the errors (e.g. #VALUE, #DIV/), ####)? I have a hunch and
am working on that. Thanks in advance (again).

 

[Harlan Grove]

(e-mail address removed) wrote...

Thanks - that indeed does work. (alternatively, I used an array formula
{=AVERAGE(IF(X2:X4="APPLES",Y2:Y4,""))} that worked too but I like your
better - it's just logical!).

Another side question based off of those results: There are some
values that are either going to be blank or have the ubiquitous #### in
it because there are a few records that will have errors. How can I
suppress the errors (e.g. #VALUE, #DIV/), ####)? I have a hunch and
am working on that. Thanks in advance (again).

Suppressing #DIV/0! errors from no matches may be done using

=IF(COUNTIF(A:A,x),SUMIF(A:A,x,B:B)/COUNTIF(A:A,x),"")

Suppressing any errors makes the formula more complicated.

=IF(AND(COUNTIF(A:A,x),ISNUMBER(SUMIF(A:A,x,B:B))),
SUMIF(A:A,x,B:B)/COUNTIF(A:A,x),"")