Thanks:  0
Likes:  0

# Thread: Does anyone know how to use excel to calculate Cheksum

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

2. The checksum is calculated by summing the value of the data on the line, excluding the leading colon and checksum byte itself, and taking its two's complement. For example, the line:

:0300300002337A1E

Breaking this line into it's components we have:

Record Length: 03 (3 bytes of data)
Address: 0030 (the 3 bytes will be stored at 0030, 0031, and 0032)
Record Type: 00 (normal data)
Data: 02, 33, 7A
Checksum: 1E

Taking all the data bytes above, we have to calculate the checksum based on the following hexidecimal values:

03 + 00 + 30 + 00 + 02 + 33 + 7A = E2

The two's complement of E2 is 1E which is, as you can, the checksum value.

For those unfamiliar with calculating a two's complement, it's quite simple: The two's complement of a number if the value which must be added to the number to reach the value 256 (decimal). That is to say, E2 + 1E = 100.

You may also calculate the two's complement by subtracting the value from 100h. In other words, 100h - E2h = 1Eh -- which is the checksum.

If the value in question is greater than FFh, simply take the part which is less than 100h. For example, if you want the two's complement of the value 494h, simply drop the leading "4" which leaves you with 94h. The two's complement of 94h is 6Ch.

Anyone?

3. Sorry,
If I had a better understanding of solving the equations, I could probably solve your problem...

4. Thank you TOM the magic of willingness is truly appreciated...

Sincerely
GUS

5. Not sure excel was built for checksum.

6. Hi Gus
Try this. It seems to return what you want.
Make sure that the add-inn "Analysis toolpak" is installed.

regards Tommy

=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)))

7. Tommy,
I may need to E-Mail you an aspirin for writing that one.

8. Hi TsTom
Thank you very much. I could use it right now (or maybe a cold beer would be better)
regards Tommy

9. Tommy,

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.

Best regards
Gus

[ This Message was edited by: GUS on 2002-03-23 16:04 ]

10. Hi Gus
It's a normal formula to be use in an excel sheet.
What is the problem ??
Which error does A2 return.
It should return FB, if you did remember the leading colon i your own string (well, it does here, but i'm also using a danish version).

regards Tommy

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•