G
Guest
{=AVERAGE(IF(AND(B2:B13241=1,D2
13241=2),N2:N13241,""))}
why doesn't this formula work? thanx!
13241=2),N2:N13241,""))}why doesn't this formula work? thanx!
G
Guest
one way
=SUMPRODUCT((B2:B13241=1)*(D213241=2)*(N2:N13241))/SUMPRODUCT((B2:B13241=1)*(D213241=2)*(N2:N13241<>0))
Mike
=SUMPRODUCT((B2:B13241=1)*(D2
13241=2)*(N2:N13241))/SUMPRODUCT((B2:B13241=1)*(D213241=2)*(N2:N13241<>0))Mike
D
Don Guillett
Use this. Array enter.
=AVERAGE(IF((B2:B22=1)*(D222=2),N2:N22))
=AVERAGE(IF((B2:B22=1)*(D2
22=2),N2:N22))
S
Stephen
Knox said:{=AVERAGE(IF(AND(B2:B13241=1,D2
why doesn't this formula work? thanx!
Because you can't use AND with arrays (as it returns only a single result,
not an array). Instead you need to multiply the two arrays:
{=AVERAGE(IF((B2:B13=1)*(D2
13=2),N2:N13,""))}
G
Guest
Knox said:{=AVERAGE(IF(AND(B2:B13241=1,D2
You could also try it slightly amended like this, array-entered with CSE*:
=AVERAGE(IF((B2:B13241=1)*(D2
13241=2),N2:N13241))*CSE = Press CTRL+SHIFT+ENTER to confirm the formula
---
G
Guest
Thank you all!
Stephen said:Because you can't use AND with arrays (as it returns only a single result,
not an array). Instead you need to multiply the two arrays:
{=AVERAGE(IF((B2:B13=1)*(D2