Does anyone know how to use excel to calculate Cheksum

GUS · Mar 22nd, 2002, 07:17 PM

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

GUS · Mar 22nd, 2002, 07:57 PM

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?

Tom Schreiner · Mar 22nd, 2002, 08:22 PM

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

GUS · Mar 22nd, 2002, 08:40 PM

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

Sincerely
GUS

Al Chara · Mar 22nd, 2002, 08:48 PM

Not sure excel was built for checksum.

Tommy Bak · Mar 23rd, 2002, 05:27 AM

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

Tom Schreiner · Mar 23rd, 2002, 09:19 AM

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

Tommy Bak · Mar 23rd, 2002, 10:25 AM

Hi TsTom
Thank you very much. I could use it right now

(or maybe a cold beer would be better)
regards Tommy

GUS · Mar 23rd, 2002, 04:15 PM

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 ]

Tommy Bak · Mar 24th, 2002, 07:05 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 22nd, 2002, 07:17 PM	#1
GUS New Member Join Date: Mar 2002 Posts: 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

Mar 22nd, 2002, 07:57 PM	#2
GUS New Member Join Date: Mar 2002 Posts: 20	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?

Mar 22nd, 2002, 08:22 PM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Sorry, If I had a better understanding of solving the equations, I could probably solve your problem...

Mar 22nd, 2002, 08:40 PM	#4
GUS New Member Join Date: Mar 2002 Posts: 20	Thank you TOM the magic of willingness is truly appreciated... Sincerely GUS

Mar 22nd, 2002, 08:48 PM	#5
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Not sure excel was built for checksum.

Mar 23rd, 2002, 05:27 AM	#6
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	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)))

Mar 23rd, 2002, 09:19 AM	#7
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Tommy, I may need to E-Mail you an aspirin for writing that one.

Mar 23rd, 2002, 10:25 AM	#8
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	Hi TsTom Thank you very much. I could use it right now (or maybe a cold beer would be better) regards Tommy

Mar 23rd, 2002, 04:15 PM	#9
GUS New Member Join Date: Mar 2002 Posts: 20	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 ]

Mar 24th, 2002, 07:05 AM	#10
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	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