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

Ask Your Question
0

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

asked 2020-09-11 17:46:04 +0200

Hermes14 gravatar image

updated 2020-09-11 23:29:40 +0200

erAck gravatar image

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]

edit retag flag offensive close merge delete

Comments

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

newbie-02 gravatar imagenewbie-02 ( 2020-09-11 18:22:44 +0200 )edit

Just added a few Enter, to format the question.

LeroyG gravatar imageLeroyG ( 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

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

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

newbie-02 gravatar imagenewbie-02 ( 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..

erAck gravatar imageerAck ( 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

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

3 Answers

Sort by » oldest newest most voted
2

answered 2020-09-11 20:11:00 +0200

Lupp gravatar image

updated 2020-11-22 21:29:19 +0200

LeroyG gravatar image

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)

edit flag offensive delete link more

Comments

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

newbie-02 gravatar imagenewbie-02 ( 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.

LeroyG gravatar imageLeroyG ( 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?

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

Changed reprenented by represented in the first line.

LeroyG gravatar imageLeroyG ( 2020-11-23 01:08:49 +0200 )edit
0

answered 2020-09-12 12:38:13 +0200

Hermes14 gravatar image

updated 2020-09-13 16:38:31 +0200

LeroyG gravatar image

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
usdcadi sold    1,31802 1,31938
eurusdi buy 1,18190 1,18206
usdzari sold    16,75290    16,75371 oil       
sold    37,44000    37,58000 oil          
sold          42,76 52,36
bitcoin buy 9088,41 10598,4
bitcoin sold    12108,42    10323,53 gold     
buy 1938,52 1955,05 gold          
sold    1948,86 1972,71
edit flag offensive delete link more

Comments

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.

erAck gravatar imageerAck ( 2020-09-13 12:56:14 +0200 )edit
0

answered 2020-09-12 01:17:33 +0200

newbie-02 gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-09-11 17:46:04 +0200

Seen: 126 times

Last updated: Nov 22 '20