| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Finding the average across multiple sheets
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 |
|
#2
|
|||
|
|||
|
Finding the average across multiple sheets
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 |
|
#3
|
|||
|
|||
|
Finding the average across multiple sheets
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 |
|
#4
|
|||
|
|||
|
Finding the average across multiple sheets
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 |
|
#5
|
|||
|
|||
|
Finding the average across multiple sheets
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)) |
|
#6
|
|||
|
|||
|
Finding the average across multiple sheets
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)) |
|
#7
|
|||
|
|||
|
Finding the average across multiple sheets
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? |
|
#8
|
|||
|
|||
|
Finding the average across multiple sheets
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). |
|
#9
|
|||
|
|||
|
Finding the average across multiple sheets
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). |
|
#10
|
|||
|
|||
|
Finding the average across multiple sheets
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 |
|
#11
|
|||
|
|||
|
Finding the average across multiple sheets
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating average scores from multiple sheets' information | Excel Worksheet Functions | |||
| selecting multiple sheets | Excel Worksheet Functions | |||
| Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
| Weighted Avg - using values from multiple sheets | Excel Worksheet Functions | |||
| Multiple sheets selected | Excel Discussion (Misc queries) | |||
Hybrid Mode
