# Working With Data In Binary Format?

Jun 15, 2009
How well does Excel / VBA deal with data in a binary format? I am working on a Base64 decoder, which in basic terms means I will have 4 numbers, which I need to convert into their binary representations (6 bits each). I then need to combine these binary groups together, and then divide them up differently to give me 3 numbers (made of 8 bits each).I'm just wondering if anyone has any examples on how best to work with binary in Excel? Also if there are an gems of builtin library functions?

View 4 Replies
ADVERTISEMENT
Mar 11, 2009

this formula was working great since 12-08, but someone came to me yesterday saying there is a flaw in the formula between Company A and Company B

If you note that 10.125.239.0/24 is overlapped with 10.125.236.0/22, yet is marked FALSE ...

View 9 Replies
View Related
Sep 5, 2009

Is there anyway to convert excel sheet into a file with binary format?

Or maybe export them into binary files?

I want to change my database type to binary from text..

Problem is my database is Huge, it's a 1044x1044 matrix..

The previous .txt database is exported using macro, into 1044 files that contain 1044 values each. I want to do the same except with binary format...

View 7 Replies
View Related
Aug 14, 2013

I am downloading my data in the eze tab. Column b is in a text format. How can I amend my formula on the trading statistcs tab for my sumproduct formula to work?

View 7 Replies
View Related
Jun 9, 2014

I have a list of dates stored via the following :

20100101 how can I convert this to date/time format to show 01/01/2010?

View 4 Replies
View Related
Jul 9, 2014

I have a lot of outputs being spat out in csv files.

I need to get some of the data from set columns and copy this over into a master tracker. Columns are not sequencial and may need to copy change at a later date. Example attached.

Some data fields will have letters in them as well. Some are varying in length in terms of the amount of data within a cell. eg. "AB389238923589Y234HI" or just "A-GT6"

I don't know where to start VBA wise but it must be possible rather than open copy paste.

The tracker has a set name but will change quaterly.

The Output CSV files are new files with a number (no date) for titles.

View 2 Replies
View Related
Dec 10, 2012

How do I graph this data? I am plotting the test results (Column A is the employee number, B is the Employee name, then each of the questions are Columns D through DX with DY being the total correct by each employee) (Rows are employees and then the Ratio that the question was answered correctly) The goal is to find questions that are routinely answered in correct or that have a problem with the question.

I was hoping to graph the correct and incorrect questions so those that are either high frequency correct, incorrect or not answerd identified I can improve the test.

Example of the spreadsheet. I am using =COUNTIF(D3:D15,"1")/12 for the ratio answered correct - this should also see the ones not answered as incorrect. 1 = correct answer 0 = incorrcet null=not answered

NO

Employee Name

Question

1

2

3

[Code]...

How can I graph these results to answer which questions have a high frequency of incorrect answers? I am thinking some kind of scatter plot.

View 5 Replies
View Related
May 28, 2013

I want to create the chart below. I have data that is coded with a binary flag (0 = red, 1 = blue) that is also ordered. For example, the data I would use to hypothetically create the left hand column below looks like this

Binary Flag

Rank

0

1

0

2

1

3

0

4

1

5

1

6

View 1 Replies
View Related
Jul 4, 2013

I've set up a binary matrix, such that I have 421 unique headings repeated twice: once in each column and once in each row (i.e., in the matrix the headings for row 5 and column 5 will trace back to the same description in the column headings and in the row headings).

I need to fill in 1s (case satisfied) or 0s (not satisfied) in this 421X421 matrix. Where my problem lies is in finding the exact row-column cell combination in which I need to enter the 1s. Descriptions that belong to the same group or category will be marked by these 1s in the matrix. For example, if I have descriptions 5, 9, 13, and 17 belonging to the same group I need to enter a 1 in each possible combination of these numbers: {5,9} {5,13} {5,17} {9,13} {9,17} {13,17}. I need a quick formula that will compute all possible combinations for inputted descriptions AND will enter a 1 into the cells, which correspond to these combinations.

View 14 Replies
View Related
Sep 9, 2008

I have a conditional format that I wanted to add another argument to, but although the statement is true, conditional format will not toggle.

My original argument was:

View 14 Replies
View Related
Jul 13, 2006

I've been working on a particular spreadsheet and I've just noticed that

double clicking the format painter will only format one cell. It won't

retain the format to be put into the next selected cell which it should when

its double clicked. The same goes for copy and paste. Normally once you've

copied something you can paste it into a number of cells individually, one

after the other, but after the first paste selection, the paste option is no

longer available. I can't see any settings under options to fix this. What has happened, and how do I fix it? (I'm using Excel 2003 SP1 - is there a SP2?)

View 2 Replies
View Related
Apr 13, 2014

In the attached sheet, one macro is developed which is not working properly. My req is to convert date from dd-mon-yyyy to yyyy/mm/dd format. (23-jan-2010--> 2010/01/23)

If incomplete date is there, then hyphen should place the gap i.e.

For date like APR-2014------> Output should be 2014/04/--

For date like 1998 -----> Output should be 1998/--/--

The below macro works only for row number 11, 15 and 16.

[Code]....

Attached File : date.xlsm

View 5 Replies
View Related
Aug 23, 2013

When I copy or cut, I usually get a dashed border around the cells I have copied (or cut), I can then right-click somewhere to paste, but at some point in time recently this functionality has stopped working. The format painter doesn't work either - the paintbrush icon stays pressed for about half a second, then just goes away and I can't paste my formatting.

An operation I often do in excel is to copy a date from one cell, then highlight a few cells elsewhere and press enter to paste the date into them. I also like to right click on a row and Cut, then past it further down my list to re-order things, but this won't work either.

I don't know what I've done to make it go away. If I restart my computer and open a workbook, then I can copy/paste at least once (maybe a few times) then it stops again.

This is caused by a Skype 'click to call' function, but I don't have that installed here. I have actually just tried installing it, so I could run through the uninstall (thinking there might be debris from something previously), but it made no difference.

View 2 Replies
View Related
Oct 27, 2007

Does anyone know how to convert a 4 digit number to binary in excel? I used the =DEC2BIN formula, but it only works on a 3 digit number that is smaller than 512.

View 9 Replies
View Related
Oct 13, 2008

cell A1 contains the numeric value: 3961.3

cell A2 contains the follwing: ="z equals "&B3-3933.7

which i would expect to give me: "z equals 27.6"

BUT, it gives me: "z equals 27.600000004"

which is awefully strange becos the 4 comes form nowhere...

what makes some sort of sense is that if the value in A1 is changed to 3961.4 or 3961.5, then the final decimal 4 in A2 changes into a 3, and then a 2.

when the value in A1 is changed to xxxx.7 the number in cell A2 becomes what one would expect it to be.

how do i get rid of these strange decimals???

(formatting cells, setting number of significant figures does not work. i naturally already tried that)

View 8 Replies
View Related
Nov 18, 2008

I have 70 sequences of binary coded variables each, which I would like to compare in terms of overlaps for the number "1", e.g.,

a1 1 0 0 0 0 1

a2 0 1 1 1 0 0

a3 0 1 1 0 1 0

.

.

.

a70 0 1 0 0 0 0

How can I do a pairwise comparison in Excel for the number "1" (ie how often does the number "1" occur at the same place for two sequences?).

I thought about a correlation matrix (that compares sequences a1-> a2, a1-> a3, a1->a4, ...., a1-> a70; a2->a3, a2-> a4, ...., a2-> a70; .....; a69-> a70), but am looking for an easy way to compute this in Excel.

View 6 Replies
View Related
Jun 16, 2006

I am running into one of the strangest issues I have seen in a long time. I have a user defined type that I utilize in VB6 and write out to a binary file....

View 7 Replies
View Related
Aug 11, 2008

I was reading this Binary Conversion from Decimal on this site, however i am unable to reply to the thread. This code is exactly what i was looking for but have the need for it to be able to give the result as a number of bits eg. i would like the result to be dispalyed as 13 bits. I have tried modifying the code without any success and would like to be able to use it in this form. Public Function Dec2Base( Num As Long, base As Long, length as long) As String

View 3 Replies
View Related
Feb 7, 2012

I want to convert Hex to Binary. I want the Binary in 8 different cells.The hex input would be lets say A1 I would want to convert that to eight different cells Bit7 G10, Bit6 G12, Bit5 G14, Bit4 G16, Bit3 E10, Bit2 E12, Bit1 E14 and Bit0 E16.

Then I want another Hex input on A2 and convert that to eight diferent cells.

Bit7 K10, Bit6 K12, Bit5 K14, Bit4 K16, Bit3 I10, Bit2 I12, Bit1 I14 and Bit0 I16.

How would I do This?

View 9 Replies
View Related
Apr 9, 2014

I have written a little test sub which reads the contents of a binary file, which is approximately 0.2 Mb in size, into a byte array.

My sub is as follows:

[Code]...

It works just fine, BUT, it is incredibly slow (it spends 80 seconds in the For loop!!!!!)

How can I speed this up?

Note that the binary file can be of arbitrary size.

I tried using a string buffer filled with zeros, and it works!

View 1 Replies
View Related
Jun 9, 2007

I read the thread Converting from Hexadecimal to Binary and what I need to do is convert the following hex number to binary; 9E6799CF. The function in that thread doesn't seem to handle that large of a hex value. Is there anyway of doing that with a HexToBin function, short of chopping it up converting the smaller numbers and recombining it in binary?

View 2 Replies
View Related
Feb 18, 2014

I am taking a decimal degree (longitude) and converting it to binary then to hexadecimal. The value I am using is a Binary Angle 32 bit structure that will be translated into an 4-byte hexadecimal (with two characters per byte i.e. "20" is one byte and results in spaces).

This is what I have:

Input of 359.999999916 (in cell A2)

Cells B2 through AG2 I have converted A2 into binary (using formulas like "=IF(A2-180>0,1,0)" for cell B2, "=IF(A2-180*B2-90>0,1,0)" for cell C2, "=IF(A2-180*B2-90*C2-45>0,1,0)" for cell D2, and so on)

I then concatenated all of the results into one cell using the following formula:

=CONCATENATE(B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2,Z2,AA2,AB2,AC2,AD2,AE2,AF2,AG2)

To get a result of (located in cell A4):

11111111111111111111111111111110

However, when I attempted to use the BIN2HEX conversion on A4, I get a "NUM!" error. Here is the formula that I was using in cell A5:

=BIN2HEX(A4,8)

View 2 Replies
View Related
Mar 17, 2014

I am trying to convert 14 to 15 digits lengthy Binary value to number.

The formula =BIN2DEC(A2) is giving error #NUM

101111100001010

101111100001010

101111100001010

101111000011100

101111000011100

101111000011100

110001000001010

View 2 Replies
View Related
Aug 7, 2013

I have a very large dataset (~5000 points in a 2D array) which I need to plot and fit a linear trendline to. There are 90 subjects each of which has a daily binary event checked over its lifespan. eg:

Day: 1 2 3 4 5 6 7 8 9 10

subj 1 0 1 0 0 0 (dead)

subj 2 1 1 1 0 1 0 1 0 0 0(dead)

subj 3 0 0 1 0(dead)

I simply averaged the number of events per day per subject and plotted them but near the long-lived end of the data the variability gets really high. I have normalized the lifespan for each subject such that instead of the event occurring on day 10, it occurred at 0.2 of its total lifespan (dying on day 50). Now that my data is in this format, I want to create an average for all subjects over the domain 0-1 representing birth-death.

The trouble is that now each subject's domain has different intervals between points (eg. 0.1 for subjects living 10 days and 0.006993 for those living 143 days). How to now space out the cells (somewhat) automatically so I can average the columns? My array is 90 x 150 so doing it manually is not really possible. Or, alternatively, is there a way to create a continuous average rather than a discrete one? Some sort of function that goes through each infinitely small (or even a discrete really tiny interval) point of the domain (0-1) and averages the binary y-value?

View 2 Replies
View Related
Feb 10, 2009

I am trying to do a query against my database using MsQuery, but I am having a problem with it...

One of the tables in my database stores information as Binary (Bits) instead of actual text.... When I create the Query in MsQuery, the text is displayed as desired...but when I send it to Excel that column is not displayed..

Does anyone have any ideas on how I can get it to send the column to Excel?

See attached for what I mean. The Bits column shows in MSQuery, but not in Excel.

View 14 Replies
View Related
Jan 1, 2013

I'm receiving this statement when trying to save:

"One or more formulas in this workbook are longer than the allowed limit of 8192 characters. To avoid the limitation, save the workbook in the Excel 2007 Binary workbook format."

The longest formula is 29 characters long. Even if I add the number of characters in every formula in the workbook, it only adds up to 1664.

View 3 Replies
View Related
Feb 11, 2009

I need to format some climatic data from a weather station into a desired format. There are a lot of cut&paste and transposing involved. I think it will be easy but tedious for anyone trained in VB to write a macro but unfortunately I am not. I've tried recording a macro but it turns out that it is not general enough to deal with all the spreadsheets that I've got.

I've attached a spreadsheet which shows the original format (in sheet 1) and the desired format (in sheet 2). To briefly describe, I only need the temperature data for 8am and 2pm, the rest of the information in sheet 1 is useless to me.

View 2 Replies
View Related
May 1, 2014

I have around 30k data. which is in invalid format.

Ex: 12987654321vinay kk 876543219

32567456789 kkccjhg fo 345678921

I want to convert this into correct format as below with start letter from

MOD, 987654321,, vinay, kk,87654219

MOD,567456789, , kkccjhg, fo,345678921

I want to know which are formulas I have to use to get this info in correct format.

View 1 Replies
View Related
Oct 6, 2006

I have a .txt file created in Notepad and it contains a 4 digit number (Job ID). I am recieving the dreaded "Bad File Mode" (#54) error when writing back the file.

I would like to OPEN it as R/W locked, read it, increment it by 1 and write it back. Am perfectly willing to create the .txt as non-binary if that is causing the issue.

Dim jinChar As String

fileNumber = FreeFile()

jinFP = "JIN.txt"

Open jinFP For Binary Access Read Write Lock Read Write As #fileNumber

Input #fileNumber, jinChar

jobIDNumber = CInt(jinChar)

jobIDNumber = jobIDNumber + 1

jinChar = CStr(jobIDNumber)

Write #1, jinChar

Close #fileNumber

View 4 Replies
View Related
Jan 25, 2010

I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.

Private Sub Worksheet_change(ByVal target As Excel.Range)

If target.Column = 1 Then

ThisRow = target.Row

startRow = 1

i = 1

Set ws = ActiveSheet

maxRow = Cells.SpecialCells(xlLastCell).Row

maxCol = Cells.SpecialCells(xlLastCell).Column

ActiveSheet.UsedRange.Interior.ColorIndex = xlNone

Do While i

View 9 Replies
View Related