3

In Excel cells A1 and A2, I have the following hexadecimal numbers: 04cf and 04fb which are 1231 and 1257, respectively.

I am trying to convert A1 and A2 to hexadecimal and then add the two to obtain 2488. In B1, I enter =SUM(HEX2DEC(A1:A2)) but this returns an error.

It looks like I need to have a separate section devoted to the decimal forms of A1 and A2, and then I can add the numbers within that section together. The thing is I have a lot of data and it would take up a lot of space on the spreadsheet. I'm looking to see if it's possible to do this more cleanly?

Thanks a bunch.

0

4 Answers 4

5

Try with CTRL + SHIFT + ENTER:

=SUM(HEX2DEC(+A1:A2))

The + symbol changes the range argument to an array. This operation can be applied to function arguments that don't accept ranges of more than one cell including some of those in the Engineering category and others that used to form part of the "Analysis Toolpak" in older versions.

You can also try SUMPRODUCT as an alternative to SUM and enter the formula without the need for the CSE keystroke.

5
  • Can you explain this answer in more detail?
    – bwDraco
    Jul 3, 2015 at 4:29
  • check out Barry Houdini's explanation here: stackoverflow.com/a/31092718/1252820
    – lori_m
    Jul 3, 2015 at 5:14
  • It's best to edit a summary of that into the answer, as we prefer posts that can stand alone.
    – bwDraco
    Jul 3, 2015 at 5:21
  • +1, this solution is elegant and works. Just don't forget to use CTRL+SHIFT+ENTER!
    – misha256
    Jul 3, 2015 at 5:38
  • @DragonLord: a short summary has been included. Hope that helps
    – lori_m
    Jul 3, 2015 at 17:31
0

Consider the following UDF:

Public Function HexSum(rIN As Range) As Long
    Dim r As Range, v As String, n As Long
    HexSum = 0

    For Each r In rIN
        v = r.Text
        n = Application.WorksheetFunction.Hex2Dec(v)
        HexSum = HexSum + n
    Next r
End Function

enter image description here

-1

Instead of the formula =SUM(HEX2DEC(A1:A2)), use =HEX2DEC(A1)+HEX2DEC(B1).

HEX2DEC is not a matrix-enabled function, like most other functions in Excel. Its argument cannot be an array, it must be a single hex number, hence the error.

-3

try

=SUM(HEX2DEC(A1),HEX2DEC(A2))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .