hello @all,
doe’s anybody know a ‘ready to use’ function to convert long binary strings (bit strings) into a decimal representation? i know about ‘bin2dec’ but that’s limited to a few bits (decimal values ~-512 … +511),
i know i can build something iteratively working through the string and converting it in portions, but … would like to concentrate on other work and have in a short statement rather than a long error prone formula …
thanks for any help …
DECIMAL(“Text”; Radix) ?
I didn’t know if anybody knew. Now I know that @EarnestAl knew one.
Anyway:
All the converting standard functions containing a DEC in their names are lying insofar. They all convert to and from Double in Calc.
If such a function converted something “2DEC”, and the Double result is assigned (returned) to a cell as its value, it will automatically be converted for the display to a string depending on the cell’s NumberFormat. And this string is what finally looks decimal.in many cases.
To clarify even more:
The functions will treat the passed string as a representation of an integer. This makes a difference, because the resulting bits are not treated as a representation of a IEEE 754 Double, which would give a different result if implemented that way. When the integer value is calculated, its value then is assigned to a double for further processing (in a formula, as a cell value, in further formatting of the output, etc.).
So in the context of the question: the string is not a “bitstring” (in the sense of representing memory bits), but a representation of a number in a given number system (as of course mentioned in DECIMAL documentation).
@EarnestAl: whow, pls. make it an answer for upvoting,
@Lupp and @mikekaganski: good hints!, 
will try to understand and calculate accordingly …
@Lupp: i’m not sure which representation is ‘leading’ at runtime, think you are right: dyadic, but the decimal must reside somewhere in memory to get displayed, when saving to a file the decimal repr. is the only thing taken?
i have the vague bad feeling that calc doesn’t give the ‘true string’ to the user but a ‘derivative’? messed up in several respects, but do not! repeat the proposal to revert / change that because @erAck is already annoyed that im poking on it again and again …
@mikekaganski: believe me, i did! ‘search before ask’, didn’t get it with ‘convert binary decimal’, neither ‘ask’ nor startpage,
the mentioned documentation: ‘Converts text with characters from a number system to a positive integer in the base radix given.’ sounds a little ‘odd’ to me, exactly 180° odd, it’s not to! but from! the given radix, would:
‘!tries to!: convert text with characters assumed representing a value in a number system based on a ‘base’ or ‘radix’ value given by the parameter ‘radix’, to a decimal representation (base or ‘radix’ ‘10’).’ do better?
@Lupp: i’m not sure which representation is ‘leading’ at runtime, think you are right: dyadic, but the decimal must reside somewhere in memory to get displayed, when saving to a file the decimal repr. is the only thing taken?
Again I don’t understand. What do you mean by “leading representation”? As told a few (or not so few) 1000 times in threads here and everywhere, te only representation of numbers internally used by Calc (itself, not user code) for cell values Is IEEE 754 Double.
Not as often, but often enough also was supposedly told that the only representation of numbers from cells used in the persistent (file) representation of spreadsheet documents in OpenDocumentFormat(s) is decimal as described by a syntax. You find this in the OASIS documents on PDF, e.g.
You may also remember that there cannot be a system of conversions guaranteeing closed round-trips DyaDec for mathematical reasons. The conversion, however, is done on save/load/display.
@Lupp: ‘leading representation’: haven’t read all your answers and comments, thus pls. be indulgent if i’m talking nonsense … there are two representations in memory at runtime, IEEE for calculations und decimal for UI, i cant say which is ‘chief’, they have to play ping-pong between their areas of responsibility?
the ‘round-trip-question’: is that the ambiguity problem i mentioned somewhere and proposed a 1:1 relation and @erAck said ‘no - nonsense’?
coupling one decimal value to exactly one double value and thus implementing a 1:1 relation instead of - as it’s today? - a m:n or b:d relation (for 15 digit values we always have enough doubles for a 1:n mapping and can select on of the 'n’s and correct it’s siblings to his value, once you allow 16 decimal digits (as calc actually does to enable larger integers?) you are running out of double for each integer at 2^53 and thus cannot go on with the same 1:1 relation?
interesting problem …
When you type something to a Calc cell, you always type text (whatever it might contain).
When you end the input (e.g., press Enter), Calc starts to process the input string into cell value.
It checks if the cell format is Text, and if so, it stores the input string as cell value as text.
If it is has another format, Calc checks if the input string starts with = or -, and then parses the input string as formula, and sets the cell formula.
Otherwise it tries to convert the string to double, using different methods: if it’s a string with decimal characters possibly with locale-specific decimal and thousand separators, or if it looks like a date in ISO notation, or in notations defined in acceptance patterns, or is it a time, or does it include % or locale-specific currency symbols etc.
If conversion succeeds, the resulting double is set as the cell’s value. Otherwise, the input string is cell’s textual value.
And the original input string is discarded. It is lost forever, it is not kept. Its copy is only stored in cell if either the cell was formatted as text, or all conversions have failed. In all other cases, what is stored in the cell is the result of the conversion (double if the conversion into it succeeded).
And then, when it needs to be displayed, the value of the cell (e.g., a double) is processed again - according to the format of the cell. There’s no ‘leading’ or ‘not leading’ representation.
Note also that at display time, for whole numbers representable in double (less than 2^53), they are displayed in full precision - i.e., for largest values, all 53 bits are used in output. For all other values, including integers >= 2^53, the number is rounded to 15 digits for display, so you see not what is stored in cell, but its rounded variant.
Neither the DECIMAL() function nor other standard means available in Calc convert numbers of any represenation to any different representation without using IEEE 754 Double at least interim.
(Slightly OT:) In addition an exact conversion of arbitrary decimal fractions to a dyadic representation of limited length is impossible for mathematical reasons. Fractions cancelled as completely as possible with denominators still containing any prime factor different from 2 are not exactly convertible. Some of them, however, have an exact representation as a decimal fraction. Powers of 5 in the denominator don’t block this.
Back to integers.
If you want to convert huge integers from dyadic (more than 53 bit) to decimal, you need to do so by string manipulations.
The attachment contains a (raw) demonstration of how it can be done by user code.
stringBasedConversionDyadicToDecimal.ods
To implement similar tools for dyadic-system-fractions would require much more helpers, I suppose.
(There were times -long before modern FPU existed- when bankers -and who else?- insited on software assuring zero-error calculations for a certain range of tasks. I don’t now the current state concerning “BCD aritmetic”. Ordinary people needing calculations exact beyond what spreadsheets can supply, use mathematical software like Mathematica or -for free- wxMaxima e.g. Such specialist software can also, of course, not change fundamental limitations.)
impressive, might be done by string manipulation,
‘=decimal(“100100100100111111000011111111010110000001011001010010101101101111”;2)’ which is a little more than 53 bits yields in 4,217144298283290E+19
It’s difficult for me to understand this comment.
Of course, I also tested the function I provided against =DECIMAL(myString; 2). That a standard function can’t do it exactly for a dyadic string of more than 53 bit is again due to its relying on the standard Double representation. That the result shown for DECIMAL() is achieved by first converting to Double and then formatting the result to decimal should now be clear.
What I described is done by string manipulation. Only in one place conversion to standard numbers is used (slightly improving efficiency), but there the maximum value is 19, and no problems concerning accuracy can occur.
'=DECIMAL(“Text”; Radix)" from @EarnestAl is the! solution,
@EarnestAl: why didn’t you make it an answer?