showing an empty cell instead of a zero in a formulated cell.
 

I have a spreadsheet (F) where each functional cell has a formula that adds
the coresponding cell information from 13 other worksheets (F1, F2, . . .
F13). These are 13 different sources for counts of information received in
15 minute intervals over a 13 week period.

There are times when none of the 13 "source" worksheets has any data. I
would like this to appear as a blank cell on the main spreadsheet (F). The
reason being, I am trying to take an averege on each row of cells with actual
data. If the zeros are left in then the average is always based on 13.

If I have 7 of my 13 source cells showing zero, I would only want to divide
the sum by the 6, which would give me a larger dividend.

Here is an example of my first cell. What can I add to the formula to have
the F cell in question show an empty cell if no data were received in that
block of time?

='F1'!B2+'F2'!B2+'F3'!B2+'F4'!B2+'F5'!B2+'F6'!B2+' F7'!B2+'F8'!B2+'F9'!B2+'F10'!B2+'F11'!B2+'F12'!B2+ 'F13'!B2

I appreciate any assistance that you can give me.

showing an empty cell instead of a zero in a formulated cell.
 

Change
='F1'!B2+'F2'!B2+'F3'!B2+'F4'!B2+'F5'!B2+'F6'!B2+' F7'!B2+'F8'!B2+'F9'!B2+'F10'!B2+'F11'!B2+'F12'!B2+ 'F13'!B2
To
=SUM('F1:F13'!B2)and test
Then test
=IF(=COUNT('F1:F13'!B2)0,SUM('F1:F13'!B2),"")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Frustrated in AL" wrote in
message ...
I have a spreadsheet (F) where each functional cell has a formula that adds
the coresponding cell information from 13 other worksheets (F1, F2, . . .
F13). These are 13 different sources for counts of information received
in
15 minute intervals over a 13 week period.

There are times when none of the 13 "source" worksheets has any data. I
would like this to appear as a blank cell on the main spreadsheet (F).
The
reason being, I am trying to take an averege on each row of cells with
actual
data. If the zeros are left in then the average is always based on 13.

If I have 7 of my 13 source cells showing zero, I would only want to
divide
the sum by the 6, which would give me a larger dividend.

Here is an example of my first cell. What can I add to the formula to
have
the F cell in question show an empty cell if no data were received in that
block of time?

='F1'!B2+'F2'!B2+'F3'!B2+'F4'!B2+'F5'!B2+'F6'!B2+' F7'!B2+'F8'!B2+'F9'!B2+'F10'!B2+'F11'!B2+'F12'!B2+ 'F13'!B2

I appreciate any assistance that you can give me.