We will be migrating from Ask to Discourse on the first week of August, read the details here

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

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.

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]

edit retag close merge delete

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

( 2020-09-11 18:22:44 +0200 )edit

Just added a few Enter, to format the question.

( 2020-09-11 18:38:39 +0200 )edit

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

( 2020-09-11 22:02:04 +0200 )edit

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

( 2020-09-12 00:19:25 +0200 )edit

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

( 2020-09-12 16:30:15 +0200 )edit

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/s... and https://bugs.documentfoundation.org/s...,
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

( 2020-09-12 23:36:02 +0200 )edit

Sort by » oldest newest most voted

It is to calculate the pip value of a trade in forex or commodities. Most currency pairs have 5 digits after the comma. Yen, silver & bitcoin have 3 digits after the comma. Oil & gold have 2 after the comma. I just thought there was a shorter way than having to first search of the comma in every currency & then doing the calculation. Using the round function give me the wrong results sometimes.

audusdi buy 0,72408 0,72408
usdzari sold    16,75290    16,75371 oil
sold    37,44000    37,58000 oil
sold          42,76 52,36
bitcoin sold    12108,42    10323,53 gold
sold    1948,86 1972,71

more

( 2020-09-13 12:56:14 +0200 )edit

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?)

more

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)

more

@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 ...

( 2020-09-12 00:32:45 +0200 )edit

@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.

( 2020-11-22 21:40:52 +0200 )edit

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?

( 2020-11-23 01:01:54 +0200 )edit

Changed reprenented by represented in the first line.

( 2020-11-23 01:08:49 +0200 )edit