How do you calculate the number of digits after a decimal point

[Hermes14]

I’m trying to calculate the number of digits after the decimal point so that I can turn the total into a whole number.

Example: I have this data.

1. buy 0,69513 0,69614

2. buy 75,293 75,331

3. sold 37,44 37,58

In row one I have the formula

=IF(C28="buy";E28-D28;IF(C28="sold";D28-E28;0))

If I try

=IF(AND(C29="buy";"0,000000);*10000);E29-D29;IF(AND(C29="sold";0,00000);*100000;0"))

[erAck: made the formula a code block by breaking it into a new line and hitting Ctrl+K which indents it by 4 spaces; turns out the embedded * asterisks were munged in the flow text display as they are control characters for emphasized text]

[newbie-02]

your ‘try’ formula looks a little confused to me … as well the formula as the intention … the question from the headline could be answered with =LEN(TEXT(MOD(E28;1);"0,####################"))-2

[LeroyG]

Just added a few Enter, to format the question.

[newbie-02]

just check the double quotes in your question, tell where you have the data, and what you intend / expect the formula to do

[newbie-02]

looks better now, thks @erAck, but this section: ;"0,000000);*10000); is still mysterious to me

[erAck]

Absolutely, it doesn’t make sense at all. The author should correct that and also place the quotes where they belong…

[newbie-02]

hello @Hermes14,
i owe you some thanks because your question gave a kick to find and suggest some ‘creative solutions’ for the miserable floating-point-rounding problem, see: https://bugs.documentfoundation.org/show_bug.cgi?id=133840 and https://bugs.documentfoundation.org/show_bug.cgi?id=128312,
thank you!,
i don’t want to upvote your question at the moment because it still has grammatical errors, and it doesn’t make sense to me to compare businesses by different magnification factors …
happy hacking

[Lupp]

Generally numbers are actually represented in LibrOffice in a dyadic format name IEEE 754 Double. Since this representation is not decimal, there is no “number of digits after the decimal separator” defined. What you see is only made for the visualization, often by applying a format code.

If a numeric result is rounded to a specific number of decimals like in =ROUND(MyResult; 3) the number in the second parameter position is the number of decimal places.

If you actually have text looking like a number, e.g. 75,293 , and you have LibO V6.2 or higher, you can get the number of decimal places by the formula =LEN(REGEX(A 5;"(?<=(,|\.))\d*";;1)) where the comma and the point both are accepted as decimal separators.

If your value is assured to be rounded to a specific number of decimal places without giving this number explicitly, you can use the formula =TEXT(MyValue;"#,###############") first and then the formula given above.

Better: =LEN(MyTextualNumber) - FIND(DecimalSeparator; MyTextualNumber)

[newbie-02]

@Lupp: ‘What you see is only made for the visualization, often by applying a format code.’

that brings it to the point, already in the 90’s? WYSIWYG was a keyword and a recommendation that normal people started to understand and be able to handle computers, not cryptography with text formatting in abbreviations, or graphics from plotter commands, but clear presentation on the screen: “that’s what it looks like afterwards”,

that especially an exact science like mathematics and programs that require precision / pretend precision like spreadsheets are so terribly lagging behind … shameful …

[LeroyG]

@Lupp, I fixed a typo, thinking that it would work the same as when editing comments, where the original user is kept. Please, correct LibrOffice to override my username. Thanks. I didn’t find a way to undo.

[Lupp]

Don’t worry. That’s an often experienced effect of the ways this site works.
You may just add a remark like "(Slightly edited for … by…)
What was the typo you fixed?

[newbie-02]

just guessing … may be you look for the formula

=IF(C28="buy";ROUND((E28-D28)*(10^(LEN(D28) - FIND(","; D28)));0);IF(C28="sold";ROUND((D28-E28)*(10^(LEN(D28) - FIND(","; D28)));0);0))

be aware that - as i adapted @Lupp’s formula to a numerical value instead of text - everything may fail,
(i was surprised myself why it worked)

be aware that with different format in D28 / E28 results may fail,

=IF(C29="buy";ROUND((E29-D29)*(10^MAX((LEN(D29) - FIND(","; D29));(LEN(E29) - FIND(","; E29))));0);IF(C29="sold";ROUND((D29-E29)*(10^MAX((LEN(D29) - FIND(","; D29));(LEN(E29) - FIND(","; E29))));0)))

may work a little better, but may also suffer from rounding issues,

shorter version:

=IFS(C29="buy";1; C29="sold";-1; 1;0)*(ROUND((E29-D29)*(10^MAX((LEN(D29) - FIND(","; D29));(LEN(E29) - FIND(","; E29))));0))

be aware that as e.g. =MOD(75,293;1) results in 0,293000000000006 the formula from my comment may fail as well as nearly all calc formulas …

!!! - welcome to the fuzzy hell of floating point math - !!!

be aware that ‘buy 1,1 1,11’ will result in the same ‘1’ as ‘buy 1,1 1,10001’, which is probably not what you want?
(unless you are on a trip to build a new randomizer?)

[erAck]

Please do not use the Answer field for comments that are not an answer to the original question, instead edit your original question to provide further details. Thanks. And please delete this answer when done.