AVERAGEIFS with array as result

[Ricardo Mass]

Hello all,

I am trying to use the AVERAGEIFS formula, averaging figures in column D divided by those in column C when criteria are met, ie

=AVERAGEIFS($D$8:$D$27/$C$8:$C$27,$A$8:$A$27,"Sub Total",$D$8:$D$27,">0")

However, this is not working, does anyone know how I might be able to do this?

I have attached the file. We do not have figures for a report in April or June, so I am trying to get an average for the year (and also trying to learn a bit on the side).

Many thanks

Rich

[NeedForExcel]

So do you want the Sum of Values with Figures in Column D to be divided by the number of months?

[Ricardo Mass]

Hi,

I would like each figure in D that meets the criteria to be divided by the corresponding figure in C, then the average of those figures.

So - AVERAGE(16467/3, 21477/4, ... ..., 46,909/9)

Hope that is clearer!

[NeedForExcel]

But what is the Criteria?

[Ricardo Mass]

Criteria are column A being equal to "Sub Total", and column D being greater than zero (ie not blank)

[NeedForExcel]

SO if both match, then the Average should be calculated?

[NeedForExcel]

Try this.

=SUM(IF($A$4:$A$21="Sub Total",$D$4:$D$21/$C$4:$C$21))

Commit using Ctrl+Shift+Enter

Deep

[Ricardo Mass]

Thanks Deep!

I have amended to the below, and it works perfectly.

=AVERAGE(IF($A$4:$A$21="Sub Total",$D$4:$D$21/$C$4:$C$21))

[NeedForExcel]

Glad it helped!

Cheers!