Probability of event occurring

[tourgis2000]

Hi everyone,

I am utterly mystified by statistics and so I'm hoping that someone can help me out.

I'm trying to find a calculation to show the probability of an event occurring in the current year based on how often it occurred over the previous seven years. No other date is available. I can also assume that the event would normally take place every three or four years. The data looks like this:

I'm looking for a calculation for the '2010' column. I think that the answers should be:
Acme - 100% (the event consitsently occurs every two years)
Big Corp. - 100% (the event occurs every three years and is overdue)
Stuff Ltd.- 66% (the event occurs every three years and is not due until 2011.)
The interval calculation shown does not work for 'Big Corp.' as you can see because the event occurring in the first year of data available.

Can anyone help?

Many thanks,

Martin

 

[Keys]

If you can assume events happen at certain intervals, you can just do a countif formula - so using the last 7 years data, if event turns up 3 times, you can assume once every 2 years, if it appears 2 times, assume once every 3 years. Then work from the results to get your percentages. That would be the very simple solution to this.

Only relevant if the assumptions you mentioned remain the same and time period doesnt extend - so probably not as robust as you probably need it to be

 

[tourgis2000]

Thank you Keys,

I have used a similar approach to produce some answers but, as you implied, the data period will extend. Any other ideas?

Martin

 

[gardnertoo]

Using an array formula, I was able to calculate the interval between the two most recent events for a given company. If one of those two events happens in violation of the underlying frequency, this will be a bad number for forecasting!! I will try to extend it out to look at ALL occurrences of "Event" to calculate a better number. Of course, a statistician would probably tell us that a sample size of seven is too small, but you gotta work with what you've got, right?

To make the formula easy to build and read, I created a named range to cover the existent data for the Acme company only. That range, named "acme", is defined by the following formula:

The named range "acme" is defined using this formula:

=OFFSET(Sheet1!$B$2,,,1,COUNTIF(Sheet1!$B$1:$P$1,"<="&YEAR(TODAY())))

To use the formula below for other companies, your best bet is probably to replace every instance of the word acme with the formula above, changing the cell reference $B$2 to $B2. Then when you copy the array formula down, that cell reference will increment down the list of companies. Here's what I've come up with. It finds the most recent (LARGE......,1) and the second most recent (LARGE.......,2) instances of the word "Event" in the acme named range, and subtracts their column numbers from each other.

=LARGE((COLUMN(acme)*(acme="Event")),1)-LARGE((COLUMN(acme)*(acme="Event")),2)

Don't forget that as a single-cell array formula, you must confirm it by hitting CONTROL+SHIFT+ENTER, not just ENTER.

 

[gardnertoo]

Here is the result of replacing the "acme" reference with the OFFSET formula:

=LARGE((COLUMN(OFFSET(Sheet1!$B2,,,1,COUNTIF(Sheet1!$B$1:$P$1,"<="&YEAR(TODAY()))))*(OFFSET(Sheet1!$B2,,,1,COUNTIF(Sheet1!$B$1:$P$1,"<="&YEAR(TODAY())))="Event")),1)-LARGE((COLUMN(OFFSET(Sheet1!$B2,,,1,COUNTIF(Sheet1!$B$1:$P$1,"<="&YEAR(TODAY()))))*(OFFSET(Sheet1!$B2,,,1,COUNTIF(Sheet1!$B$1:$P$1,"<="&YEAR(TODAY())))="Event")),2)

Pretty ugly, but it works.

 

[gardnertoo]

Here's a group of cells, using array formulas again, that looks back at further intervals. This will be of more use once you've accumulated several "Event"s per company, of course.

Book1
	A	B	C	D	E	F
16	Years between nth most recent and (n+1)th
17	n=	1	2	3	4	5
18	Acme	2	2	0	0	0
19	Big Corp.	3	0	0	0	0
20	Stuff Ltd	3	0	0	0	0
Sheet1

 

[tourgis2000]

Thank you Gardnertoo,

That was very helpful (and far less circuitous than the solution I had been working on).

This is where I am. Here is the test data:

And this is the output sheet:

I have used a pivot table to produce the yearly figures (hiding some label rows), and your calculation to produce the Largest Interval. Now I need to find a formula for the 'Years since review' cells that have been filled manually. This needs to find the last entry in each row plus the years to the current year (e.g. for Big Stuff it is E5:G5=3 cells. I also need to find the bottom right hand figure from the relevant ranges in the first example in order to complete the last three columns. I guess that combinations of MATCH, MAX, OFFSET etc. will get me there but I am struggling to find ones that work. Can you, or anyone else, point me in the right direction?

Thanks again,

Martin

 

[gardnertoo]

On the output sheet, the fact that you've got the number "1" as the "Yes" result makes this fairly easy. The following array formula will get you there:

=YEAR(TODAY())-MAX((B$1:G$1)*(B4:G4))

 

[tourgis2000]

Fantastic, thank you again. I could have spent days on that!

The final piece of my puzzle is to find the last cell of 'Winner' and 'Budget' by Company. So for 'Winner' I need to get the following results:

Acme - Agency3
Big Stuff - Agency 1
Huge Corp. - Agency 3

I have created dynamic named ranges for the columns, but I am still struggling with a way to pull out these results. Can I ask for help once more?

Martin