Average of Averages across multiple tabs with #DIV/0! Error

[TomYC]

I am attempting to get what amounts to an 'average of averages' across 12 (monthly) worksheets. But some of the averages are (as yet) errors, for there is no data (yet).
I begin here with some partial formula, and you can see where it's going:
=(IF(ISERROR(JAN!B44),0,JAN!B44)+IF(ISERROR(FEB!B44),0,FEB!B44)+IF(ISERROR(MAR!B44),0,MAR!B44)+...IF(ISERROR(SEP!B44),0,SEP!B44)+IF(ISERROR(OCT!B44),0,OCT!B44)).

Clearly this formula will run into the length of characters limitation if I keep going! But in the example above, the value in OCT!B44 is currently an error value (#DIV/0!), which is why the error trapping above. I should say that the formulae that give each monthly average are themselves so long as to prevent error trapping, otherwise I would fix the problem on each worksheet.

I think what I'm looking for is something like SUMIF, where I can specify a range across 12 worksheets--might that be possible?

 

[SkipVought]

Hi,

Excel is a wonderful tool. It is easy to use, once you get the hang of it. It is ALSO easy to make HUGE mistakes. One of the HUGE mistakes that users often make is chopping their data up into sheets.

It seems that you have fallen into this trap.

My advice to you is to assemble ALL your data into ONE SHEET, adding a column to indicate the DATE (yes, make it a REAL date, not just a MONTH!!!). You should only have data in this table that exists, ie not future stuff that does not.

From one table you can 1) produce monthly reports (on separate tabs if you like but why bother with separate tabs) 2) summarize your data as needed with simple functions or a PivotTable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip is spot on here... I can't stress enough only make a new sheet when you really know WHY you need a new sheet. You have to think about data in ways that you want to use it later, not just what looks good at the moment.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[lionelhill]

Not wishing to hijack this thread, but wishing to raise something very closely related:

Has anyone any suggestions about good introductions to using dates in pivot tables with a view to getting monthly summaries from fully dated entries in a table? This is still something with which I struggle (shame!).

 

[SkipVought]

lionelhill,

Please start a new thread and I will comment.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Thanks for the commentary on data location.
And of course in an ideal world all data would indeed be in one place. However when you consider the division of labor in large organizations, not to mention financial accounting historical practice, it is not unusual to see data pulled monthly and dumped into monthly buckets, aka worksheets. Would I design it this way? No--because I know how to filter it selectively, by date range. Such is life.

 

[SkipVought]

...and if it were me, I would STILL consolidate the data, as this is the only way that one can address the current issue and any other data analysis/data reporting issue.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

If you can do what Skip has suggested, it would be the ideal way to deal with it.

I do understand the trials and tribulations of large corporations. I work for one, and sometimes there isn't the time to stop and think that you should be draining the swamp. Besides, if your manager wants things one way, there is no arguing that the ideal way to do things is different.

So, while this is not ideal, put error trapping in Div/0 error. Where you would say, =if(iserror(my_function),0,my_function)

Then you could sum up everything as =Sum(Janec!B44)

If you can afford the time, please follow what Skip has suggested.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[TomYC]

I appreciate the efforts here!
Perhaps there is a way to dynamically combine, say, all the non-blank rows (below row 1) in each of 12 worksheets into a single worksheet? I ask that because I am, in fact dealing with a file into which someone else dumps monthly data reports, into one of twelve monthly "bins." Asking someone to "concatenate" by hand is another source of errors.

 

[SkipVought]

Do one. Record a macro of the process of appending one sheet's worth of data.

Post back with your recorded macro in forum707 for help customizing this process.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}