Average cells from different sheets with zeros

[draacor]

Ok i have scoured this site looking for a way to do this and i cant so im opening it up for discussion.

I have 6 excel worksheets in a single workbook. The first 5 worksheets has data that pulls from other workbooks. I am trying to compile data across other workbooks into one single workbook. The 6th worksheet summarizes the previous five. The problem im running into is that each of the 5 worksheets represents a different month:

June
July
August
September
October

Obviously i have data for june july and august, but so far all of september and october are zeros. But the sixth worksheet that averages these months together doesnt know that, so it still factors september and october into the average. How do i average data across multiple worksheets but not factor in any zero or blank values?

 

[SkipVought]

Hi,

I run into the same challenge from time to time -- similar data in different places.

Let me give you a tip, based on years of experience. Spend your time and effort combining your similar data into ONE TABLE.

I would use a query. But there's no good way to do it "automatically" using native Excel features. You might want to use a VBA approch and ask for help in Forum707.




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]