Doing Binary and/or Hexidecimal calculations in Excel

Question

In general I have always considered myself as very competent in Excel (normal, Pivot Tables, graphing, VBA, etc), but right now I'm about to look for a good stiff drink!!!

I have a large number of hexadecimal records that I want to analyze and plot some of the calculated results. In a program it might look like this

static float getValue (uint8_t highbyte, uint8_t lowbyte) {
    int highbits = (highbyte & 0x0F) << 7 ;
    int lowbits = lowbyte & 0x7F;
    int rawValue = highbits | lowbits;
    float Value = rawValue;
    return Value;
}

I have tried using Excel and its binary and hexadecimal functions. Sometimes I get something that makes sense, for example "=BITAND(HEX2BIN(G3,8),HEX2BIN(N2,8))" where cell g3 is hex "30" and n2 is hex "0f" and the result is 1040. This I assume is the decimal result, but Excel throws a data error when I try to convert it to the binary equivalent so I can carry out the left shift operation.

As I said above I have a lot of records to process and I would like to keep it all in Excel if possible.

Needless to say, working in hex or binary is not my strong suit. Can anyone help me with the Excel functions to duplicate the above programming code?....RDK

L. Scott Johnson · Accepted Answer · 2025-04-09 01:40:15Z

1

hex2bin() returns a string, which is not a suitable argument for bitand(). Use hex2dec() instead:

=BITAND(HEX2DEC(G3),HEX2DEC(N2))

I think also you intend to do an or operation, not an and, so you should check about replacing bitand() with bitor(), but that's secondary to the main problem of trying to use a string binary as a number.

edited Apr 9 at 1:40

answered Mar 7, 2019 at 12:42

L. Scott Johnson

4,4192 gold badges19 silver badges31 bronze badges

Thanks, I'll try experimenting with this later today. Excel's hex and binary functions are very confusing
– RDK
Commented Mar 8, 2019 at 6:31
Yeah. The tricky part here is that HEX2BIN and HEX2DEC are designed to work with strings by necessity. If they find a number, they will convert it to a string (take the decimal representation of that number) and then interpret those digits as hexadecimal digits. The bit operations BITAND, BITOR, and so on expect numbers or decimal representations of numbers. So when HEX2BIN returns "00110000", BITAND doesn't interpret that as 48 but rather as 110,000.
– L. Scott Johnson
Commented Mar 8, 2019 at 12:39

Add a comment |

Collectives™ on Stack Overflow

Doing Binary and/or Hexidecimal calculations in Excel

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related