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

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(D1235,D12,"")

Then just get the average of those cells:

=AVERAGE(Sheet1:Sheet8!A1)

--
Biff
Microsoft Excel MVP


"TPaton" wrote in message
...
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

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







"T. Valko" wrote:

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(D1235,D12,"")

Then just get the average of those cells:

=AVERAGE(Sheet1:Sheet8!A1)

--
Biff
Microsoft Excel MVP


"TPaton" wrote in message
...
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

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.
--
Regards
Roger Govier

"Phav" wrote in message
...
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







"T. Valko" wrote:

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(D1235,D12,"")

Then just get the average of those cells:

=AVERAGE(Sheet1:Sheet8!A1)

--
Biff
Microsoft Excel MVP


"TPaton" wrote in message
...
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