multiple IF statements in array

  • Thread starter Thread starter Julz
  • Start date Start date
J

Julz

How can I need to add another if, to this array
{=AVERAGE(IF(close!U2:U19000="Y",close!R2:R19000))}
if close!U2:U19000="Y" AND if close!Y2:Y19000="Y",...

So, I have several Excel books, but they don't really go into any
complex formulas. I haven't been able to figure out when I should use
arrays, sumproduct as opposed to count, (--(sumproduct, etc. Is there a
resource where I can see the logic behind the formula? I want to learn
to fish!!

Thanx!!
~Julz
 
Hi
try (as array formula)

AVERAGE(IF((close!U2:U19000="Y")*(close!Y2:Y19000="Y"),close!R2:R19000)
)

Or as SUMPRODUCT alternative
=SUMPRODUCT(--(close!U2:U19000="Y"),--(close!Y2:Y19000="Y"),close!R2:R1
9000)/SUMPRODUCT(--(close!U2:U19000="Y"),--(close!Y2:Y19000="Y"))
 
try this array formula.UNTESTED

=AVERAGE(IF(close!u2:u19000="Y",nextcondition,lastcondition,0))
 
Back
Top