Okay, here’s a description:
The rows are made up of unique individuals. The columns are week numbers. The data is saying whether a certain event occurred with an individual during a particular week (1 if yes, 0 if no). Each individual might have multiple events during the course of the weeks. I want to calculate the average time between events for each individual. Any idea of how I might do this?
You don’t have enough information, I suspect, because you don’t know how often the event happened twice or more.
The only way I can think of you need Visual Basic (Press alt+f8 to bring up the macro menu) because Excel can’t do loops, and the only solution I can think of uses a nested loop. I don’t really have time to do it now.
You don’t need a loop.
However, can the event happen more than once in the same week? If so, you do need more info than a 1 or 0. You need to know how many times the event occurred in each week. If we assume that the number gives the number of times the event occurs in each week, then in another row use this formula to determine the average number of days between events in row 2:
=COUNT(2:2)*7/SUM(2:2)
This assumes that a column remains blank until there is real data (either a 0 or a number) for that week. If you put zeroes in columns to fill them all up in advance then this won’t work without adding some fancy date arithmetic.
If an individual can have no more than one event per week, then the average number of “weeks” between events is the total number of weeks / total number of events.
Viz., =count(A1:Z1)/sum(A1:Z1)
ETA: Viz., what CWG just said.
That’s not true. The total weeks/total events is the average number of events per week, not the time between them. I think the OP wants to know something like this:
Example 1:
001010100
There were 2 events, each was 2 weeks apart from the last event, the average is 2 weeks.
Example 2:
00100010100
There were 2 events, one was 4 weeks and one was 2 weeks apart from the last event, the average is 3 weeks.
Example 3:
111
Average of 1 week.
In other words, every time there is a “1”, you need to see how many weeks it’s been since the last “1”, and do an average of those events. Unclear how the first “1” would be counted.
etc…
Well seein’ as there’s no answer I’ll give it a try.
There are problems with my code,
It depends how you want to count them. This version only counts the days marked as zeroes. If you want to include the 1 as part of the time between the event you’ll have to add
sumOfGaps=sumOfGaps+1
after the line:
If (ActiveCell.Value = 1) Then
You select the last cell on the right, change thetotalColumns from 6 to whatever you actually have, and it goes backwards to produce the final answer in the left of the last cell.
Whoa. Thanks a lot, spayced-- and everyone else.
I think you can do this without VB or loops. However, you do need 2 additional rows of formuals for each row of data (you can either hide them later of put them into another sheet).
Let’s assume row 1 contains your data (starting in column B).
e.g:
_ 1 0 1 0 0 1 0 0 0 1.
In the next row, you place a 1 in column A (to make sure you start counting at 1). Each other column gets the formula
(for column B) =IF(B2=1,1,A3+1)
(i.e. if the row above is one you start counting at 1 - otherwise you increment by 1). In this example you get
1 1 2 1 2 3 1 2 3 4 1
Now you have to single out the highest number in each sequence. But as a sequence only ends when the next week has an event you just need to check for that. If yes, you copy the number, otherwise you leave it blank (not 0).
For column B: =IF(C2=1,B3,"").
Again, in this example you get
_ _ 2 _ _ 3 _ _ _ 4 _
All you need to do now is take the average over this row (=AVERAGE(B4:K4), here: 3)
Note: This only works for 1/0, not for multiple events in one week
After reading subsequent posts, I think the original question probably needs to be stated more precisely. That happens all the time with Excel questions on discussion boards.
BTW I should have mentioned that my result is in units of days. You can take out the “*7” if you want units of weeks.
I don’t see how this code does anything differen than a formula using COUNT and SUM functions. The code also assumes that there are 10 columns, whereas the COUNT and SUM functions are good no matter how many columns there are or whether they change. I could not figure out what the loop problem is but if I get a spare moment I’ll do some debugging.
BTW when posting code I recommend you use the CODE tag instead of a QUOTE tag, which will preserve your indentation and other spacing.
Works now! Select the rightmost cell, change totalColumns to the correct number and voilá. Would add support to scroll down rows but I’ve spent way too much time with this already.
Although your solution is clever fitzlade it still leaves the problem in which you have arbitrary lengths with no way to get from 1 1 2 1 2 3 1 2 3 4 1 to _ _ 2 _ _ 3 _ _ _ 4 _
If it can be done with functions like SUM and COUNT, I would be even more amazed because I just cannot see how it’s possible. An iteration is not the same as a sum.
Sub AvgTimeBetweenEvnt()
Dim totalColumns As Integer
Dim workingColumn As Integer
totalColumns = 12
workingColumn = totalColumns
Dim totalGaps As Integer
totalGaps = 0
Dim sumOfGaps As Integer
sumOfGaps = 0
Dim break As Boolean
break = False
Do Until workingColumn < 1
workingColumn = workingColumn - 1
If (ActiveCell.Value = 1) Then
If justHadOne = False Then
sumOfGaps = sumOfGaps + 1
totalGaps = totalGaps + 1
End If
justHadOne = True
Do
ActiveCell.Offset(0, -1).Select
If ActiveCell.Value = 0 Then
justHadOne = False
sumOfGaps = sumOfGaps + 1
workingColumn = workingColumn - 1
'ActiveCell.Offset(1, 0).Select
'ActiveCell.Value = workingColumn
'ActiveCell.Offset(-1, 0).Select
Else
ActiveCell.Offset(0, 1).Select
break = True
End If
Loop Until break = True
break = False
End If
ActiveCell.Offset(0, -1).Select
Loop
Dim finalValue As Long
finalValue = (sumOfGaps / totalGaps) * 100
ActiveCell.Value = finalValue / 100
'ActiveCell.Offset(1, 0).Select
'ActiveCell.Value = sumOfGaps
'ActiveCell.Offset(1, 0).Select
'ActiveCell.Value = totalGaps
End Sub
lines with ’ are for debugging purposes and are commented out
Your code appears to be iterating through the data, finding gaps, determining their size, etc., etc. This is rather painstaking.
In general, to determine the average time between events over a time period, you simply divide the time period by the number of events. That is, COUNT/SUM where COUNT gives you the number of weeks and SUM gives you the number of events.
I retract my earlier post. The count/sum method works.
Say that you have a record for four weeks like “Smith, John 1111”. As I understand it, this indicates that at least one event happened to John Smith each week. Here are two scenarios that are consistent with this data:
Event 1, 11:59 PM Sunday week 1
Event 2, 11:59 PM Sunday week 2
Event 3, 11:59 PM Sunday week 3
Event 4, 11:59 PM Sunday week 4
Event 1, 11:58 PM Sunday week 1
Event 2, 11:59 PM Sunday week 1
Event 3, 11:58 PM Sunday week 2
Event 4, 11:59 PM Sunday week 2
Event 5, 11:58 PM Sunday week 3
Event 6, 11:59 PM Sunday week 3
Event 7, 11:58 PM Sunday week 4
Event 8, 11:59 PM Sunday week 4
In the first scenario, the average time between events is one week. In the second, it’s a little closer to half a week. And by adding more events at the end of each week, I can drive the average down pretty far, while still not changing what your spreadsheet would record. I’d love to know how the folks writing VBA solutions think they’re getting around this fundamental lack of information.
I’ve been ignoring that, since they are at the same time I don’t count it because we’re looking for the time between events, and theres no time between those events. I agree that the OP needs to clarify for us on more than one point.
The Count/Sum method gets different answers than mine, & mine match what I have done by hand, so we must be working towards something different. Try them both out, you’ll see.
Suppose there are 10 envelopes. Five have 1 cent each in them, and five have $1000 in them. By that sort of argument, 5 envelopes have nothing in them and can be ignored, so the average amount in the envelopes is $1000.
Or suppose you have an event that occurs every second between midnight and noon, then not at all between noon and midnight. You would say that every event occurs “at the same time” as the preceding event, except for the first occurrence at midnight, so the event happens on an average once every 24 hours, when most people would say the average is once every two seconds.
11:58 and 11:59 are the same time?
Of course not, I understand the problem. I just interpreted the problem to focus on counting only gaps that are not back to back events. I could be wrong but thats just what I thought the question to mean.
OP?
Even if you assume that each week’s event occurs at the same day and time in that week, the problem is still ambiguous. Suppose there are ten weeks and the data are provided as stated by the OP. Then 1010101010 and 0011111000 will yield the same mean time of 2 weeks between events using the simple count/sum method, and clearly this is wrong for the second case. One way to get around this is to ignore all leading and trailing zeros in the binary sequence.
There are other problems with using the mean times between events to compare different individuals. Unless you assume that all individuals have participated in the same or nearly the same number of events, the means could be based on drastically different denominators. And note that this mean cannot even be computed for any individual who was in zero or one event.
With the following assumptions, I think that we’ll have a meaningful problem and solution: (1) each week’s event occurs at the same day and time in that week; (2) drop any individuals with zero or one event; (3) start counting relevant weeks at an individual’s first event, and stop counting relevant weeks at an individual’s last event; and (4) every individual had approximately the same number of events across many weeks.
If these are quantum events we’re talking about, one minute apart is not back to back. I doubt that’s what the OP has in mind, but I don’t know that it isn’t.
Regardless, moving four of those events from 11:58 PM Sunday to 11:59 PM on Wednesday doesn’t change the basic result: there are two scenarios with different average time between events that cannot be differentiated based on the information in the OP’s spreadsheet. And this is not a fluke, because the OP’s spreadsheet contains no information about the time between events. There is no way to compute the average of a set of values that you don’t know and can’t infer.