Forum Discussion
how to get formulas to caculate across tabs?
I have formula in Totals Tab for all my tabs. How do I get it to calculate the percentages when there are no percentages on the other tabs without messing up the total. When the percentage on other tabs are 0 it calculates that in the math throwing off the actual percentage on the totals page.
2 Replies
When the percentage on other tabs are 0 it calculates that in the math throwing off the actual percentage on the totals page.
First of all, an average of averages isn't a true average of the underlying annual data. It's not legitimate math. To take an extreme example, presume you had 100 clients in one of the years, and the average was 10%. 1 client in each of the other four years, with each average being 100%. The five year "average" of those averages would be 82%, but that would clearly not be the real average for those 104 clients, the true average being 13.46%. So even if there were numbers in those cells, your results would not be an accurate reflection of the five years of data.
Secondly, what are your plans for the years 2021 and 2022 (presumably 2025 is going to take care of itself). If you're going to be filling in some numbers of 2021 and 2022, then at least you won't have zeroes in there.
But another possibility is that you should consider redesigning the whole workbook. (a) There is no need to have tons of blank rows. (b) You've spent much too much time on what are essentially input sheets on making things look pretty, and that sort of thing (though tempting for sure) can interfere with clean processing in the long run; (c) You might find that combining all the data into a single table, since you've got the years as two of the data elements anyway (start date, end date), you might well find that Excel excels at parsing single databases into reports on subsets (by year, by quarter, whatever) AND does a more seamless job of pulling together comprehensive totals and averages that way. When we used to keep records on paper, it made lots of sense to divide years up; in Excel it makes sense only for the human viewer, but in fact (as you're experiencing) it gets in the way of Excel, making it harder to come up with multi-year averages and other summaries.
