Need Calc to always ignore cells containing zero...

Discuss the spreadsheet application

Need Calc to always ignore cells containing zero...

Postby Crispassion » 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
OOo 2.4.X on Ms Windows XP
Crispassion
 
Posts: 1
Joined: Tue Jun 10, 2008 3:01 am

Re: Need Calc to always ignore cells containing zero...

Postby Dave » Tue Jun 10, 2008 4:37 am

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.
Dave
 
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Need Calc to always ignore cells containing zero...

Postby Duhhh » Tue Jun 10, 2008 9:02 pm

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.
OOo 3.3.X on Ms Windows XP
Duhhh
 
Posts: 49
Joined: Tue Jun 03, 2008 6:10 pm
Location: USA

Re: Need Calc to always ignore cells containing zero...

Postby Villeroy » Tue Jun 10, 2008 9:26 pm

Code: Select all   Expand viewCollapse view
=IF(COUNT(A4:A8;A10)=6;5*(A10-A4)+4*(A10-A5)+3*(A10-A6)+2*(A10-A7)+(A10-A8);NA())
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, latest OpenOffice & LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 19561
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need Calc to always ignore cells containing zero...

Postby DanielUK » Fri Jul 04, 2008 11:25 pm

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!
OOo 2.4.X on Ubuntu 8.x
DanielUK
 
Posts: 2
Joined: Thu Jun 19, 2008 1:24 am

Re: Need Calc to always ignore cells containing zero...

Postby TheGurkha » Sat Jul 05, 2008 12:23 am

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.
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
User avatar
TheGurkha
Volunteer
 
Posts: 6482
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.


Return to Calc

Who is online

Users browsing this forum: nofish, Richarda44 and 19 guests