Bug 45951 - BASE(): support two's complement negative binary numbers (like DEC2BIN and HEX2BIN)
Summary: BASE(): support two's complement negative binary numbers (like DEC2BIN and HE...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.5.0 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Reported: 2012-02-12 03:32 UTC by Valerio Messina
Modified: 2016-09-19 16:47 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

examples of current formula limitations (83.73 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-05-30 20:59 UTC, Valerio Messina

Note You need to log in before you can comment on or make changes to this bug.
Description Valerio Messina 2012-02-12 03:32:08 UTC
BASE(): support two's complement negative binary numbers, like DEC2BIN and

This is usefull to represent high value negative numbers. As DEC2BIN and
HEX2BIN are limited to 10 binary digit, so no solution to represent binary numbers less than -512.

For reference: see also the corresponding post in the OASIS mailing lists at

For reference: tracked as http://tools.oasis-open.org/issues/browse/OFFICE-3557
Comment 1 Valerio Messina 2012-02-16 04:47:31 UTC
the BASE() should return Err:502 if the 1st parameter is negative and the 3th parameter is missing. When the 3th parameter is present, BASE() should compute negative results, at least when 2nd parameter is 2, 8 or 16.
Comment 2 Joel Madero 2013-05-30 16:22:53 UTC
Lionel - per usual, can you confirm this, sorry for having to ping you about these unconfirmed ones
Comment 3 Lionel Elie Mamane 2013-05-30 16:39:28 UTC
@jmadero: I think you had the wrong bug... This seems to be about some spreadsheet formula function that the reporter wants us to extend? You usually send Database-stuff to me, not spreadsheet (unless it is about connecting a spreadsheet to a database maybe).

Adding Eike to CC since he seems to have intervened on the OASIS tracker on this question https://tools.oasis-open.org/issues/browse/OFFICE-3557.

I see unanswered concerns on the OASIS mailing list https://lists.oasis-open.org/archives/office-comment/201011/msg00003.html

The request looks reasonable and sane to me, but I don't understand what the heck -512 comes to mean here (what is this magical constant?), nor the link with DEC2BIN or HEX2BIN. Actually, I don't understand HEX2BIN at all... It seems to "convert" a string representation in hexadecimal of a signed 40-bit number (why 40? dunno), that is 10 nibbles total, that is 10 characters/hexdigits in the string, into a string representation in binary of a signed 10-bit number (that is again 10 characters/bindigits in the string). The ranges look weird, because this will over/underflow for most values. It looks to me like the standard has the things in inverse of what it should. It would make sense to convert a 10-hexadecimal_digit string representation of a number (10 nibbles = 40 bits) into a 40-binary_digit string representation of the same number. Then the ranges match!
Comment 4 Valerio Messina 2013-05-30 20:32:00 UTC
the situation as now is:
1) DEC2BIN() and HEX2BIN() support two's complement negative binary numbers, but with a limitation of 10 (binary) digits on the results, so the minimum negative numbers is -512.
2) BASE() support up to 65534 binary digits in results, but does not support negative numbers at all.

So no way to convert -513 and less. This is not a so big negative number!

The limitation to 40 bit in input with HEX2BIN, is because once converted, in the current strange implementation, the output should be less than 10 bits.
For this RFE see another bug I opened.
At least 128 bit should be supported.
Comment 5 Joel Madero 2013-05-30 20:36:59 UTC
:( I just wasn't careful enough, sorry to bug you on it, if you have furhter input to Valerio's comment, of course it's appreciated :-D
Comment 7 Valerio Messina 2013-05-30 20:59:56 UTC
Created attachment 80069 [details]
examples of current formula limitations
Comment 8 Lionel Elie Mamane 2013-05-31 05:27:48 UTC
(In reply to comment #4)

> The limitation to 40 bit in input with HEX2BIN, is because once converted,
> in the current strange implementation, the output should be less than 10
> bits.

To fit the result in 10 bits (10 binary digits), the input has to fit in ... 10 *bits*, not 10 *nibbles* (that is, 10 hexadecimal digits, which correspond to 40 bits)! Anyway, I don't understand what the limitation of one implementation is doing in the "implementation neutral" standard.

And it seems that the implemenation in LibreOffice cannot handle 10 bits, only 9: =HEX2BIN("2FF") gives "Err: 502" but =HEX2BIN("1FF") gives "111111111".

This is not my area of expertise, so I'll shut up now.
Comment 9 Valerio Messina 2013-05-31 08:47:20 UTC
understood what you mean now, I found the HEX2BIN limits with 10 hex digits (40 bit), when the output would fit in 10 binary digits. For example converting:
"0000000001" (10 digits) result 1, but converting "00000000001" (11 digits) results Err:502.
So apparently there are two strange limitations: 10 digits in the source number, and 10 digits in the resulting number.

While DEC2BIN accept up to 10 as second argument and manage negative numbers in results expressed on 10 digits (DEC2BIN(-1)=1111111111, HEX2BIN accept up to 10 as second argument, but you are right, the result must be in range 0/+511 (9 bits), so no two's complement, only positive numbers, with a 0 as MSb.
For example HEX2BIN(0x200) should result 1000000000 that has 10 bits, so the result as now is Err:502

I report those behaviors in bug 45950
Comment 10 Valerio Messina 2013-05-31 09:44:58 UTC
this bug is still marked as UNCONFIRMED, probably should be set to NEW so someone can ASSIGN it
Comment 11 Joel Madero 2013-05-31 17:29:12 UTC
Not until an expert verifies that there isn't some reason why the limit is there. We'll wait for Eike's feedback as Lionel and myself both admit that we lack knowledge in this section of the code
Comment 12 Eike Rathke 2013-06-28 14:49:51 UTC
And just how would you like a negative number to be represented in bases other than powers of 2?

For powers of 2 and two's complement, an easy way is to use the modulo value of the known length, e.g. for 4 bit it is modulo 16 (2^4), so =BASE(MOD(number,16),2) for values from -8 ("1000") to 7 ("111").

If you want you can extend that system to other bases, e.g. for base 3 with length of 2 digits (modulo 3^2=9) the range could be -4 ("12") to 4 ("11") and =BASE(MOD(-1,9),3) would result in "22". But that just would be a convention.

Doing the same for base 10 (decimal system) would result in a ten's complement, on the other hand there (if at all) the nine's complement is used instead.

For base 16 you probably do not want a sixteen's complement but still a two's complement instead.

Just some examples why I think this general approach to BASE is not a good idea.

So instead of extending BASE with undefined functionality, defining some "radix complement" function sounds more appropriate to me. I'm closing this report here.

You mentioned you proposed the same at OASIS, before doing such it would be good to think about the technical implications and use cases.
Comment 13 Robinson Tryon (qubit) 2015-12-18 10:32:53 UTC Comment hidden (obsolete)
Comment 14 Xisco Faulí 2016-09-19 16:47:55 UTC Comment hidden (obsolete)