Multiple Issues dealing with leaving cells blank as well as Sum, Average, at the same time

Ok, I will try to explain this to the best of my Excel abilities.

First off, the sheet must be able to print out as a blank because some people like filling out things by hand (I know, I know, You just can't change them, believe me I'm always trying). So when you leave formulas in you get the infamous divide by zero error's or the #Value errors.

So here's what I got so far.
I have cells B1 through G1 with the following;
"B1Safety" "C1Directs" "D1Indirects" "E1JV Only Total" "F1Subs" "G1JV & Subs Total"

I have cells A2 through A9 with the following;
A2 Monday
A3 Tuesday
A4 Wednesday
A5 Thursday
A6 Friday
A7 Saturday
A8 Sunday
A9 Average per week

One of the formula's are in B9 through G9 which is the following;
=IFERROR(AVERAGE(B2:B8),"") which works fine for averaging the column when there is data inputted as well as only averaging the cells with data entered into them. Example if only 5 days had data inputted then it would average only the 5 days which had data in them and not treat the other 2 days as "0's" which of course would drop the average.

C2:C8, D2:D8, & F2:F8 are all as the example listed above. I have no qualms with any of these.

Ok, now here in-lies the deli-ma, E2 has the following formula;
=IFERROR(SUM(B2:D2),"") Which works fine for summing the the 3 cells with data inputted or left blank. Now I have the same formula in E9 as B9-D9, the only problem is that it Averages all seven days (E2:E8), so if there was no data inputted on a day then it sees it as a zero which greatly reduces the average.

This is also the same for G2 and it's formula is as follows;
=IFERROR(SUM(E2:F2),"") Which works fine for summing the the 3 cells with data inputted or left blank. Now I have the same formula in G9 as B9-D9, & F9, the only problem is that it Averages all seven days (G2:G8), so if there was no data inputted on a day then it sees it as a zero which greatly reduces the average.

Again Cells B9, C9, D9, & F9 work fine. How do I get E9 & G9 to only Average the cells with data inputted into them?

Oh, I almost forgot, I also had to format cells E2:E8 & G2:G8 with the "0;-0;" in order for the zeros not to show up in the cells.

I sure hope I explained the problem well enough for someone to help me.

Thanks,
Jay

Hi there

Welcome to the forum.

A little difficult to follow... it may have been easier to upload an example. To do this, use Go Advanced below then Manage Attachments.

One answer would be to use COUNTIF and SUMIF to calculate the average. I'm not sure if you want to include 0 when entered. If not it is:

Please Login or Register  to view this content.

If you only want to exclude the "" then:

Please Login or Register  to view this content.

Cheers, Rob.

JVIC Weekly Project Safety Report 105194.xlsx

I hope I attached this correctly.
The cell(s) in questions are H41 & K41. All the sheet tabs are for the most part the same except for the first one, which is where the beginning data is inputted.

If you can tweak the cell(s), I will look to see how you solve this.

Thanks very much,
Jay

Hi Jay

Sorry for the delayed response.

The cells should have the following formulae in them:
H41:

Please Login or Register  to view this content.

K41:

Please Login or Register  to view this content.

Hope this helps.

Best regards, Rob.

Cheers, Rob.