array formulas-sumproduct and average
 

Hello, I need hel. I am using this formula
=SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289))) I don't know what's wrong
with it. I need to find the average for column H in relation to 1(under 2
yrs) in Column E. Hope this makes sense.

Thanks,
Becky

Try: =AVERAGE(IF(E2:E289=1,H2:H289))
Array-enter the formula with CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Becky" wrote in message
...
Hello, I need hel. I am using this formula
=SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289))) I don't know what's
wrong
with it. I need to find the average for column H in relation to 1(under 2
yrs) in Column E. Hope this makes sense.

Thanks,
Becky

And if an error trap is needed to return blanks: ""
instead of #DIV/0! , then try:

=IF(ISERROR(AVERAGE(IF(E2:E289=1,H2:H289))),"",AVE RAGE(IF(E2:E289=1,H2:H289)
))

(Array entered as before)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks, that did it.

"Max" wrote:

Try: =AVERAGE(IF(E2:E289=1,H2:H289))
Array-enter the formula with CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Becky" wrote in message
...
Hello, I need hel. I am using this formula
=SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289))) I don't know what's
wrong
with it. I need to find the average for column H in relation to 1(under 2
yrs) in Column E. Hope this makes sense.

Thanks,
Becky

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
Becky wrote in message
...
Thanks, that did it.