Average(if(and...

  • Thread starter Thread starter pacman2k4
  • Start date Start date
P

pacman2k4

Hi all,

I am trying to average some times based on two conditionals.

first column I have names, second is times, and third is a yes/no.

Trying to average the time of the person named "Paulson" who answere
"yes".

here's the formula I tried to use:

{=AVERAGE(IF(AND(B4:B16="Paulson",D4:D16="yes"),C4:C16))}

This seems to work with one condition:

{=AVERAGE(IF(D4:D16="yes",C4:C16))}

Thought adding AND would enable two but doesn't seem to be working.
I'm getting a 0:00 time return, so it seems that the statement is no
returning true.

Any help would be much appreciated
 
Unless I missed something, that "solution" gave me an #VALUE error,
Dave.

The only way I can think to do it is to add a new column, maybe column
E, which is =IF(AND(B4="Paulson",D4="yes"),C4,"") in the cell on row
4, and then copy it down to row 16.

Then, use the formula =AVERAGE(E4:E16).

Be careful to use "" in the false condition, as I have it written
above, instead of 0. Otherwise, your average will be wrong.

By the way, if you have multiple people for whom you want averages,
you'd need a column for each person, in this solution. Of course, it
might help if you put the person's name as the column heading and used
the following formula instead:

=IF(AND($B4=B$1,$D4="yes"),$C4,"")

then you can copy that formula down the row AND across all the columns
for each person, and use the =AVERAGE formulas at the bottom of each
row.

Sorry that this is not a very elegant answer, but it should do the
trick.
 
Wow - now I see how it works! After entering
=AVERAGE(IF(B4:B16="Paulson",IF(D4:D16="yes",C4:C16)))
press Ctrl-Shift-Enter instead of Enter and it becomes an Array
formula. That's a REALLY cool feature! It's going to take a little
playing with to get used to, but cool!
 
Back
Top