![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
I am disappointed that Libre Office does not treat blank cells as a zero in math calculations. I have a dozen spreadsheets from Excel and Open Office that take advantage of this fact. Now I get errors with Libre Office. Going back and retrofitting n(cell reference) to achieve the translation is a ton of extra work. Am I missing something.
gs
I'm not sure what you're missing, but my LO spreadsheets always treat blank cells as zeros. I'm not that well versed in spreadsheets, but I've never had the problem you describe.
Virgil
I have just tried in LO 3.6 and a blank cell is treated as zero. A cell with text in it such as a space causes an error in calculations. Do you mean that Excel and Open Office treat white space characters as zero.
Steve
Maybe this option can help:
Menu/Tools/Options/LibreOffice calc/Formula/Detailed calculation
settings/Custom/Treat empty string as zero
So the cells are the result of opening a .csv file where the string has nothing between commas such as
5,4,3,2,1
between the consecutive commas is nothing, so the cell should be made totally empty.
gs
If I put 5,4,3,2,1 in afile.csv and open it in LO3.6 I can then put the equation =b1*c1 in an empty cell and the result is 0.
Do you not get that. What version of LO.
steve
Hi 
If you need to put 0s in between commas then it might be possible without even opening the file, and might be fairly easy to apply to a shed load of files all very quickly after each other.
In Gnu&Linux i suspect something like
cp file-name.csv | grep(?) (replace "," with ",0," > file-name-0s.csv
although as you can see i'm not sure grep is right or what would follow it.
Stars instead of parts of the file-name would act as wild-cards meaning it would act on all such files that kinda match.
I'm more of a point&click user so the command line is usually a bit
of a mystery but i think it might be perfect for doing that sort of
thing very quickly.
Regards from
Tom
that sounds like it would work, but my options menu does not have that setting! I am using v3.4.5
gs
No I don't get zero I get a or #VALUE! error message.
gs
Sorry, my version is 3.4.5.
Hi
I have quite a few machines on a really old version too but really should upgrade soon. I downloaded the 4.0.4 but now might as well do the 4.0.5 instead.
Regards from
Tom
Hi
Err are you using a Mac? If so the menus are very different. It's
LibreOffice - Preferences - blah
instead of
Tools - Options - blah
Hmmm, on Ubuntu 12.04 with LO 4.0.2 I only get the
Tools - Options - LibreOffice Calc
option if i have a spreadsheet open in LibreOffice. A blank "new" spreadsheet is fine. Then i have to click the "Detail" button in the middle section of the "Formula" page to get the option to tick the "Treat empty string as zero" line. There's only 2 lines in that pop-up so it's not easy to get it wrong! 
It seems like a bit of a faff trying to find the place but i'm not sure if there is anywhere that would be better. I get the feeling that either they are going to add more options in there or else that section is going to be merged with something. Change is likely in future releases but which way is unclear from the UI right now.
Regards from
Tom
TomThanks for trying to help out.I have attached a screen grab of
my Formula sub-menu that just doesn't have the option you refer
to.I have went through every menu of the options for calc and
found nothing about this!gsOn 08/20/13 10:10, Tom Davies wrote:
Hi :)Err are you using a Mac?If so the menus are very different.It'sLibreOffice - Preferences - blahinstead ofTools - Options - blahHmmm, on Ubuntu 12.04 with LO 4.0.2 I only get theTools - Options - LibreOffice Calcoption if i have a spreadsheet open in LibreOffice.A blank
"new" spreadsheet is fine.Then i have to click the "Detail"
button in the middle section of the "Formula" page to get the
option to tick the "Treat empty string as zero" line.There's
only 2 lines in that pop-up so it's not easy to get it wrong!
;)It seems like a bit of a faff trying to find the place but i'm
not sure if there is anywhere that would be better.I get the
feeling that either they are going to add more options in there
or else that section is going to be merged with something.Change is likely in future releases but which way is unclear
from the UI right now.Regards fromTom
that sounds like it would work, but my options menu
does not have thatsetting!I am using v3.4.5gsOn 08/19/13 15:51, mariosv wrote:Maybe this option can help:Menu/Tools/Options/LibreOffice
calc/Formula/Detailed calculationsettings/Custom/Treat empty string as zero--View this message in context:http://nabble.documentfoundation.org/treatment-of-blank-cells-tp4070670p4070674.htmlSent from the Users mailing list archive at
Nabble.com.--To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.orgProblems?http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/Posting guidelines + more:http://wiki.documentfoundation.org/NetiquetteList archive:http://listarchives.libreoffice.org/global/users/All messages sent to this list will be publicly
archived and cannot be deleted
Hi
The list doesn't accept attachments so i have uploaded the screen-shot to
Nabble
Screenshot.png
<http://nabble.documentfoundation.org/file/n4070816/Screenshot.png>
So, it's an Xfce "Desktop Environment" so probably a Gnu&Linux (rather than
Bsd or Mac) and we know the LO version is 3.4.5.
Errr, i'm stuck now!
Regards from
Tom
Because this option has been introduced in 4.0.
Perhaps one of the reasons it was made is that different function do not treat
white space consequentially. This is really bad, because one can not really
expect to get predictable results using the same set of arguments with
different functions.
It might be that 3.4.5 is one of these versions that are confused by empty
cell.
But then, 3.4 line is long dead and gone. It will not get any bugfix release
and this will not be fixed in 3.4.x. Your only option is to upgrade to 4.0.5
(due to release this week).
