Does anyone know how to use excel to calculate Cheksum

GUS

New Member
Joined
Mar 7, 2002
Messages
20
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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?
 
Upvote 0
Sorry,
If I had a better understanding of solving the equations, I could probably solve your problem...
 
Upvote 0
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)))
 
Upvote 0
Hi TsTom
Thank you very much. I could use it right now :) (or maybe a cold beer would be better)
regards Tommy
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top