AVERAGEIF problem

[TPaton]

I am trying to create a formula on a 'summary' sheet that will be the average
of the same cell on a series of 'detail' sheets. Using the AVERAGEIF formula
would appear to be the answer, but I get a #VALUE error when I put the
following formula in the Summary cell

=AVERAGEIF(Sheet1:Sheet8!D12,">35")

Any thoughts, ideas, suggestions??

Thankds

 

[T. Valko]

You can't do a conditional average across multiple sheets using that
function. There are other formulas you could use but they are pretty long
and complex.

To keep things simple I would just use an additional cell with a formula on
each sheet like this:

=IF(D12>35,D12,"")

Then just get the average of those cells:

=AVERAGE(Sheet1:Sheet8!A1)

 

[Phav]

Hi, I seem to be having a problem along the same lines as TPaton - so just
wondering if you can help me. It looks like my formula has been accepted but
I don't get any result.
I would like the formula to translate the Meet as 70% marks, Exceed as 100%
marks and then average out. So what I did was set up this criteria in a
different column AJ
Below is my formula... thanks for your time
=AVERAGE(IF(D24="Exceed",$AJ$3,IF(D24="Meet",$AJ$4,IF(D24="Needs
Dev",$AJ$5,IF(D24="NA",$AJ$6)))))

D
2 Meet
3 Meet
4 Meet
5 #VALUE!

AJ
3 Exceed 100%
4 Meet 70%
5 Needs Dev 0%
6 NA

Thanks PD

 

[Roger Govier]

Hi

I think I would be using a helper column E, with the formula in E2
=IF(NOT(ISERROR(D2)),VLOOKUP(D2,$AJ$3:$AK$6,2,0),"")
and copied down as required.

Then the overall result would be
=AVERAGE(E2:E5)
with the cell being formatted as percent.