Apache OpenOffice Community Forum

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

E.G. =SUM(A1:A5)/COUNTIF(A1:A5;"<>0")

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

You lost me here. What would you want instead? Some calculation involving a number not zero, even if the value in that cell is zero? You ARE doing arithmetic, and so must give alternatives if there are to be alternatives. That involves thinking ahead, and programming carefully, using conditional statements. You must first decide what the alternative calculation is to be. "Mess up the result" is not very informative.

David.

I think I understand. I had lots of trouble with this myself, until I did a little digging inside the data.

You can dig yourself, using the output from the CELL() function. For example. CELL A1 is "empty." In Cell B1, you enter: =A1. Now, using the CELL function, let's see what we have:

CELL("contents";A1) tells us the contents of A1 is 0.
CELL("contents";B1) tells us the contents of B1 is 0.
CELL("type";A1) tells us the type of data in A1 is blank.
CELL("type";B1) tells us the type of data in B1 is a value.

That's the "problem." When you use "=A1", you were writing a formula, and that formula said to get the "contents" of A1 and put it in B1. The contents of A1 was not a blank, it was a 0, with TYPE blank. the = function doesn't get type.

The easiest way to fix this is to use something like the following in B1.

=IF(ISBLANK(A1);"";A1)

If you do that, you'll see:

CELL("contents";B1) says the contents is blank
CELL("type";B1) says the type is a label.

Okay, it isn't perfect - rather than returning a TYPE blank, you get a value of blank, and a type=label. However, for my use, it was better than the alternative.

Remember, ISBLANK only works on TRULY blank cells (not in cells that contain formulas). For example, check to see if ISBLANK(B1) returns true when A1 is blank. No, it doesn't. I don't think there's a way to generate a blank in a formula. If there was, rather than returning "" above, you could return blank and be really happy.

Duhhh wrote:
=IF(ISBLANK(A1);"";A1)

Okay, it isn't perfect

ABSOLUTELY PERFECT I'd call it! I just spent an hour or two trying to work out how to get rid of the 0!! Faffing about with conditional formatting and all that fun stuff

Thanks buddy! I was over the moon when I saw it worked!

Glad you got it sorted.

Please add [Solved] to the the start of the title of your first post (use the edit button) if your question has been answered.