Trying to create an average calculation of values across sheets, but only from valid cells

Hi there,

Sorry a little complex I know but I'm trying to simplify a spreadsheet so it requires no manual input to automatically update averages.

Essentially I'm trying to monitor sales across months (each month is in a separate sheet). I want to break down sales into days of the week on average across the months.

I've worked out how to identify each weekday and then calculate the sum average of each weekday within the month:

{=AVERAGE(IF(WEEKDAY(C6:C38)=2,U6:U38))} for each weekday

the calculations for these are in the same cells on each sheet. I've worked out (I think) How to sum up and then average these values across the sheets

=Average(August:April!X7)

But what I cannot account for is that the months that have not passed return a "#DIV/0!" result. How do I account for that? I have tried "IF AUGUST:APRIL!X7>0, Average...etc" to no avail, and then I tried the "ISBLANK" statement, but they just return 0. Also, I need the calculation to stop at the current date rather than calculate all of them if the date has not yet elapsed.

Am I making this too complicated?

Anyone have any ideas?

Try using this array formula on your monthly sheets:

=IFERROR(1/(1/AVERAGE(IF(WEEKDAY(C6:C38)=2,U6:U38))),FALSE)

If months not passed have blank cell, then

=AVERAGE(August:April!X7)

should work

Thanks @John, but won't that add the empty numbers as a divisable number?

i.e: a series with

1,2,3,4,5,6,7,0,0,0,0,0

=(1+2+3+4+5+6+7+0+0+0+0+0)/12 rather than =(1+2+3+4+5+6+7)/7

?

Originally Posted by Root_

Try using this array formula on your monthly sheets:

=IFERROR(1/(1/AVERAGE(IF(WEEKDAY(C6:C38)=2,U6:U38))),FALSE)

Thanks @root I'm gonna give this a go later on, I solved this temporarily by fully populating all cells with '0' and putting all dates in, this then delivered a '0' instead of a "#Div/0" result and made the rest work.

Originally Posted by JohnTopley

If months not passed have blank cell, then

=AVERAGE(August:April!X7)

should work

Thanks @John, but won't that add the empty numbers as a divisable number?

i.e: a series with

1,2,3,4,5,6,7,0,0,0,0,0

=(1+2+3+4+5+6+7+0+0+0+0+0)/12 rather than =(1+2+3+4+5+6+7)/7

?

Change the zero values to blank and they will not be counted.

Sadly that would break the solution to get the weekly totals formula! I will try @roots solution with the blanks and see if I can get it to work with the Average.

I even tried 'averageif<a1:a5, "<>0") but that didn't seem to work (plus wouldnt be right if no sales were made on a day that has elapsed).

break the solution to get the weekly totals formula!

Why?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.