AVERAGE #DIV/0 empty cell problem across different W/Sheets to summary sheet

[Chalkie]

Hi

This is my first post. I am not great with excel so please be patient with me.

I have a summary sheet from where I have the information from 12 separate sheets (months) feeding into. The problem I am having is I want to calulate the averages from the other sheets. If no data is entered (as that month has not yet passed) there will obv be nothing to summarise so it is kicking back #DIV/0. The other sheets (months) are averaged fields I am calulating from.

The summary sheet I would like the average for the year so far. I setting the spreadsheet up only, multiple people will use this but are worse than me at excel so no formulas to be added later.

I have looked at IF and IFERROR but they don't seem to work as I already am calulating averages in these cells.

If anyone understand what I'm, saying please help!

[Olly]

it will be easiest to help if you can attach an example workbook, showing the worksheet / data structure you are working with

[Moo the Dog]

Since I don't have an example to go by... I would think that making each of the AVERAGE formulas on the individual pages into IFERROR statements would work? Something like this:

Say you are averaging cells A1:A10 on each sheet (Sheet1:Sheet12), and storing that average in cell B1. Use =IFERROR(AVERAGE(A1:A10),"") in cell B1 on each sheet.

Then, on your summary sheet, you can use: =AVERAGE(Sheet1:Sheet12!B1).

Since the B1 cells on all sheets will either contain a number, or a blank, the average formula should now work correctly.

Good luck,
Moo

[Olly]

Moo the Dog, that would result in an average of an average - which would be inherently inaccurate...

[Chalkie]

Thanks for your repsonses so quick

My sheets calulate fine, no issues there. It is just when the months without data are averaged in - this where the problems are.

So (Network wont allow me to attach)

January Sheet Totals C57:F57 are averaged into C59. This repeats 3 times on the sheet again at K57:N57 to K59 then S57:V57 to S59.
All the sheets are named by month and are set out exactly as above.

Summary sheet
I have the 3 sets of data from each sheet displayed with =January!C59 etc (Cells F10:F12)
In cell F8 I would like the average for cells F10:F12 to be displayed. This would be the same for each month.

I also have a an average for the year in K5 as =AVERAGE(F8,F15,F22,F29,F36,F43,F50,F57,F64,F71,F78,F85)


Is this better info?

Thansk again.

[Moo the Dog]

Moo the Dog, that would result in an average of an average - which would be inherently inaccurate...

How right you are. Too early in the morning still, haven't had enough coffee! =)

- Moo

[Olly]

Still sounds like you're ending up with averages of averages, to me...

However. To ignore the cells with no numbers in:

Please Login or Register  to view this content.

Adapt to suit your workbook.

[Chalkie]

image.jpg

Hope you can see this first image

[Tony Valko]

AVERAGE will ignore empty cells unless EVERY cell referenced is empty then you'll get the #DIV/0! error.

The suggestion by Moo in post #3 is a suitable work-around.

If this isn't working tell us EXACTLY which cells are being referenced. Do any of these cells already contain #DIV/0! errors?

[Chalkie]

image.jpg

I know my main problem is the average for the year. Sorry

[Chalkie]

thank you for all of your help - very much appreciated.

Thank you I have sorted it (hopefully - might be back ha)

I have used =AVERAGE(F10:F12,F17:F19,F24:F26,F31:F33,F38:F40,F45:F47,F52:F54,F59:F61,F66:F68) on summary sheet to get my average for the year

[Tony Valko]

Good deal. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.