How to Average with Conditions

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I want to average a column x if column y meets a certain condition. I
tried the following:

=IF(M6:M2000=2,AVERAGE(R6:R2000))

But it is getting a Value error.

Thanks
 
I want to average a column x if column y meets a certain condition. I
tried the following:

=IF(M6:M2000=2,AVERAGE(R6:R2000))

But it is getting a Value error.

Thanks
Click to expand...

Try...

=IF(M6:M2000=2,AVERAGE(R6:R2000),"")

which is an array function and must be entered using Ctrl + Shift +
Enter.
 
=SUMIF(M6:M2000,2,R6:R2000)/COUNTIF(M6:M2000,2)
Click to expand...

Thanks for the help. I couldn't get this formula to work. I copied
and pasted it directly:

=IF(M6:M2000=2,AVERAGE(R6:R2000),"")

But this formula did work:

=SUMIF(M6:M2000,2,R6:R2000)/COUNTIF(M6:M2000,2)
 
But this formula did work:

=SUMIF(M6:M2000,2,R6:R2000)/COUNTIF(M6:M2000,2)
Click to expand...

Sorry about that, I deluded myself because of the column R values I
had used.
Correct array equivalent is...

=SUM(IF(M6:M2000=2,R6:R2000))/COUNT(IF(M6:M2000=2,M6:M2000))

which of cause is a silly option.

Ken Johnson
 
=AVERAGE(IF(M6:M2000=2,R6:R2000))

array formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
=AVERAGE(IF(M6:M2000=2,R6:R2000))

array formula.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...

Thanks Bob.

My silly option was a really silly option!

Ken Johnson
 
Back
Top