Ask Your Question
0

'BASE' function for fractional values?

asked 2021-01-14 04:18:41 +0100

newbie-02 gravatar image

updated 2021-01-17 01:23:37 +0100

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.

edit retag flag offensive close merge delete

Comments

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.

Lupp gravatar imageLupp ( 2021-01-14 13:32:12 +0100 )edit

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

newbie-02 gravatar imagenewbie-02 ( 2021-01-17 01:25:49 +0100 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2021-01-14 16:01:38 +0100

Lupp gravatar image

updated 2021-01-18 13:00:06 +0100

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.

edit flag offensive delete link more

Comments

@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 ...(more)

newbie-02 gravatar imagenewbie-02 ( 2021-01-17 04:04:58 +0100 )edit

₂ (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.

Lupp gravatar imageLupp ( 2021-01-17 14:53:19 +0100 )edit

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 ...(more)

newbie-02 gravatar imagenewbie-02 ( 2021-01-17 23:02:59 +0100 )edit

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.

Lupp gravatar imageLupp ( 2021-01-18 01:02:00 +0100 )edit

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

newbie-02 gravatar imagenewbie-02 ( 2021-01-18 05:44:29 +0100 )edit

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.C:\fakepath\numberConversionToNormalizedDyadic.ods

Lupp gravatar imageLupp ( 2021-01-18 13:00:17 +0100 )edit

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

newbie-02 gravatar imagenewbie-02 ( 2021-01-18 18:38:41 +0100 )edit

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.

Lupp gravatar imageLupp ( 2021-01-18 20:44:58 +0100 )edit

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.

newbie-02 gravatar imagenewbie-02 ( 2021-01-19 02:20:59 +0100 )edit
2

answered 2021-01-14 13:04:34 +0100

keme gravatar image

updated 2021-01-14 21:08:33 +0100

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.

edit flag offensive delete link more

Comments

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.

keme gravatar imagekeme ( 2021-01-14 21:07:11 +0100 )edit

@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

newbie-02 gravatar imagenewbie-02 ( 2021-01-17 04:12:01 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-01-14 04:18:41 +0100

Seen: 57 times

Last updated: Jan 18