Does anyone know how to use excel to calculate Cheksum
Does anyone know how to use excel to calculate Cheksum
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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. #2
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


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

    Sincerely
    GUS

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not sure excel was built for checksum.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com