How to Average with Conditions

[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

 

[Ken Johnson]

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

Try...

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

which is an array function and must be entered using Ctrl + Shift +
Enter.

 

[Jim Cone]

=SUMIF(M6:M2000,2,R6:R2000)/COUNTIF(M6:M2000,2)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Paul" <[email protected]>
wrote in message
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

 

[Paul]

=SUMIF(M6:M2000,2,R6:R2000)/COUNTIF(M6:M2000,2)

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)

 

[Ken Johnson]

But this formula did work:

=SUMIF(M6:M2000,2,R6:R2000)/COUNTIF(M6:M2000,2)

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

 

[Bob Phillips]

=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)

 

[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)

Thanks Bob.

My silly option was a really silly option!

Ken Johnson