Question with regard to Average formula

K

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
:p

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
 
K

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)
 
H

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)
Click to expand...

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)
 
K

KingCreole

Harlan said:
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)
Click to expand...

superb. thanks very much! :)
 
Top