#div0 and circular reference

[Liana]

I have a workbook with various weekly & monthly figures however I cannot seem
to get an overall total without getting #DIV0 & if I try IF function then I
get circular reference messages, I cannot sum pages from A:Z as there are
weekly summaries so the cells are not all corresponding - can anyone please
help

 

[Bernard Liengme]

We need to know the cells where the subtotals are on each sheet
What formula did you use?
We can help but more info is needed
best wishes

 

[Liana]

is it possible to send you the sheet, I am sure it is a simple task but I am
just not coming up with the correct function

 

[Liana]

I have weekly totals for cells L9:L27 giving a weekly average & the formulas
are -
IF(c9+e9+G9+I9+K9*24<1,"""",(B9+d9+F9+H9+J9/c9+e9+G9+I9+K9)
I have an overview for each week of the month followed by a sheet for each
day of the week x 5 weeks

then I have monthly totals so I can work out the monthly averages for each
person & then a final overall average the formula of which is
=AVERAGE('Overview wk 1'!L9,'Overview wk 2'!L9,'Overview wk3 '!L9,'Overview
wk4'!L9,'Overview wk5 '!L9)
this is giving me the #DIV0 error

Then I tried to get the overall average by doing =AVERAGE(B4:B22)

I hope I have explained this ok

 

[David Biddulph]

Presumably your divide by zero is because your 5 weekly L9 values are all
zero (or empty, or text values)?

I don't understand your L9 formula.
That obviously isn't what you've got in your spreadsheet, because it isn't a
valid formula and Excel wouldn't have accepterd it. The parentheses don't
match. Don't try to retype what you've got in your spreadsheet into here.
Copy from your formula bar and paste into the newsgroup.
What is the """" trying to achieve? It gives a text string of " as a
result. If you want an empty string as the result, use "".
I'm interested that you've multiplied K9 by 24 and then added it to
c9+e9+G9+I9. I don't know what's in the various input cells, but if the
various cells contain Excel times and you're trying to convert them to
hours, then shouldn't you be adding them all up then multiplying the result
by 24?
It's also interesting that you've divided J9 by C9, and then added the
result to B9+d9+F9+H9+e9+G9+I9+K9. I wonder whether you intended to add
B9+d9+F9+H9+J9 and then divide the result by c9+e9+G9+I9+K9 ?

It also isn't clear which formula is giving you a circular reference.

It seems that you need to do some more debugging before you come back to us
for further help.

 

[Liana]

My main problem now is only 1 formula as i have the monthly totals per person
pulled through(albeit some people have a 0% perhaps due to absence or holiday
but now I am trying to average the totals to get an overall team average & it
will not pull through - all I put was average(B4:B22) but it comes up as 0%
is this because I am averagging an average ? in fact twice as I already
worked out monthly average from weekly averages
Help !

 

[Liana]

I don't suppose you would be willing to have a look at what I have done as it
is hard to explain all my pages but they are clear to see on the actual
worksheet

 

[David Biddulph]

I tried to reply to you but your "e-mail address" didn't work.

 

[Liana]

email is= (e-mail address removed)
Thanks

I tried to reply to you but your "e-mail address" didn't work.