Does anyone know how to use excel to calculate Cheksum

[GUS]

I been wondering if there's is another easy way using excel and do the calculation..

CHECKSUM
The last byte is the checksum of the entire frame. The checksum is determined so that the sum of all the bytes in the frame - including the frame ID, the length byte and the checksum byte itself - is zero, ignoring any carry bits.

For example the frame $F5,$57,$01,$00,$B3

Has a frame ID of $F7.
Has a length of $57-$55=$02 data bytes.
Contains two data bytes: $01,$00.
Has a checksum of $B3.



To calculate the checksum in hex:

Sum the bytes $F5+$57+$01+$00=$14D.

Find the remainder when the sum is divided by $100 which in this case is $4D.

The checksum will be $100-$4D=$B3.




To calculate the checksum in decimal:

Sum the bytes 245+87+1+0=333.

Find the remainder when the sum is divided by 256 which in this case is 77.

The checksum will be 256-77=179 (179 is $B3 in hexadecimal)

Thank You

 

[Yogi Anand]

On 2002-03-23 15:15, GUS wrote:
Tommy,
Thank you very much for your reply..

I am sorry I am not to smart as you guys.
Question..
How I use the string =dec2hex(HEX2DEC(100)-HEX2DEC(RIGHT(dec2hex(HEX2DEC(mid(A1;2;2))+HEX2DEC(mid(A1;4;2))+HEX2DEC(mid(A1;6;2))+HEX2DEC(mid(A1;8;2))+HEX2DEC(mid(A1;10;2))+HEX2DEC(mid(A1;12;2))+HEX2DEC(mid(A1;14;2)));2)))

In a macro in a module.. ?

Because when I type =dec2hex(HEX2DEC(100)-HEX2DEC(RIGHT(dec2hex(HEX2DEC(mid(A1;2;2))+HEX2DEC(mid(A1;4;2))+HEX2DEC(mid(A1;6;2))+HEX2DEC(mid(A1;8;2))+HEX2DEC(mid(A1;10;2))+HEX2DEC(mid(A1;12;2))+HEX2DEC(mid(A1;14;2)));2)))
on a given cell, it didn't work I did add the Analysis toll Pack which is also selected in add in module.
I have in Cell A1 022CE5003B7B3C and I typed the formula given in A2. Which should give the checksum from A1.

Please advise
Best regards
Gus
This message was edited by GUS on 2002-03-23 16:04

Hi Gus and Tommy:
How about looking at the differences in the Danish and the U.S. versions of Excel.
In Tommy's formula, the argument separator is ; -- U.S. version uses the , as argument separator.
I have not put Tommy's formula in the worksheet yet, but I guess this is it! Now befor e I say anything more I better go check that formula out with the , as the argument separator!

 

[Yogi Anand]

Hi Gus and Tommy:
I did replace the ; as argument separator with a , and Tommy's formula did result in 1E

Great Job T O M M Y !

 

[Tommy Bak]

Thanks Yogi
You're absolutely right.
In Denmark the default setting is ; for list seperator and , as decimalsign.

I just didn't convert it.

regards Tommy

 

[GUS]

You all were right, even do it I have to do some changes.. I learn something new from you Tommy B.

Muchas Gracias Amigo..

Best regards
GUS

 

[Avarial]

I know this is an old thread, but it is relevant to a current problem I am having.

In cell A1, I have GPRMC,164237.615,A,3857.7725,N,09437.6296,W,0.85,159.46,221117,,,A

In cell B1 I have =dec2hex(HEX2DEC(100)-HEX2DEC(RIGHT(dec2hex(HEX2DEC(mid(A1,2,2))+HEX2DEC(mid(A1,4,2))+HEX2DEC(mid(A1,6,2))+HEX2DEC(mid(A1,8,2))+HEX2DEC(mid(A1,10,2))+HEX2DEC(mid(A1,12,2))+HEX2DEC(mid(A1,14,2))),2)))

#NUM ! is the answer that I get, and I know the result should be "73"

Can someone please tell me what I did wrong?

 

[Avarial]

Not sure how to edit my post above.

Using Gus' original example of ":0300300002337A1E", I did get an answer of 1E

 

[fsteff]

Although this is a VERY old posting, 7005 days old as of this writing, it's still the first hit when you search for EXCEL and CHECKSUM on Google.
It's unfortunate as this thread doesn't even contain a working solution.

So here's a verified solution, which I've used for a long time.

This solution is valid for the checksum used in intel hex files and many other places, where the checksum is calculated by summing the values of all hexadecimal digit pairs in the record modulo 256 and taking the two's complement.

For this implementation, I have 21 Hexidecimal 8bit values in the range J6:AD6. (I'll leave it as an exercise to split an intel hex string into this range.)

Unfortunately excel does not support recursion, so this formula is rather large:

=DEC2HEX(
  MOD(256-
    MOD(
      MOD(
        MOD(
          MOD(
            MOD(
              MOD(
                MOD(
                  MOD(
                    MOD(
                      MOD(
                        MOD(
                          MOD(
                            MOD(
                              MOD(
                                MOD(
                                  MOD(
                                    MOD(
                                      MOD(
                                        MOD(
                                          MOD(
                                          HEX2DEC(J6);256)
                                        +HEX2DEC(K6);256)
                                      +HEX2DEC(L6);256)
                                    +HEX2DEC(M6);256)
                                  +HEX2DEC(N6);256)
                                +HEX2DEC(O6);256)
                              +HEX2DEC(P6);256)
                            +HEX2DEC(Q6);256)
                          +HEX2DEC(R6);256)
                        +HEX2DEC(S6);256)
                      +HEX2DEC(T6);256)
                    +HEX2DEC(U6);256)
                  +HEX2DEC(V6);256)
                +HEX2DEC(W6);256)
              +HEX2DEC(X6);256)
            +HEX2DEC(Y6);256)
          +HEX2DEC(Z6);256)
        +HEX2DEC(AA6);256)
      +HEX2DEC(AB6);256)
    +HEX2DEC(AC6);256);
  256);
2)

As is evident, the formula is really straightforward to extend to fit any length of data record.