Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to auto count data in an empty cell to be 0. Blank cell=0 | Excel Discussion (Misc queries) | |||
format a cell with a formula so an empty reference cell shows blan | Excel Discussion (Misc queries) | |||
why a reference to an empty cell is not considered empty | Excel Discussion (Misc queries) | |||
Adding numbers in one cell and showing total in seperate cell | Excel Discussion (Misc queries) | |||
How do I leave formula cell blank if 2nd reference cell is empty? | Excel Discussion (Misc queries) |