AVERAGEIF problem

T

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

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)
 
P

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(D2:D4="Exceed",$AJ$3,IF(D2:D4="Meet",$AJ$4,IF(D2:D4="Needs
Dev",$AJ$5,IF(D2:D4="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
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top