Average and If function

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

Hi there,

I'm looking to find the average of 9 cells. The cells are
in 9 individual workbooks, if one of the cells hasn't got
any data the cell that I am using to find the average
displays "#DIV/0!". I believe this is because the formula
is trying to count the zeroed cells and only once all
cells are entered does it give a value.

Is there a way of ignoring the zeroed cells and only
averaging the cells that have data in them?

I think its something to do with the IF and Average
function but I am unsure as to how to construct it so it
works and becomes an array formula.

Many Thanks

Si
 
Hi Simon,

I'm not so sure i understood your problem coorectly
1. If you calculate the average for 9 cells and at least one cell ist
filled (with a number) avergae works just fine. Empty cells are just
ignored. Only if all 9 cells are blank the "#DIV/0" error is displayed
2. '0' are included in the average function.

Frank
 
Hi Frank,

Righ this is my formula:

=AVERAGE('[Team A Success Tracker.xls]Summary'!
$D$15,'[Team B Success Tracker.xls]Summary'!$D$15,'[Team C
Success Tracker.xls]Summary'!$D$15,'[Team D Success
Tracker.xls]Summary'!$D$15,'[Team E Success Tracker.xls]
Summary'!$D$15,'[Team F Success Tracker.xls]Summary'!$D$15)

The Cells D15 in each workbook is the average of another
set of cells. If these other cells are not filled in, then
cell D15 shows #DIV/0! and the is represented in the
master average.

How do I get it so it doesn't do this?

The reason its like this is for example if someone doesn't
fill in the sheet we still need to know an average for the
other teams??

Many Thanks

Si
 
Hi Simon

now it becomes clear :-) so it is possible that one or more Team
averages already get the 'DIV/0' error. I would suggest to change the
average formula on the team workbooks so that they check if they have
values to calculate. e.g.,
=IF(COUNTA(Cell1,Cell2,...)>0,AVERAGE(Cell1, Cell2,...),"")

In this case the formulare returns an empty cell if all cells to
calculate are blank.
HTH
Frank
 
Hi Frank,

Firstly thank you very much for your help.

I'm not too sure if the formula works or maybe I'm being
stupid and not entering it correctly???? The cell will
display #DIV/0! unless all cells that are referenced have
data in.

What I need is an "average" of the averages across the 5
teams??? Is it possible?

The master cell:
=IF(COUNTA('[Team C Success Tracker.xls]Summary'!
$D$15,'[Team D Success Tracker.xls]Summary'!$D$15,'[Team E
Success Tracker.xls]Summary'!$D$15)>0,AVERAGE('[Team C
Success Tracker.xls]Summary'!$D$15,'[Team D Success
Tracker.xls]Summary'!$D$15,'[Team E Success Tracker.xls]
Summary'!$D$15))

And in the individual workbooks the average cell for that
team:
=AVERAGE(Agent1!D15, Agent2!D15, Agent3!D15, Agent4!D15,
Agent5!D15, Agent6!D15, Agent7!D15, Agent8!D15, Agent9!D15)

Many Thanks

Si
 
Hi Simon,

you have to enter the formula in all of your sub workbooks (that is in
each average calculation on team basis). After this also the summary
workbook should work
Frank
 
Frank you are the man,


Thank you, its all fully working and giving me averages!!!

Thank you again

Si
 
Frank you are the man,


Thank you, its all fully working and giving me averages!!!

Thank you again

Si
 
Back
Top