Compare The Binary Sequences
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
ADVERTISEMENT
Sep 7, 2009
I am doing research and I have a list of over 100 small dna sequences that I would like to see if there are any similarities between them. I have put them in individual cells but I dont know how or what function to use.
View 11 Replies
View Related
Jan 12, 2009
I am using excel 2007 and I need help with the following if anyone would be kind enough. I have a dynamic array that consists of stream of 1's and 0's. I want to be able to find the largest sequence of 1's and the smallest sequence of 1's. So at a point in time the array mite be 111110001101111000 and I want to be able to dertermine the longest contiguous sequence of 1's. But as I say the array is dynamic and so I need to do that after each input.
View 8 Replies
View Related
Jul 18, 2006
I need to create a warning when specific values sequences are input from a
drop down menu. My example:
I have 31 columns (days in month) and have 25 rows (peoples names) from the
drop down box I have the following values-These are shift designations
(V1,V2,V3,V4, SV1,SV2,SV3,SV4, A1,A2,A3,A4,N,R1,R2,R3,DB,C).
If the selections in cell between B1:AF25 are either SV 1-4 or A 1-4 I want
a warning to appear IF a value of V 1-4, R 1-3, DB or C are attempted to be
put in the immediately following cell.
Example: Cell B3 has A-4 and in cel C3 they attempt to put N, then it
should give a warning "This is not a recommended shift sequence".
View 11 Replies
View Related
Apr 1, 2007
I want to write a formula that counts sequences.
Details of Data:
Imagine you roll a 4-sided dice! The four sides of the dice are 1,2,3 and 0.
Sequence needs to be 5 in a row of either 1 and 2, 1 and 3 or 2 and 3. The 0 is to be recorded, but 'skipped over' in the sequence.
When a sequence of 5 occurs, there needs to be a count to record how many more times the missing number takes until it arrives. For example, if the sequence of 5 is 1 and 2, and the count will be to see how long it takes the number 3 to arrive.
View 9 Replies
View Related
May 8, 2014
I need to come up with a formula that will lookup a value in a table based upon a specified number sequence in another table and then add that value to another number/price that corresponds with that specified number sequence.
In the attached worksheet you will see that there are two groups of tables (A & B). I want to take the prices in group A in columns B:E and then add the values at the top of the page in columns G:J based upon specific numbers to the right of the decimal place in column A under "Rate".
So in the example at the bottom you will see that the first rate in column A is 5.125. Since the rate ends in .125 then it should be using the value of 2.57 from J2. So it should take the price in B27 (108.328) and add 2.57 to give you 110.898 in M27.
My problem is that the rate sequence wont always be the same and neither will the values in columns G:J. The only constants are that the rates will end in 1/8ths and the values will be based upon which 1/8ths they end in. For instance; In the example the rate range goes from 5.125 down to 3.75 but sometimes it may be 5.625 down to 4.25.
View 12 Replies
View Related
Mar 18, 2009
how to go about finding sequences and highlighting them.
here's an example list:
7600
7601
7602
7603
7604
7608
7609
7610
7611
now i need to find 3 things in this list.
1) how many 'batches' of sequences there are in this list. The numbers 7600 to 7604 are a consecutive sequence of five numbers followed by another sequence from 7608 to 7611. that makes two sequences. i need a formula for one cell that would specify how many sequences there are in a given list (in this case they're two sequences in this list).
2) is there any formula that can allow me to see each sequence highlighted in a different color (or any way to differentiate the sequences so i can copy paste them easily?). Pretty sure i'd have to use conditional formating on the entire list to do so but am clueless as to the implementation.
3) a formula for a single cell that can state the largest sequence in the list. in the list above the largest seuqnce is 5 numbers so that would be the value i'm looking for.
View 9 Replies
View Related
Oct 22, 2013
Worksheet cells A1 – A25 have sequential numbers 1 – 24
the following elegant code generates every permutation of six numbers from the list into Cols C – H
VB:
Option Explicit
Sub Combinations()
Dim rRng As Range, p
[Code].....
View 7 Replies
View Related
Nov 5, 2008
I have a spreadsheet tool that I use to calculate values on a set of variables, based on values on an initial set of other variables, for a large number of cases.
The calculations require a number of sequential steps to be repeated to get the calculated values for each case, and I am hoping there is a straightforward way to use a VB macro to automate this process.
Essentially, these steps are required to calculate values for a single case:
1. A data input sheet holds the values on the initial/input variables for each case. The first step involves copying the string of values for the case on the input variables into a sheet where the calculations are done.
2. As soon as the input string of values are pasted into the calculation sheet, the calculation formulas generate values for the case on the new, output variables.
3. The output variable string is then copied from the calculation sheet into a “results output” sheet. This copy/paste procedure needs to offset from the first row on the output sheet, so that the row number where the values on the output variables are stored correspond to the case ID. For example, case ID 10 is stored 10 rows down from the first row (i.e., on row 11). Case ID 100 is stored 100 rows down from the first row (i.e., on row 101).
As there are a very large number of cases, I need to have a “control panel” in place, where I can indicate the range of case IDs that will be processed. This takes the form of two cells – one indicates “From ID” and the other “To ID” (for example, “From ID” 1 “To ID” 100). The macro then uses this information to loop through the calculation steps outlined above for these case IDs.
View 4 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
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
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 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
View Related
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
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
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
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
May 28, 2009
I have the following two formula's:
=IFERROR(IF(X12="",X13&" "&MATCH(FALSE,INDEX(X13=X13:X1000,0),0)-1,X12&" "&MATCH(FALSE,INDEX(X12=X12:X1000,0),0)-1),"")
=MAX(FREQUENCY(IF(INDEX(X:X,12):INDEX(X:X,1000)="WON",ROW(INDEX(X:X,12):INDEX(X:X,1000))),IF(INDEX(X:X,12):INDEX(X:X,1000)="LOST",ROW(INDEX(X:X,12):INDEX(X:X,100)))))
The first shows the current sequence of events (WON or LOST) and the second the best sequence achieved.
They work fine but I would like to modify them to only use the data, if "P" is in column D of that row.
View 9 Replies
View Related
Feb 21, 2014
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx‎
View 3 Replies
View Related
