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

F

Frustrated in AL

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.
 
B

Bernard Liengme

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top