How do you make blank cells calculated as zero

[LeeL]

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.

[SM_Riga]

Please see Libre help, this post, this topic, also this topic

[LeeL]

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.

[erAck]

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.

[LeeL]

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.

[mariosv]

Please use ‘add a comment’ and if the answer solves the question click .