Average IF (two conditions)

Hello,

I would like to produce the average of the values in cells CC2 through CC3393, provided that their corresponding value in column G is 1 and their corresponding value in column H is 3.011. All of these values are on the sheet called, "By Image Data," which is a different sheet than the one on which I am trying to report the average. This is what I came up with, but it is giving me an error. Any help would be appreciated.

=AVERAGE(IF(AND('By Image Data'!G2:G3393=1,'By Image Data'!H2:H3393=3.011),'By Image Data'!CC2:CC3393,""))

Thanks!

Try

=AVERAGE( IF( 'By Image Data'!G2:G3393=1, IF('By Image Data'!H2:H3393=3.011, 'By Image Data'!CC2:CC3393,"")))

Originally Posted by shg

Try

=AVERAGE( IF( 'By Image Data'!G2:G3393=1, IF('By Image Data'!H2:H3393=3.011, 'By Image Data'!CC2:CC3393,"")))

Hm. Thanks for that idea, but that didn't work either. I'm still getting #VALUE!

Sorry, it's an array formula, meaning it MUST be confirmed with Ctrl+Shift+Enter, not just Enter. (With the cursor in the formula bar, hold down Ctrl and Shift, then press Enter.) You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

Also, the value in column H must be exactly 3.011; it can't be some longer value that displays as 3.011 (but the formula could be adapted).

Originally Posted by shg

Sorry, it's an array formula, meaning it MUST be confirmed with Ctrl+Shift+Enter, not just Enter. (With the cursor in the formula bar, hold down Ctrl and Shift, then press Enter.) You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

Also, the value in column H must be exactly 3.011; it can't be some longer value that displays as 3.011 (but the formula could be adapted).

Amazing! Thank you! Having this formula work reduces the amount of time I'd need to spend on this particular operation by about 6 hours.

Great

I have a girlofscience (actually a daughterofscience) at UNC Hospitals in Chapel Hill.

Originally Posted by shg

Great

I have a girlofscience (actually a daughterofscience) at UNC Hospitals in Chapel Hill.

That's not far from me at all! What a small world. I'm a grad student in neuroscience at Duke.

... at Duke.

That doesn't make you a bad person

She's a second-year Rehab resident.