Averaging from multiple worksheets within a workbook

W

walkerma

New Member
Joined
May 13, 2008
Messages
21
I'm trying to do some averages for a year to date "cover" sheet. Worksheet 2 is Jan, Worksheet 3 is Feb, etc..through December. Worksheet 1 is the year to date averages. My data is a formula on each worksheet in cell B22, I thought I had the function written correctly however Excel won't take it:

=AVERAGE(IF(January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22,November!B22,December!B22<>0, January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22,November!B22,December!B22,""))

There are blanks,of course, until each month is filled in. Could it be because the number that is in B22 on each worksheet is the result of a formula instead of typed number?? I've spent hours on this...Any ideas out there??
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Try...

=AVERAGE(IF(N(INDIRECT(TEXT(DATE(2008,ROW(INDIRECT("1:12")),1),"mmmm")&"!B22"))>0,N(INDIRECT(TEXT(DATE(2008,ROW(INDIRECT("1:12")),1),"mmmm")&"!B22"))))

or

=AVERAGE(IF(N(INDIRECT("'"&$A$2:$A$13&"'!B22"))>0,N(INDIRECT("'"&$A$2:$A$13&"'!B22"))))

...where A2:A13 contain the sheet names January, February, March, etc. Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Upvote 0
If future months do contain a blank in B22 (not 0), you can do it like so:

=SUM(January:December!$B$22)/COUNTA(January:December!$B$22)

However, this takes the workbook order, so you can't have any in-between sheets.
 
Upvote 0
Thank you thank you for the help!!!

I copied your second formula and edited A2:A13 to contain sheet names so it looks like this:

=AVERAGE(IF(N(INDIRECT("'"&January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22, November!B22,December!B22&"'!B22"))>0,N(INDIRECT("'"&$January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22, November!B22,December!B22&"'!B22"))))

Still getting an error...did I edit the wrong part?

Mary
 
Upvote 0
iliace,

that's almost there!! only problem is it is dividing by 12 (months) and I want it to divide only by the cells that are not blank or zero.

I have Jan-April completed and I know the average I want on the year to date cover sheet is 68.25 (273/ 4 months) The result of your formula is 22.78 (273/12 months)

Can you figure out how to get it to ignore the sheets that still have a zero?

I really really apprecite your help!!!
 
Upvote 0
If you're going to use the second formula, which is more efficient than the first one, then you'll need to enter the sheet names in a range of cells. In the example, the sheet names are entered in A2:A13 to which the formula refers. So the sheet names are not specified in the formula itself.
 
Upvote 0
PS....I can't take the zeros out of the other sheets because the number appears as a result of a formula so are currently defaulting to zero. To get a blank I'd have to remove my formula and I can't do that
 
Upvote 0
Domenic,

Yes, that's my intention! So, do I need to list each sheet name as I have or is there a way to put in a range of sheets in place of A2:A13?

I'm sorry I'm not quite catching on.....
 
Upvote 0
walkerma said:
Domenic,

Yes, that's my intention! So, do I need to list each sheet name as I have or is there a way to put in a range of sheets in place of A2:A13?
Click to expand...

Enter January in A2, February in A3, March in A4, and so on. The formula itself remains as is, unless you choose to enter the sheet names in another range of cells, in which case only the reference to A2:A13 would change. Alternatively, you could dispense with having to list your sheet names in a range of cells by replacing...

A2:A13

with

{"January","February","March", ..... "December"}

I'm sorry I'm not quite catching on.....
Click to expand...

No problem...
 
Upvote 0
I thought I understood excel fairly well, but I fear I am getting dumber by the minute....

I do understand what you are saying... I need to list the months in my worksheet instead of referencing sheet names, but I can't change any of the cells I have already created so can I list those months on another blank sheet and make the formula work?

I feel you are my hero in the making if I could just completely understand this concept you are explaining!! We are so close!! :) Stupid users are such a pain, aren't they??
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Count - specific consecutive order
Replies
6
Views
472
Peter_SSs
Peter_SSs
S
  • Solved
auto Lock cells after due date
Replies
3
Views
361
Sophia2
S
I
  • Solved
Advice to edit code so deleting by mistke doesnt happen
Replies
7
Views
423
ipbr21054
I
C
  • Question
Sorting Excel Dates in Chronological Order
Replies
6
Views
443
alansidman
A
P
  • Solved
Distributed Row Results
2
Replies
19
Views
888
Pestomania
P

Forum statistics

Threads
1,214,414
Messages
6,119,373
Members
448,888
Latest member
Arle8907

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top