| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding single cell in multiple worksheet then averaging
I am stumped. My situation is I want to total let's say "A1" on multiple
worksheets but then want to average the total out by the number of worksheets. Is there a formula that would address this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding single cell in multiple worksheet then averaging
One way
Put a worksheet call 'Start' as you first worksheets and another call 'End' as the last. You can hide them if you want. Then the formula below will do what you want =AVERAGE(start:End!A1) Mike "maril" wrote: I am stumped. My situation is I want to total let's say "A1" on multiple worksheets but then want to average the total out by the number of worksheets. Is there a formula that would address this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding single cell in multiple worksheet then averaging
This helped some but I get a #Div/0 error when cells have "0" in it. I've
tried this formula but it seems it throws off the average by 1, =average(startsheet!:endsheet!,a1,"0"). Can you tell me what I need to fix? "Mike H" wrote: One way Put a worksheet call 'Start' as you first worksheets and another call 'End' as the last. You can hide them if you want. Then the formula below will do what you want =AVERAGE(start:End!A1) Mike "maril" wrote: I am stumped. My situation is I want to total let's say "A1" on multiple worksheets but then want to average the total out by the number of worksheets. Is there a formula that would address this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding single cell in multiple worksheet then averaging
Maril,
Try this =AVERAGE(startsheet:endsheet!A1) This will averge the contents of A1 on every sheet between 'startsheet' & 'endsheet' and Excel is clever enough to ignore blank cells. A zero in a cell will be used in the calculation for average but wont give rise to a #Div/0 error. If your getting those then it's something else. Mike "maril" wrote: This helped some but I get a #Div/0 error when cells have "0" in it. I've tried this formula but it seems it throws off the average by 1, =average(startsheet!:endsheet!,a1,"0"). Can you tell me what I need to fix? "Mike H" wrote: One way Put a worksheet call 'Start' as you first worksheets and another call 'End' as the last. You can hide them if you want. Then the formula below will do what you want =AVERAGE(start:End!A1) Mike "maril" wrote: I am stumped. My situation is I want to total let's say "A1" on multiple worksheets but then want to average the total out by the number of worksheets. Is there a formula that would address this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding single cell in multiple worksheet then averaging
Could it possibly be due to the fact that the cells have formulas in them?
If that's what's causing the problem, do you know how to fix it? "Mike H" wrote: Maril, Try this =AVERAGE(startsheet:endsheet!A1) This will averge the contents of A1 on every sheet between 'startsheet' & 'endsheet' and Excel is clever enough to ignore blank cells. A zero in a cell will be used in the calculation for average but wont give rise to a #Div/0 error. If your getting those then it's something else. Mike "maril" wrote: This helped some but I get a #Div/0 error when cells have "0" in it. I've tried this formula but it seems it throws off the average by 1, =average(startsheet!:endsheet!,a1,"0"). Can you tell me what I need to fix? "Mike H" wrote: One way Put a worksheet call 'Start' as you first worksheets and another call 'End' as the last. You can hide them if you want. Then the formula below will do what you want =AVERAGE(start:End!A1) Mike "maril" wrote: I am stumped. My situation is I want to total let's say "A1" on multiple worksheets but then want to average the total out by the number of worksheets. Is there a formula that would address this? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding single cell in multiple worksheet then averaging
Took some thinking but I got the answer:
=AVERAGE(startsheet:endsheet!A1,"0",startshee:ends heet!A1) "Mike H" wrote: Maril, Try this =AVERAGE(startsheet:endsheet!A1) This will averge the contents of A1 on every sheet between 'startsheet' & 'endsheet' and Excel is clever enough to ignore blank cells. A zero in a cell will be used in the calculation for average but wont give rise to a #Div/0 error. If your getting those then it's something else. Mike "maril" wrote: This helped some but I get a #Div/0 error when cells have "0" in it. I've tried this formula but it seems it throws off the average by 1, =average(startsheet!:endsheet!,a1,"0"). Can you tell me what I need to fix? "Mike H" wrote: One way Put a worksheet call 'Start' as you first worksheets and another call 'End' as the last. You can hide them if you want. Then the formula below will do what you want =AVERAGE(start:End!A1) Mike "maril" wrote: I am stumped. My situation is I want to total let's say "A1" on multiple worksheets but then want to average the total out by the number of worksheets. Is there a formula that would address this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| adding multiple checkboxes in a single cell | New Users to Excel | |||
| adding multiple checkboxes in a single cell | New Users to Excel | |||
| Adding a single function onto multiple cells that already have Num | Excel Discussion (Misc queries) | |||
| Adding up data in multiple cells for a single criteria. | Excel Worksheet Functions | |||
| Converting multiple worksheet to a single page worksheet | Excel Discussion (Misc queries) | |||
Hybrid Mode
