Averaging Binary Values Over Continuous / Non-Uniform Domain
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
ADVERTISEMENT
Apr 9, 2014
I have two data columns: the first is a list of the last 100 calendar dates, and the second is an ON/OFF (or 1/0 if you prefer) value corresponding to each date. If the last value in the second column list is ON, I want to find how many continuous days the value has been ON for. If the last value in the second column is OFF, I want it to return a zero. Note that in the 100 day span of data, the value may switch between ON and OFF several times, but I only want the continuous, uninterrupted ON days at the end of the date range.
View 8 Replies
View Related
Dec 9, 2013
In an employee attendance file I am trying to count the number of times an employee has taken 3 or more days of leave together (continuously) in a month. My attendance file looks something like this
sat
sun
mon
tue
wed
thus
fri
[Code] ..........
In the example above E001 has taken 3 continuous days leave twice so formula should return 2, for E002 & E003 the answer would be 1 each.
View 4 Replies
View Related
Nov 13, 2007
Without using VB is there a single cell solution to this?
Column of cells contain values in this format;
100, 100, 95, 96
as text.
There can be any number of commas and values. Is there a way to find the average of the numbers in each cell? (Above example would return 97.75.)
View 9 Replies
View Related
Feb 26, 2008
Are test have some text values RA = 0 and A= 0.5 as well as numeric values. I am currently using the formula =IF(COUNTIF(A1:A13,"A"),(COUNTIF(A1:A13,"A")*0.5)+SUM(A1:A13))/COUNTA(A1:A13) which is not very good at all, but it works for this instance. I also use the formula in B2 =IF(A1="RA",0,IF(A1="A",0.5,A1)) as a helper column but I need a stabile formula with out a helper column that will allow me to average text and numeric values. In the future, they are going to expand the text values part i.e. B = 0.3, BD = 0.2 etc....
View 9 Replies
View Related
Dec 18, 2013
I am a teacher creating a grade book in Excel. Each student is in a single row, with columns for the various assignments. For data analysis purposes, I am trying to develop formulas to make two different calculations.
The first calculation is simply the average quiz grade for all students, for all quizzes.
The second calculation is the average quiz grade for all grades that are higher than zero. This will tell me the average quiz score for quizzes that have actually been attempted.
I do need to track students that have withdrawn from the class, so column A will contain the letter "w" if the student has withdrawn from the class. If the student has withdrawn, the grades for that student should be ignored in the calculations.
Since I do accept quizzes after their due dates, a particular quiz score will have an entry of "L" if it has not yet been taken. These entries also need to be ignored in the calculations.
See the attached workbook for some sample data and the expected results.
Sample Workbook.xlsx
View 4 Replies
View Related
Jan 27, 2009
I have a string variable which is dynamically populated with a URL value. Some examples of the URL are:
http://cartoondoor.blogbus.com/index_5.html
http://asirir.blogspot.com/
http://neuronets.chat.ru/appendix.html
I'd like to be able to extract the domain--meaning the string value between // and /.
View 2 Replies
View Related
Aug 6, 2014
Is it possible to perform average function on subsequent group of rows and make a new set of rows ?
For example: As below, in column 1 the average of values of first 3 rows (i.e, 1, 2 & 3) is 2. similarly average of values in next 3 rows (i.e, 4,5 & 6) is 5 and so on....
Is this possible to get a new set of rows by averaging values of rows from a particular column. without applying average formula in each row of column 2. i m using MS Excel 13.
Column 1 ______ Column 2
1 _____________ 2
2 ______________ 5
3 ______________ 8
4 _____________ 11
5
6
7
8
9
10
11
12
View 4 Replies
View Related
Jan 14, 2014
I've got daily water level data that spans around one hundred years. I am looking for monthly average water level data, but rather than using the whole month, I'd like to average the first two days and the last two days. I want to have a single value for each month in the 100 years of data.
If the data was smaller I could do this by hand, however having 100 years worth of data = 1200 months = so many days...
It's tough since months don't have a set number of days, and then leap years and it all gets so complicated. I've never really had a problem where date was a criteria to pull values from, and I don't know how to work with this. The internet suggest the Averageif function which I have dabbled with, but no dice.
View 1 Replies
View Related
Feb 23, 2014
I have a long url and I need only the root domain in the url.
for example:
[URL] .....
[URL] .....
[URL] .....
[URL] .....
[URL] .....
View 2 Replies
View Related
Dec 25, 2012
I am seeking to analyse loading cycles on a beam.
I have half hourly data for five years, in two columns A: Date and B: Load in Kg. The load on the beam is not uniform over time and shows a cycle of load.
In Column C I have an AVERAGE function that returns the average load based in the 4 hour period (2 hours backward looking and 2 hours forward looking) if it is less than 50% of the maximum load.
The data in Column C returns sets of continuous vertical cells of data showing periods of low loading and empty cells where the load is greater than 50%. The sets can be as small as one cell (i.e. one hour) to a set of contiguous vertical cells for one week of low load.
My objective is to use Excel to analyse all the sets of contiguous cells in Column C to determine the time period from the first cell of each set to the last cell of that set and drop the result into another work sheet (pivot table) to further analyse these results.
View 5 Replies
View Related
Sep 24, 2007
I need to sort email address list by domain name instead of just ascending and decending,
View 9 Replies
View Related
Dec 2, 2011
I have one shared book having different tabs from my team member.
in every tabs i got Dates written in various forms like,,,,2/12/2011, 12/2/2011, 12-2-2011 etc......
I want to get uniform date format from all the tabs at once.
View 6 Replies
View Related
Nov 4, 2009
I am getting a #name? error in certain cells for a file that is located on our domain controller. This file will work only on certain computers. I did not make the file, but since it works on certain computers, I can only assume that it is not a function error. We are running Office 2003.
View 2 Replies
View Related
Jun 25, 2012
I'm looking to generate correlated random unifrom numbers between 0 and 1 using the excel random number generator, rand(), and a user defined correlation matrix - without the use of a plugin. I realise there are plenty of risk plugins I could use, but was looking for something excel/vba reliant so I could distribute it without the user having to install anything.
I've tried using a cholesky matrix, but most of time it resulted in one of the numbers being 1, and higher that all the others.
View 2 Replies
View Related
Nov 28, 2013
I am a novice user and not very familiar with Macros or VBA. I took a macro I found on contextures and got it to resize comments that already exist. However, I am trying to get it to also create the comment (with no content) if the comment does not already exist, and then resize it. This is what I have so far:
Code:
Sub ResizeCommentsInSelection()
Dim mycell As Range
Dim myRng As Range
Dim lArea As Long
Set myRng = Selection
[code].....
View 2 Replies
View Related
Feb 27, 2013
I have a spreadsheet with two columns - one with names, the other with values. In some cases, the values column alongside a name is blank. Is there a formula-driven method (not a pivot table) that could produce a separate list of only the names that have a value in the value column? The formula needs to be dynamic, not produced via a filter.
View 7 Replies
View Related
Mar 10, 2014
I have a set of data with daily total returns (TR) for 218 companies on the FTSE350 between 2007-2013. The Data looks as follows:
Company ..............Date(daymonthyear)..... TR
1........................... 112007 ..........................10.4
1 .......................... 212007 ..........................10.2
1 .
1 . .
1 .......................... 30102013 .........................12.3
. . . .
. . .
. . .
2............................112007 .......................... 4.5
2 ............................212007 ..........................4.6
2 .
. .
. .
Etc.
. .
. .
218.......................30102013 .....................13.2
I want to perform a dickey fuller test to see if there is a unit root, but for this I can’t have repeated time values, thus I need to create a daily average TR for each day (Thus summing up all TR values for all companies that have data on TR available on that day and averaging them).
How in excel can create an average value for TR every time a series of numbers in the “date” column take the same value? The best thing possible would then be if this could appear so that I have a data set that looks like
Date (daymonthyear)........ TR
112007 ..............................13.2
.
.
.
.
30102013............................ 9.4
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
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
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 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