'BASE' function for fractional values?

[newbie-02]

hi @all,

i’m just curious if there is something like DEC2BIN or BASE(xyz;2) which is capable handling (decimal) fractions like 1,5 or 12,004505?

‘=BASE(10;2)’ results in ‘1010’ (binary), and with what i’m looking for something like ‘FRACBASE(10,5;2)’ should result in 1010,1 (binary),

our friend Lupp build something similar working with an external DLL and producing the digits of exponent and mantissa of the IEEE representation of decimal figures in separate cells, i tried myself with - long - expressions in a sheet, both work but are not quite handy,

is there any simple solution around?

reg.

b.

[Lupp]

Please remove base from the tags That tag is supposed to relate to the Base component of LibreOffice.
calc and binary shoud be enough, fraction probably added.

[newbie-02]

@Lupp: done, this ambiguity? - base ./. base - is ‘no good’, things like that make every research e.g. in www quite difficult

[keme]

is there any simple solution around?

“Simple” as in “totally trivial”? Afraid not. Some clutter must be expected.

You did not show us what you “… tried myself with - long - expressions …”, nor did you indicate what you consider a " - long - " expression, so this may be of no use to you.

Nevertheless …

This one works for up to 24 fraction bits, yielding precision of approximately 7 decimals. Redirect A1 to whatever cell your number is contained in.

=DEC2BIN(A1) & "," & DEC2BIN( MOD(A1 ; 2^0) * 2^8 ; 8 ) & DEC2BIN(MOD( A1 ; 2^(-8) ) * 2^16 ; 8 ) & DEC2BIN( MOD( A1 ; 2^(-16) ) * 2^24 ; 8 )

This utilizes the DEC2BIN() function in a stepwise manner. That function is limited to 10 bit signed binary. Here a choice is made to proceed in byte size steps, so only 8 bits are used for each step.

Utilizing the full unsigned capacity of the function (9 bits) requires that you liberate yourself I liberate myself from the byte-at-a-time mindset, which yields this formula:

=DEC2BIN(A1) & "," & DEC2BIN( MOD( A1 ; 2^0 ) * 2^9 ; 9 ) & DEC2BIN( MOD( A1 ; 2^(-9) ) * 2^18 ; 9 ) & DEC2BIN( MOD( A1 ; 2^(-18) ) * 2^27 ; 9 )

I have not considered two’s complement conversion or large integer part, so the formula needs more work to support negative numbers or values exceeding 256.

Note also the use of comma as fraction separator (immediately after the first DEC2BIN()). This corresponds to the decimal separator used in the question, but depending on setting you may want to use a different character there.

[keme]

BASE() is obviously better, with support for any bit length it seems. I must have gone into a mental lockdown from the bit fiddling.

@Lupp unlocked it. See his answer. Forget this suggestion.

[newbie-02]

@keme: i like your answer although it was overtaken by a better one, also the approach of iterative processing is nice and deserves a ‘thank you’, upvoting

[Lupp]

What about

=IF(A1<0;"-";"+") & LEFT(BASE(ABS(A1);2)&"₂"&BASE(MOD(ABS(A1);1)*2^52;2;52);32)
(to get 32 characters behind the sign including the separator e.g.)?
[editing 2020-01-15 about 12:15]
There was a glitch in the explanation: (to get 32 digits behind the separator e.g.) was wrong.
The new explanation above should be correct. Rectification emphasized in italic.
[/edit]
The inverse operation seems not to be supported as efficiently.

@newbie-02: Even if you make sure to get the maximum of 52 dyadic digits supported by IEEE 754 Double, this is not exactly the same as the dll-routine I once supplied (and you mentioned). The Calc formula needs to actually calculate to get the bit-pattern, while the mentioned dll-function reads the bits from the RAM position the
number first was written to.

[newbie-02]

@Lupp: works, i had to replace “₂” with “,” but that’s one of these marginalities when working with localized versions and different character sets in ‘not well internationalized systems’ …
it is! possible to get more than the announced 32 digits by replacing the ‘32’,
‘this is not exactly the same as the dll-routine I once supplied’ - yes, there are marginalities to work around as the length of the string depending if a integer-part is present or not, the exact point where the binary rounding steps in (significant of binary representation of ‘0,1 dec’ ending in ‘~0011010’ instead of ‘~0011001’) and maybe others,
but … it is good help for my work, you are invited to join the IEEE 754 refurbishing team …
(the question is part of my work to check if it’s possible to get better results calculating fractions with floats, e.g. ‘=0,1 + 0,2’ → 0,3 instead of ‘3,0000000000000004E-1’, looks promising at the moment … )

[Lupp]

₂ (U+2082) not available for you? Its much better than the comma regarding disambiguation.
Concerning “the dll…” the relevant difference is that it reads bit patterns from RAM instead of calculating anything.

[newbie-02]

hello @Lupp,
‘dll’ - would be useful, but felt not fit enough to implement, instead cluttered together a formula trying to catch ‘the full string’:

=IF(A1<0;"-";"+")&BASE(ABS(A1);2)&","&IFERROR(LEFT("00000000000000000000000000000";-INT(LOG(ABS(A1)-INT(ABS(A1));2))-1);"")&LEFT(BASE((ABS(A1)-INT(ABS(A1)))*2^(52-INT(LOG(ABS(A1)-INT(ABS(A1));2)));2);53-INT(LOG(ABS(A1);2))+INT(LOG(ABS(A1)-INT(ABS(A1));2)))

which looks like working, but is more ‘experimentally aggravated’ than i understand it
if you have time for it i’d appreciate it very much if you could:

• check that formula for correctness, or
• do some testing if it ‘holds’, or
• adjust your dll to return the mantissa in one cell and the exponent in another, or just the bare double string,
  the goal is to provide an alternative to all these dec->bin conversion, rounding and cancellation corrupted results, that calculates correctly and is performant as working in ‘bits’ instead of decimals

[Lupp]

IEEE 754 Double -as long as not denormalized - is a floatingpoint format giving the sign in one bit and a 11-bit exponent-part (including an offset of 1023) to the base 2. To represent the full range in a standard dyadic fixpoint format you need to accept strings of 1 character for the sign, 1 for the separator, and more than 2000 for digits.
Probably no use in a formula for the conversion.

[newbie-02]

@Lupp, yeah, know that,
thus either i limit myself to values in a reasonable range (calculating money in ranges with a granularity > 0,01 is as useless as calculating ‘nanocents’ - means 53-9? ‘integer-bits’ for high values plus 53+6 bits for the fractional part for 1 cent, all together a string < 110 bit … calc can handele such, just tried something with 12.500 bits, works, thumbs up for calc),
or i stay universal and evaluate in a mantissa - exponent format,
second sounds better as easier to implement in libraries or chips lateron …
possible by either adapting the formula to a ‘normalized’ format, or - would prefer - pull from memory with e.g. your dll …

[Lupp]

IMO theres no reason to apply any of the alternatives - except an abstract interest in the ways numbers can be represented. Of course, you can also write conversions to an from “Roman” or whatever, and I often used the idea for getting examples in beginner’s courses about programming.
For your further “playing” see attached example.numberConversionToNormalizedDyadic.ods

[newbie-02]

@Lupp: :-), thanks you so much,
nice to see that your formula grew to similar complexity as mine,
think we have some flaw left as 0,3 dec. should result in +1,0011001100110011001100110011001100110011001100110011 2E-2 (acc. ‘weitz’) rather than
+1,0011001100110011001100110011001100110011001100110100ß-10
there is something odd with the tail, ‘rounded’? isn’t neccessary for this case …
i need especiallay theese bits ‘clean’ as they are the! central point injecting deviations which result in fp-errors,

[Lupp]

Once more I don’t understand. I don’t know what you mean by “your formula”. I described an algorithm trying to reduce the complexity concerning the ways of understanding, and I also implemented that (a very similar) algorithm in a user function without much attention to efficiency or compactness.
Anyway the proceeding returns exactly the perodic dyadic fraction (cut off after 52 bits) you quoted under “should result in” for 0.3 . And that’s also what my .dll you mentioned returns as the RAM representation.
I just checked this, because i couldn’t be sure. After all the conversion algorithm does a lot of calculations only mathematically designed without considereing ticklish questions of machine-arithmetic.
The result you quoted under “rather than” must have a different origin.

[newbie-02]

hello @Lupp,

did i mention how much i appreciate your calm way of getting to the bottom of things? no? i like it immensely! and it makes you a very positive part of this community, high praise!

the ‘different origin’ for the wrong result is calc ver. 6.1.6.3, which i (yet) considered quite good and stable, now i have to check if it makes unnecessary mistakes or if some tricky settings are to blame …

the result with ver 7.2.0.0.a0+ is (more) correct,

(@Mike Kaganski changed a math library shortly, perhaps that’s the difference)

‘more things between heaven and earth’ …

from such a point - two systems with different results - i can often easily quickly narrow down problems, and learn!

regards,

b.