You are not registered yet. Please click here to register!


 
 
plc storereviewsdownloads
This board is for PLC Related Q&A ONLY. Please DON'T use it for advertising, etc.
 
Try our online PLC Simulator- FREE.  Click here now to try it.

---------->>>>>Get FREE PLC Programming Tips

New Here? Please read this important info!!!


Go Back   PLCS.net - Interactive Q & A > PLCS.net - Interactive Q & A > LIVE PLC Questions And Answers

Reply
 
Thread Tools Display Modes
Old December 22nd, 2008, 08:09 PM   #1
sparky_289
Member
United States

sparky_289 is offline
 
sparky_289's Avatar
 
Join Date: Jun 2008
Location: Hilmar
Posts: 4
Cool Excel code for breaking values into bit fields

I'm looking for a fast easy Excel code to move whole integer values into separate bit fields. Does anyone know of a relatively easy code?
  Reply With Quote
Old December 22nd, 2008, 08:34 PM   #2
panic mode
Member
Canada

panic mode is offline
 
panic mode's Avatar
 
Join Date: May 2003
Location: Toronto, Canada
Posts: 1,962
you can use DEC2BIN to get binary value, then just extract character or bit of interest using MID
  Reply With Quote
Old December 22nd, 2008, 08:35 PM   #3
kolyur
Lifetime Supporting Member + Moderator
United States

kolyur is offline
 
kolyur's Avatar
 
Join Date: Oct 2004
Location: Wooster, Ohio
Posts: 1,489
This formula should work:
=MID(TEXT(DEC2BIN(123,8),"00000000"),X,1)

where 123 is the number (could be reference to another cell) and in this case 8 is the number of bits you're working with. X is the position of the binary digit you want to extract (counting from the left/MSB, not the right). In order for the DEC2BIN function to work, you need to have the Analysis Toolpak enabled.
  Reply With Quote
Old December 22nd, 2008, 11:20 PM   #4
TConnolly
Lifetime Supporting Member
United States

TConnolly is offline
 
TConnolly's Avatar
 
Join Date: Apr 2005
Location: Salt Lake City
Posts: 6,151
The attached spreadsheet has a little bit of legerdemain in it in the form of a custom function in VBA.

The function is:

Public Function BITAND(x As Long, y As Long)
BITAND = x And y
End Function

and it is located in Module 1 on the VBA sheets. You will need to have Excel security set to allow macros - I recommend medium so that you get a prompt before opening.

With the custom function any integer can be bitwise ANDed with any other. By ANDing it with 2^n, where n is a bit position, we can determine if that bit is TRUE or FALSE, and return the value 1 or 0 with the Excel IF function.

Just another way of doing it without using TEXT functions. It handles unsigned integers.

Also there is a useful tool called LL-SOLVER that is a free download, you can get it from the dowload section at MRPLC, which is useful for converting and viewing 16 bit integers in hex, decimal, and binary - much simpler than EXCEL for the occasional quick binary conversion.

http://forums.mrplc.com/index.php?download=606
Attached Files
File Type: xls EXCELBITS.xls (21.0 KB, 265 views)
__________________
True craftsmanship is only one more power tool away.

That's the beauty of processors, they don't have emotions they just run code - The PLC Kid.

Last edited by TConnolly; December 22nd, 2008 at 11:24 PM.
  Reply With Quote
Old December 23rd, 2008, 11:34 AM   #5
sparky_289
Member
United States

sparky_289 is offline
 
sparky_289's Avatar
 
Join Date: Jun 2008
Location: Hilmar
Posts: 4
This is a great code. Thanks!
  Reply With Quote
Old December 23rd, 2008, 12:28 PM   #6
trishark
Member
United States

trishark is offline
 
Join Date: Dec 2008
Location: Houston
Posts: 3
A different approach - no vba

abcdefghijklmnopq1bit151413121110987654321023276816384819240962048102451225612864321684213111111111111111111111111878787872323773104=if(b6=1,a3-b2,a3)56=c3,1,0)">0=d3,1,0)">0=e3,1,0)">0=f3,1,0)">0=g3,1,0)">0=h3,1,0)">1=i3,1,0)">0=j3,1,0)">0=k3,1,0)">0=l3,1,0)">1=m3,1,0)">0=n3,1,0)">1=o3,1,0)">0=p3,1,0)">1=q3,1,0)">1=r3,1,0)">17=b2,1,0)">=if(a3>=b2,1,0)


formula listed below the start of cell/row so visible in this copy. Formulas copied across b3 thru q3 and b6 thru q3. Number to parse goes in a3, row 6 gives bits. Formulas could probably be combined and row 2 numbers hardcoded into formula if you need to get to one line.

Mike
  Reply With Quote
Old December 23rd, 2008, 12:29 PM   #7
trishark
Member
United States

trishark is offline
 
Join Date: Dec 2008
Location: Houston
Posts: 3
Well, copy from excel did not work at all. Will attach file.
  Reply With Quote
Old December 23rd, 2008, 12:33 PM   #8
trishark
Member
United States

trishark is offline
 
Join Date: Dec 2008
Location: Houston
Posts: 3
another try

another way to skin the cat
Attached Files
File Type: xls bits.xls (14.0 KB, 153 views)
  Reply With Quote
Old December 23rd, 2008, 01:44 PM   #9
zova
Member
Russian Federation

zova is offline
 
zova's Avatar
 
Join Date: Jan 2006
Location: Russia, Komi
Posts: 116
And this is my code which I use for a long time.


Code:
Function KM(ByVal H As Long, L As Long) As String
' this function returns binary sequence as string
' H - input integer value
' L - length of returned string 
Dim k As Long
Dim E As Long
KM = "B"
1:
    E = H Mod 2
        If E = 0 Then
            KM = "0" + KM
        Else
            KM = "1" + KM
        End If
        
    H = Int(CDbl(H) / 2)
    If H >= 1 Then GoTo 1
    
k = 1 + L - Len(KM)
While k <> 0
KM = "0" + KM
k = k - 1
Wend
    
End Function
I was late a little, but I will be glad if it be useful for someone.
  Reply With Quote
Old December 23rd, 2008, 01:59 PM   #10
zova
Member
Russian Federation

zova is offline
 
zova's Avatar
 
Join Date: Jan 2006
Location: Russia, Komi
Posts: 116
this is complete Excel book with function above

ps - I'm sorry for russian comments
Attached Files
File Type: zip MOSCA_decoding.zip (63.8 KB, 72 views)
  Reply With Quote
Old December 23rd, 2008, 04:49 PM   #11
TConnolly
Lifetime Supporting Member
United States

TConnolly is offline
 
TConnolly's Avatar
 
Join Date: Apr 2005
Location: Salt Lake City
Posts: 6,151
So far we have 100 ways to skin that cat, need to make it 101 just to be cliche.
__________________
True craftsmanship is only one more power tool away.

That's the beauty of processors, they don't have emotions they just run code - The PLC Kid.
  Reply With Quote
Old December 24th, 2008, 07:27 AM   #12
henry5674
Member
United Kingdom

henry5674 is offline
 
Join Date: Jun 2006
Location: swindon
Posts: 40
Some more skinned cats

I normally use the ^ (power of) operator when decoding binary to/from decimal. There are three functions on the sheet, Decimal to binary,Binary to decimal and a function to check a bit status in a word.
Attached Files
File Type: xls BitCode.xls (32.0 KB, 173 views)
  Reply With Quote
Reply
Jump to Live PLC Question and Answer Forum

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Topics
Thread Thread Starter Forum Replies Last Post
Alarm Code mwatkins LIVE PLC Questions And Answers 27 October 15th, 2019 03:44 AM
Automatin Direct Changes Download ArikBY LIVE PLC Questions And Answers 53 May 12th, 2004 01:05 AM
Internal addresses rogerhollingsworth LIVE PLC Questions And Answers 5 August 10th, 2003 07:02 PM
Looping VBA code in Excel pagewe LIVE PLC Questions And Answers 8 February 10th, 2003 11:19 AM
Auto generate code in Excel? PhilRey LIVE PLC Questions And Answers 9 January 17th, 2003 07:32 AM


All times are GMT -4. The time now is 04:35 PM.


.