{=AVERAGE(IF(AND(B2:B13241=1,D2:D13241=2),N2:N1324 1,""))}

why doesn't this formula work? thanx!

one way

=SUMPRODUCT((B2:B13241=1)*(D2:D13241=2)*(N2:N13241 ))/SUMPRODUCT((B2:B13241=1)*(D2:D13241=2)*(N2:N13241< 0))


Mike

"Knox" wrote:

{=AVERAGE(IF(AND(B2:B13241=1,D2:D13241=2),N2:N1324 1,""))}

why doesn't this formula work? thanx!

Use this. Array enter.
=AVERAGE(IF((B2:B22=1)*(D2:D22=2),N2:N22))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Knox" wrote in message
...
{=AVERAGE(IF(AND(B2:B13241=1,D2:D13241=2),N2:N1324 1,""))}

why doesn't this formula work? thanx!

"Knox" wrote in message
...
{=AVERAGE(IF(AND(B2:B13241=1,D2:D13241=2),N2:N1324 1,""))}

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:D13=2),N2:N13,""))}

"Knox" wrote:
{=AVERAGE(IF(AND(B2:B13241=1,D2:D13241=2),N2:N1324 1,""))}

You could also try it slightly amended like this, array-entered with CSE*:
=AVERAGE(IF((B2:B13241=1)*(D2:D13241=2),N2:N13241) )

*CSE = Press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank you all!

"Stephen" wrote:

"Knox" wrote in message
...
{=AVERAGE(IF(AND(B2:B13241=1,D2:D13241=2),N2:N1324 1,""))}

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:D13=2),N2:N13,""))}