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.

Lionel - per usual, can you confirm this, sorry for having to ping you about these unconfirmed ones

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

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.

:( 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

see:
https://bugs.freedesktop.org/show_bug.cgi?id=45950
https://bugs.freedesktop.org/show_bug.cgi?id=45952

Created attachment 80069 [details]
examples of current formula limitations

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

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

this bug is still marked as UNCONFIRMED, probably should be set to NEW so someone can ASSIGN it

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

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.

@Valerio:
You mentioned you proposed the same at OASIS, before doing such it would be good to think about the technical implications and use cases.