Excel out of resources!? Issue with using AVERAGE(IF(...

[brucewflee]

Greetings to the almighty Excel community,

I have an excel file which was running fine until I have implemented an "AVERAGE(IF(..." and is slowing down the calculations. It has got to a point where it is prompting out the error message "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated. ".

The offending formula is carrying out a function where it will average a certain column when the column header is matching with a criteria name and the corresponding rows are also carrying values.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]Apple[/TD]
[TD]Orange[/TD]
[TD]Pear[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in F2 = {AVERAGE(IF($A$1:$C$1=$E2,IF(ISNUMBER($A:$C),$A:$C)))}

To make the matter worse, I have at least 30 of these array formula running at the same time. I am guessing that by changing "$A:$C" into "$A1:$C1000" will help speeding up, but I have already had 100,000 (rows) x 30 (columns) of data and is growing by days so I would really like it to look for the whole columns.

Is there any straightforward function that can carry out the same task? It would be appreciated if someone can help simplifying the formula or providing a solution.

Thank you very much for the help in advance.

 

[MarcelBeug]

Try

=AVERAGE(INDEX($A:$C,0,MATCH(E2,$A$1:$C$1,0)))

Average ignores non numeric values; no need to check for that.

 

[brucewflee]

Try

=AVERAGE(INDEX($A:$C,0,MATCH(E2,$A$1:$C$1,0)))

Average ignores non numeric values; no need to check for that.

Thanks Marcel! The excel is working in the blink of an eye again!

 

[MarcelBeug]

You're welcome.