Question with regard to Average formula

[KingCreole]

Hi all,

I have a work sheet called 'HSO Front Sheet' and another one called
'HSO Data sheet'. On the front sheet it averages 5 cells from the data
sheet using the following forumla:

='HSO Data sheet'!D4+'HSO Data sheet'!F4+'HSO Data sheet'!H4+'HSO Data
sheet'!J4+'HSO Data sheet'!L4

If no data is contained within the data sheet the cell on the front
sheet returns: #DIV/0!

This then messes up an average of the averages sum i've got elsewhere


I've tried ='HSO Data sheet'!D4+'HSO Data sheet'!F4+'HSO Data
sheet'!H4+'HSO Data sheet'!J4+'HSO Data sheet'!L4,0

but this effects the actual result, ie if the 5 cells in the data sheet
all are set to 5 the average comes out at 4.2

Can anyone advise what forumla i should be using? Many thanks

 

[KingCreole]

sorry, i copy and pasted the wrong formula, the actual formula being
used is:

=AVERAGE('HSO Data sheet'!E4,'HSO Data sheet'!G4,'HSO Data
sheet'!I4,'HSO Data sheet'!K4,'HSO Data sheet'!M4)

 

[Harlan Grove]

KingCreole wrote...

sorry, i copy and pasted the wrong formula, the actual formula being
used is:

=AVERAGE('HSO Data sheet'!E4,'HSO Data sheet'!G4,'HSO Data
sheet'!I4,'HSO Data sheet'!K4,'HSO Data sheet'!M4)

Try

=IF(COUNT('HSO Data sheet'!E4,'HSO Data sheet'!G4,'HSO Data sheet'!I4,
'HSO Data sheet'!K4,'HSO Data sheet'!M4),AVERAGE('HSO Data sheet'!E4,
'HSO Data sheet'!G4,'HSO Data sheet'!I4,'HSO Data sheet'!K4,
'HSO Data sheet'!M4),0)

 

[KingCreole]

KingCreole wrote...

Try

=IF(COUNT('HSO Data sheet'!E4,'HSO Data sheet'!G4,'HSO Data sheet'!I4,
'HSO Data sheet'!K4,'HSO Data sheet'!M4),AVERAGE('HSO Data sheet'!E4,
'HSO Data sheet'!G4,'HSO Data sheet'!I4,'HSO Data sheet'!K4,
'HSO Data sheet'!M4),0)

superb. thanks very much!