Need Calc to always ignore cells containing zero...
Posted: Tue Jun 10, 2008 3:23 am
Hi. I hope someone can help. This issue has been plaguing everything I'm trying to do with this particular spreadsheet!
I have set up a spreadsheet format which stays the same, but references a second sheet into which varying data can be copied and pasted. So whatever gets pasted into the second sheet then turns up in particular cells in the first sheet.
I have a particular group of five cells arranged in a column. Into these cells will appear numbers, such as 23.57. Sometimes one or two of these cells will be empty if there is no corresponding data in the second sheet. I am then applying various simple formulas to these five cells and having the answer appear in a "total' cell at the bottom.
The problem I have is that Calc always seems to treat these empty cells as zero, rather than ignoring them in calculations. Why this isn't a default is beyond me. For instance if I want to find the average of these five cells, it will treat a zero as a number and divide by five, rather than by four (if there is one zero out of the five data values). I have managed to write a formula that performs the averaging correctly, but what a waste of time that was! (Who would want to include zero in an average calculation?) {=AVERAGE(IF(C4:C8=0;"";C4:C8))}
It does the same thing with other calculations too - for instance
=5*(A10-A4)+4*(A10-A5)+3*(A10-A6)+2*(A10-A7)+(A10-A8)
In the above formula, if A6 happened to be empty it would treat it as zero and mess up the result. I would like the formula to filter out cells that are empty and just use cells that have data.
Is there a way to force Calc to treat all empty cells as empty, or as text (say using "")?
Cris
I have set up a spreadsheet format which stays the same, but references a second sheet into which varying data can be copied and pasted. So whatever gets pasted into the second sheet then turns up in particular cells in the first sheet.
I have a particular group of five cells arranged in a column. Into these cells will appear numbers, such as 23.57. Sometimes one or two of these cells will be empty if there is no corresponding data in the second sheet. I am then applying various simple formulas to these five cells and having the answer appear in a "total' cell at the bottom.
The problem I have is that Calc always seems to treat these empty cells as zero, rather than ignoring them in calculations. Why this isn't a default is beyond me. For instance if I want to find the average of these five cells, it will treat a zero as a number and divide by five, rather than by four (if there is one zero out of the five data values). I have managed to write a formula that performs the averaging correctly, but what a waste of time that was! (Who would want to include zero in an average calculation?) {=AVERAGE(IF(C4:C8=0;"";C4:C8))}
It does the same thing with other calculations too - for instance
=5*(A10-A4)+4*(A10-A5)+3*(A10-A6)+2*(A10-A7)+(A10-A8)
In the above formula, if A6 happened to be empty it would treat it as zero and mess up the result. I would like the formula to filter out cells that are empty and just use cells that have data.
Is there a way to force Calc to treat all empty cells as empty, or as text (say using "")?
Cris