[SOLVED] CALC function

[Keith Sayers]

I have encountered what seems to be a structural fault in CALC Version: 6.4.7.2 Build ID: 1:6.4.7-0ubuntu0.20.04.1
- the FIND() function cannot detect a decimal point within a zero field. Suffixed is an extraction from a database - row 17, columns W to Z.
The command in cell Z17 is =LEFT(W17,FIND(".",W17)-1) and the result is in cell Z17

Column : W X Y Z
0.00 18 53 #VALUE!


In other cells in that column the command works correctly and if I change that decimal point to a comma, then FIND() does find it.

Is this a known problem? If so is there a workaround? If not is it worth reporting and if so to whom?

[jlittle]

spreadsheets have two types of data, numbers and text. The number in your w17 is zero, not the text "0.00", that's just how it is formatted for display.

You could convert the number to text with the text function, and you give the text function a format to use, so maybe

left(find(".", text(w17, "#0.00"))-1)

But that looks like a roundabout way to get the first digit of the whole number. I'd prefer something like mod(int(w17), 10) for that.

[Keith Sayers]

spreadsheets have two types of data, numbers and text.....

I am well aware of that!

You could convert the number to text .....

But why should that be needed? The other numbers (number numbers, not text numbers) in the Z column are all detecting the decimal point. If they can why cannot Z17? This is my puzzle - why should the decimal in the number 0.00 give the FIND() function indigestion?

left(find(".", text(w17, "#0.00"))-1)

That works but needs another W17, after that first bracket

..... something like mod(int(w17), 10) ......

By chance, the numbers in the W column are the product of a MOD() function earlier in the proceedings ......

But my thanks for the response.

[chimak111]

... to whom?

https://bugs.documentfoundation.org.

[Keith Sayers]

Done - thank you.

[Keith Sayers]

Apparently not a bug (so a feature?) - see https://bugs.documentfoundation.org/....cgi?id=145183

[chimak111]

Good that you got some decent responses even though there's a difference of opinion!

If you're moving to a newer version of LibreOffice, I'd suggest staying with the "Still" branch rather than the "Fresh" one.

[Keith Sayers]

Indeed! But now how do I mark this thread SOLVED? There used to a drop down list at the top of the page but I do not now see it.

[Snowhog]

Go to first post. Click Edit. Use the drop down menu left of the Title.

[oshunluvr]

Can we move "[SOLVED]" to the top of that list?

Seems like the order should be based on need. Having to scroll to nearly the bottom seems unkind if we want users to mark their posts.

[Snowhog]

Guest Done. [SOLVED] is now at the top of the pick list.