Problems with array formula

M

manan

Hi

i have a spread sheet which has data about different sectors. I want to
find out the average of each sector.
{=AVERAGE(IF($E$7:$E$307=IU85,J7:J307))}

This is teh formula i am using. If column e which has details about the
sector of each company matches the sector name in IU85 then give me the
average of correspondingdata in column j.
Now the issue is that some companies data is not present and they were
blank. It is giving error while computing the average. Secondly i tried
to change the blanks and added text in place of blanks. But it is still
giving me those errors.

Can anyone please help me resolve this issue.
Thanks in adavnce
Regards
Manan
 
B

Biff

Hi!

Try this:

Array entered:

=AVERAGE(IF(($E$7:$E$307=IU85)*(J7:J307<>""),J7:J307))

Biff
 
A

aresen

Biff said:
Hi!

Try this:

Array entered:

=AVERAGE(IF(($E$7:$E$307=IU85)*(J7:J307<>""),J7:J307))

Biff
Click to expand...
I don't believe you can use AVERAGE in an array formula accurately. It
would correctly sum the terms but would always be dividing by 301 in
your case.
If you intend to copy this formula down to get the averages of
different sectors then use $J$7:$J$307 instead of J7:J307.
Anyway, try the following instead:
=SUM((IU85=$E$7:$E$307)*$J$7:$J$307)/SUM((IU85=$E$7:$E$307)*($J$7:$J$307<>"")*1)
Don't put any text in column J or you will get the #VALUE! error. A
blank entry will not be interpreted as zero which is what I assume you
want.
 
B

Biff

I don't believe you can use AVERAGE in an array formula accurately

Sure you can. I do it every day.

Biff
 
Top