I'm trying to find the average for a person with scores in column 4 on 3
separate sheets. The following function returns #VALUE.. What am I doing
wrong? Because the person may be in a different row on each sheet, I'm
thinking I need to use the VLOOKUP function.... Help!!! Also, as an
aside, the person may have a zero on one sheet that would affect the
average score...

=AVERAGE(VLOOKUP(A4,Week03:Week01!A$4:G$30,4,0))


--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile: http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=477377

Hi!

the person may have a zero on one sheet that would affect the
average score...

Does that mean you want to exclude any zeros from the average?

Biff

"quailhunter"
wrote in message
...

I'm trying to find the average for a person with scores in column 4 on 3
separate sheets. The following function returns #VALUE.. What am I doing
wrong? Because the person may be in a different row on each sheet, I'm
thinking I need to use the VLOOKUP function.... Help!!! Also, as an
aside, the person may have a zero on one sheet that would affect the
average score...

=AVERAGE(VLOOKUP(A4,Week03:Week01!A$4:G$30,4,0))


--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=477377

Hi Biff,
Yes. If they have a zero, then this should not count in their average
score.

Thanks.

Biff Wrote:
Hi!

the person may have a zero on one sheet that would affect the
average score...

Does that mean you want to exclude any zeros from the average?

Biff

"quailhunter"

wrote in message
...

I'm trying to find the average for a person with scores in column 4
on 3
separate sheets. The following function returns #VALUE.. What am I
doing
wrong? Because the person may be in a different row on each sheet,
I'm
thinking I need to use the VLOOKUP function.... Help!!! Also, as an
aside, the person may have a zero on one sheet that would affect the
average score...

=AVERAGE(VLOOKUP(A4,Week03:Week01!A$4:G$30,4,0))


--
quailhunter

------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread:
http://www.excelforum.com/showthread...hreadid=477377



--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile: http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=477377

Hi!

After reading your post again I see that the name and score can be anywhere
within the range on each of the sheets. I don't think this is possible to do
(at least, I don't know how to do it!).

If the data was in the same cell on each sheet this would be simple but a
multi-conditional (name and score greater than zero)average across sheets is
not possible! Excel just doesn't handle 3D referencing easily. If anyone CAN
do this I be interested to see how! (using a single formula!)

Biff

"quailhunter"
wrote in message
...

Hi Biff,
Yes. If they have a zero, then this should not count in their average
score.

Thanks.

Biff Wrote:
Hi!

the person may have a zero on one sheet that would affect the
average score...

Does that mean you want to exclude any zeros from the average?

Biff

"quailhunter"

wrote in message
...

I'm trying to find the average for a person with scores in column 4
on 3
separate sheets. The following function returns #VALUE.. What am I
doing
wrong? Because the person may be in a different row on each sheet,
I'm
thinking I need to use the VLOOKUP function.... Help!!! Also, as an
aside, the person may have a zero on one sheet that would affect the
average score...

=AVERAGE(VLOOKUP(A4,Week03:Week01!A$4:G$30,4,0))


--
quailhunter

------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread:
http://www.excelforum.com/showthread...hreadid=477377



--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=477377

quailhunter wrote...
I'm trying to find the average for a person with scores in column 4 on 3
separate sheets. The following function returns #VALUE.. What am I doing
wrong? Because the person may be in a different row on each sheet, I'm
thinking I need to use the VLOOKUP function.... Help!!! Also, as an
aside, the person may have a zero on one sheet that would affect the
average score...

=AVERAGE(VLOOKUP(A4,Week03:Week01!A$4:G$30,4,0) )

If you're using VLOOKUP, there'd be at most only one match for A4 in
any of the worksheets' A4:A30 ranges? If so, you'd need a list of the
worksheet names in some range (I'll refer to it as WSL), then you could
try

=SUMPRODUCT(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4, INDIRECT("'"&WSL&"'!D4:D30")))
/SUMPRODUCT(--(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4,INDIRECT("' "&WSL&"'!D4:D30"))0))

That doesn't work for me.

SUMPRODUCT(--(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4,INDIRECT("' "&WSL&"'!D4:D30"))0))

Returns the incorrect count

Biff

"Harlan Grove" wrote in message
oups.com...
quailhunter wrote...
I'm trying to find the average for a person with scores in column 4 on 3
separate sheets. The following function returns #VALUE.. What am I doing
wrong? Because the person may be in a different row on each sheet, I'm
thinking I need to use the VLOOKUP function.... Help!!! Also, as an
aside, the person may have a zero on one sheet that would affect the
average score...

=AVERAGE(VLOOKUP(A4,Week03:Week01!A$4:G$30,4,0 ))

If you're using VLOOKUP, there'd be at most only one match for A4 in
any of the worksheets' A4:A30 ranges? If so, you'd need a list of the
worksheet names in some range (I'll refer to it as WSL), then you could
try

=SUMPRODUCT(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4, INDIRECT("'"&WSL&"'!D4:D30")))
/SUMPRODUCT(--(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4,INDIRECT("' "&WSL&"'!D4:D30"))0))

D

Biff wrote:
That doesn't work for me.

SUMPRODUCT(--(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4,INDIRECT("' "&WSL&"'!D4:D30"))0))

Returns the incorrect count

[...]

Do you have negative numbers in the ranges of interest?

No negatives, either a value or zero.


--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile: http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=477377

Aladin Akyurek wrote...
Biff wrote:
That doesn't work for me.

SUMPRODUCT(--(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4,INDIRECT("' "&WSL&"'!D4:D30"))0))

Returns the incorrect count
....
Do you have negative numbers in the ranges of interest?

Interesting point. As I've written before, if there can be valid
positive and negative values, there can also be valid zero values. If
there can be valid zero values, you can't ignore all zeros in averages.

That said, if all the values were positive *and* there were only one
entry matching A1 in each other worksheets' A3:A30 range, then this
should work (at least it works in my tests).

Do you have negative numbers in the ranges of interest?

No negatives.

The second Sumproduct is only counting a single instance where the value is
0 per sheet:

Sheet2

Sue....... 34
Joe........ 34
Bob....... 93
Jim........ 26
Joe....... 100
Tom...... 82

Sheet3

Tom....... 7
Sue........ 9
Liz........ 88
Joe........ 0
Joe....... 93
Bob...... 66

=SUMPRODUCT(--(SUMIF(INDIRECT("'"&wsl&"'!A4:A30"),A4,INDIRECT("' "&wsl&"'!D4:D30"))0))

Returns 2

Biff

"Aladin Akyurek" wrote in message
...
D

Biff wrote:
That doesn't work for me.

SUMPRODUCT(--(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4,INDIRECT("' "&WSL&"'!D4:D30"))0))

Returns the incorrect count

[...]

Do you have negative numbers in the ranges of interest?

That said, if all the values were positive *and* there were only one
entry matching A1 in each other worksheets' A3:A30 range, then this
should work (at least it works in my tests).

Yes, it works under those conditions, where there is only a single instance
(or none) per sheet. I was working on a solution in which there were
multiple instances per sheet. How could that be done? Nothing I've tried
works. The difficult part is the count.

Biff

"Harlan Grove" wrote in message
ups.com...
Aladin Akyurek wrote...
Biff wrote:
That doesn't work for me.

SUMPRODUCT(--(SUMIF(INDIRECT("'"&WSL&"'!A4:A30"),A4,INDIRECT("' "&WSL&"'!D4:D30"))0))

Returns the incorrect count
...
Do you have negative numbers in the ranges of interest?

Interesting point. As I've written before, if there can be valid
positive and negative values, there can also be valid zero values. If
there can be valid zero values, you can't ignore all zeros in averages.

That said, if all the values were positive *and* there were only one
entry matching A1 in each other worksheets' A3:A30 range, then this
should work (at least it works in my tests).

Biff wrote...
That said, if all the values were positive *and* there were only one
entry matching A1 in each other worksheets' A3:A30 range, then this
should work (at least it works in my tests).

Yes, it works under those conditions, where there is only a single instance
(or none) per sheet. I was working on a solution in which there were
multiple instances per sheet. How could that be done? Nothing I've tried
works. The difficult part is the count.
....

There are times it's a PITA that Excel really isn't a 3D spreadsheet.

The simplest solution would be using Laurent Longre's MOREFUNC.XLL
add-in, specifically, using that add-in's THREED function.

=SUMPRODUCT(--(THREED(A:Z!$A$4:$A$30)=A4),THREED(A:Z!$D$4:$D$30) )
/SUMPRODUCT((THREED(A:Z!$A$4:$A$30)=A4)*(THREED(A:Z !$D$4:$D$30)0))

Using only built-in functions, it gets very ugly, but it's possible. If
WSL, the named list of worksheets to process, were a horizontal array,

=SUMPRODUCT(
--(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4),
N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIRECT ("4:30"))-4,0,1,1))
)/SUMPRODUCT(
(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4)*
(N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIREC T("4:30"))-4,0,1,1))0)
)

Using only built-in functions, it gets very ugly

It's not all that ugly and it works! Very nice!

I was headed in the right direction but was still a long ways off.

Wonder how the OP made out?

Biff

"Harlan Grove" wrote in message
oups.com...
Biff wrote...
That said, if all the values were positive *and* there were only one
entry matching A1 in each other worksheets' A3:A30 range, then this
should work (at least it works in my tests).

Yes, it works under those conditions, where there is only a single
instance
(or none) per sheet. I was working on a solution in which there were
multiple instances per sheet. How could that be done? Nothing I've tried
works. The difficult part is the count.
...

There are times it's a PITA that Excel really isn't a 3D spreadsheet.

The simplest solution would be using Laurent Longre's MOREFUNC.XLL
add-in, specifically, using that add-in's THREED function.

=SUMPRODUCT(--(THREED(A:Z!$A$4:$A$30)=A4),THREED(A:Z!$D$4:$D$30) )
/SUMPRODUCT((THREED(A:Z!$A$4:$A$30)=A4)*(THREED(A:Z !$D$4:$D$30)0))

Using only built-in functions, it gets very ugly, but it's possible. If
WSL, the named list of worksheets to process, were a horizontal array,

=SUMPRODUCT(
--(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4),
N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIRECT ("4:30"))-4,0,1,1))
)/SUMPRODUCT(
(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4)*
(N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIREC T("4:30"))-4,0,1,1))0)
)

Guys... I feel like you're both pouring your guts out on this one, but
I'm definitely doing something wrong. When I entered Biff's function, I
get #NAME? returned...

Here's my setup:
Sheet1 --- (This is where I'd like to store the average of sheet2 &
sheet3 for each person on Sheet1...)
Bob
Jim
Joe
Joex
Liz
Sue
Tom

Sheet2...
Bob 93
Jim 26
Joe 34
Joe 100
Sue 34
Tom 82

Sheet3...
Bob 66
Joe 0
Joex 93
Liz 88
Sue 9
Tom 7

So in Sheet1, in B4, I inserted Biff's function and get #NAME
returned....


Biff Wrote:
Using only built-in functions, it gets very ugly

It's not all that ugly and it works! Very nice!

I was headed in the right direction but was still a long ways off.

Wonder how the OP made out?

Biff

"Harlan Grove" wrote in message
oups.com...
Biff wrote...
That said, if all the values were positive *and* there were only
one
entry matching A1 in each other worksheets' A3:A30 range, then this
should work (at least it works in my tests).

Yes, it works under those conditions, where there is only a single
instance
(or none) per sheet. I was working on a solution in which there were
multiple instances per sheet. How could that be done? Nothing I've
tried
works. The difficult part is the count.
...

There are times it's a PITA that Excel really isn't a 3D
spreadsheet.

The simplest solution would be using Laurent Longre's MOREFUNC.XLL
add-in, specifically, using that add-in's THREED function.

=SUMPRODUCT(--(THREED(A:Z!$A$4:$A$30)=A4),THREED(A:Z!$D$4:$D$30) )
/SUMPRODUCT((THREED(A:Z!$A$4:$A$30)=A4)*(THREED(A:Z !$D$4:$D$30)0))

Using only built-in functions, it gets very ugly, but it's possible.
If
WSL, the named list of worksheets to process, were a horizontal
array,

=SUMPRODUCT(

--(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4),

N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIRECT ("4:30"))-4,0,1,1))
)/SUMPRODUCT(

(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4)*

(N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIREC T("4:30"))-4,0,1,1))0)
)



--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile: http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=477377

Hi!

For what it's worth.......

Some of the "biggest hitters" there are have replied to this thread. If they
can't do it, it can't be done!

At least one of the formulas offered here should work so I'm guessing that
you're missing some minor detail.

If you want to send me a copy of your file I'll take a look. Just let me
know how to contact you.

Biff

"quailhunter"
wrote in message
...

Guys... I feel like you're both pouring your guts out on this one, but
I'm definitely doing something wrong. When I entered Biff's function, I
get #NAME? returned...

Here's my setup:
Sheet1 --- (This is where I'd like to store the average of sheet2 &
sheet3 for each person on Sheet1...)
Bob
Jim
Joe
Joex
Liz
Sue
Tom

Sheet2...
Bob 93
Jim 26
Joe 34
Joe 100
Sue 34
Tom 82

Sheet3...
Bob 66
Joe 0
Joex 93
Liz 88
Sue 9
Tom 7

So in Sheet1, in B4, I inserted Biff's function and get #NAME
returned....


Biff Wrote:
Using only built-in functions, it gets very ugly

It's not all that ugly and it works! Very nice!

I was headed in the right direction but was still a long ways off.

Wonder how the OP made out?

Biff

"Harlan Grove" wrote in message
oups.com...
Biff wrote...
That said, if all the values were positive *and* there were only
one
entry matching A1 in each other worksheets' A3:A30 range, then this
should work (at least it works in my tests).

Yes, it works under those conditions, where there is only a single
instance
(or none) per sheet. I was working on a solution in which there were
multiple instances per sheet. How could that be done? Nothing I've
tried
works. The difficult part is the count.
...

There are times it's a PITA that Excel really isn't a 3D
spreadsheet.

The simplest solution would be using Laurent Longre's MOREFUNC.XLL
add-in, specifically, using that add-in's THREED function.

=SUMPRODUCT(--(THREED(A:Z!$A$4:$A$30)=A4),THREED(A:Z!$D$4:$D$30) )
/SUMPRODUCT((THREED(A:Z!$A$4:$A$30)=A4)*(THREED(A:Z !$D$4:$D$30)0))

Using only built-in functions, it gets very ugly, but it's possible.
If
WSL, the named list of worksheets to process, were a horizontal
array,

=SUMPRODUCT(

--(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4),

N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIRECT ("4:30"))-4,0,1,1))
)/SUMPRODUCT(

(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4)*

(N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIREC T("4:30"))-4,0,1,1))0)
)



--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=477377

"quailhunter"
wrote...
....
Here's my setup:
Sheet1 --- (This is where I'd like to store the average of sheet2 &
sheet3 for each person on Sheet1...)
Bob
Jim
Joe
Joex
Liz
Sue
Tom

Sheet2...
Bob 93
Jim 26
Joe 34
Joe 100
Sue 34
Tom 82

Sheet3...
Bob 66
Joe 0
Joex 93
Liz 88
Sue 9
Tom 7

So in Sheet1, in B4, I inserted Biff's function and get #NAME
returned....
....

What's the *EXACT* formula you're trying?

If you're using something like

=SUMPRODUCT(
--(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4)
,
N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIRECT ("4:30"))-4,0,1,1))
)/SUMPRODUCT(
(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4)*
(N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIREC T("4:30"))-4,0,1,1))0)
)

did you create a list of worksheet names and define the name WSL referring
to that list? If not, that'd explain the #NAME? error. If I define WSL
referring to

={"Sheet2","Sheet3"}

and put your sample names in col A and sample numbers in col D beginning in
row 4, then with Bob in cell A4 in Sheet1 and the other names below it, I
enter the *array* formula above in cell B4 in Sheet1, then fill it down into
B5:B10, I get the following results in A4:B10.

Bob 79.5
Jim 26
Joe 67
Joex 93
Liz 88
Sue 21.5
Tom 44.5

Hi Harlan Grove,

When you get a chance. Can You explain step by step how this formula
works*?*


SUMPRODUCT((T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"), ROW(INDIREC
T("4:30"))-4,0,1,1))=A4)*(N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30 ),ROWINDIREC
T("4:30"))-4,0,1,1))0)


--
Fin Fang Foom
------------------------------------------------------------------------
Fin Fang Foom's Profile: http://www.excelforum.com/member.php...o&userid=36516
View this thread: http://www.excelforum.com/showthread...hreadid=477377