Ask Your Question
0

How do you make blank cells calculated as zero

asked 2019-04-15 20:32:21 +0200

LeeL gravatar image

I have just downloaded LibreOffice and it appears just what I am looking for. However, I have a large number of spreadsheets done in .xls, .ods and .sxc. In all of these, all blank cells are calculated as zero. However, when I open any of them with LibreOffice and fill in the required information which usually means many cells are blank, I get a #VALUE response where results are shown as the blank cells are not being taken as zero, leaving me the only alternative of putting a zero in each of the cells, which is very time consuming. I assume there is a simple way to make LibreOffice consider blank cells as zero to solve this issue but so far I have not been able to find it.

edit retag flag offensive close merge delete

Comments

1
SM_Riga gravatar imageSM_Riga ( 2019-04-15 21:07:16 +0200 )edit

Applied this solution and apparently it did the trick. Many thanks, especially concerning the speed with which it was provided which greatly helped me with my pending work.

LeeL gravatar imageLeeL ( 2019-04-16 20:09:20 +0200 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2019-04-15 22:32:21 +0200

erAck gravatar image

It depends entirely on which functions you use. By definition many functions like SUM() (ok, it doesn't matter in that) and AVERAGE(), MAX(), MIN(), ... ignore empty cells completely as they process the NumberSequence of a range. This can not be changed. Regarding your #VALUE! error results nobody can help without seeing the actual formula expression you use and the data it processes. So please give detailed examples or attach a sample file (stripped of personal data of course and other data not to be published).

Note also that there are different settings how strings are to be processed in numeric operations' context (if that is your problem), see Tools -> Options -> Calc -> Formula, Detailed Calculation Settings, Custom, Details, Contents to Numbers. The safest is to use Generate #VALUE! error as that indicates problems in your spreadsheet early so you can fix them, with other settings the calculations may vary in different applications or locales.

edit flag offensive delete link more
0

answered 2019-04-16 16:49:33 +0200

LeeL gravatar image

Applied this solution and apparently it did the trick. Many thanks, especially concerning the speed with which it was provided which greatly helped me with my pending work.

edit flag offensive delete link more

Comments

Please use 'add a comment' and if the answer solves the question click ✔.

m.a.riosv gravatar imagem.a.riosv ( 2019-04-16 19:15:29 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-04-15 20:32:21 +0200

Seen: 251 times

Last updated: Apr 16