SOLVED

# Formula to see how often and how long something happens?

Occasional Contributor

# Formula to see how often and how long something happens?

Hi!

Does anybody know a formula to see how often an episode happens and how often it lasts?

Suppose I have the GDP for Australia for the years 1870-1892.

I want to get a formula that would help me answer the following questions:

• How many episodes there were when Australia was growing faster than 3% annually? As an episode, I understand how many consecutive years the GBP was growing more than 3% annually.

• For each episode, how many years was Australia growing faster than 3% annually?

• For each episode, what was the average growth in a given period?

To demonstrate what I want to achieve please see the below.

I know how to easily get the total number of years that Australia was growing faster than 3% annually. I have filtered such years in green with the value "1" (column F).

But what I don't know is how to get excel to identify from column F the number of episodes/ how long was the episode in column G (in yellow) and how to get the average growth in such an episode in column H (in orange).

For instance, the first such episode, when Australia was growing faster than 3% annually was between 1872-1876. This episode lasted 5 years (yellow) and during this episode, the average growth was 8.1% (orange).

I am looking for a formula/ set of forms that will allow me to do this for a very long time series from multiple countries rather than having to do this manually.

Sample of the data I am using is attached below. My Msc Excel version is 2016.

Thank you!

11 Replies

# Re: Formula to see how often and how long something happens?

I'd try to do that with PivotTable and DAX measures. Perhaps could be done with formulas as well, but here are couple of questions

- on which version of Excel you are, does it support dynamic arrays or not?

- better if you submit sample Excel file, at least with this part which is on screenshot. That's too boring to retype numbers from the screenshot into new Excel book.

# Re: Formula to see how often and how long something happens?

Thank you. I attached the sample I am working on. My Ex version is 2016. I do not know if it supports dynamic arrays (how can I check this?).

# Re: Formula to see how often and how long something happens?

Thank you for the file, will check. Nope, 2016 doesn't support dynamic arrays.

# Re: Formula to see how often and how long something happens?

Here

formulas could be

``````in F3:
=IF(E3>0.03,1,"")

in G3:
=IF((\$F3=1)*(\$F2=""),MATCH("",\$F3:\$F\$149,0)-1,"")

in H3:
=IF(\$G3<>"",AVERAGE(OFFSET(\$E3,,,\$G3)),"")``````

and drag them down till end of the range.

# Re: Formula to see how often and how long something happens?

Dear Sergei.

This is amazing thank you very much.

However, when I tried to drag the formulas to my larger data set it doesn't after Belgium - do I need to add something into the formula to make it work?

best response confirmed by DanathanPL89 (Occasional Contributor)
Solution

# Re: Formula to see how often and how long something happens?

Please check in attached. You need to drag cells from three columns at once.

# Re: Formula to see how often and how long something happens?

Dear Sergei.

Thank you very much for this. It is extremely helpful! These formulas look like some black magic!

I was wondering if you could help with the next step of this.

Suppose, I would like to know, how often a banking crisis happened in a country (I have the data, we are concerned about the beginning of the banking crisis not how many years it lasted so we assume just one year) in relation to the occurrence of such episodes when GDP growth was more than 3% per year.

Specifically, I would like to know if:

1. A banking crisis happened during the first year of such an episode of 3% growth.
2. A banking crisis happened during the last year of such an episode of 3% growth.
3. A banking crisis happened during an episode of 3% growth (not the first year not the last year).
4. A banking crisis happened the very next year after the episode of a 3% growth.

I populated this with examples of banking crises for Australia (fictional data).

# Re: Formula to see how often and how long something happens?

Could you please clarify why in your sample you marked as first crisis end of first episode, not it's beginning, and same for other columns.

# Re: Formula to see how often and how long something happens?

The results you are looking for can all be found by applying the same type of conditional formulae that Sergei already laid out. It's a closed set of conditions so all that is needed is a logical test on each data point of concern - eg:
if GDP in year x < .03 AND GDP in year (x +1) >= .03
that will indicate your first year of 3% growth, to find the other scenarios just modify your conditions as needed. Put the results as a Boolean (1 for true) as new columnar data in the time series and the "how often" question is the sum of each column.

# Re: Formula to see how often and how long something happens?

Dear Sergei,

thank you for looking into this.

For instance, please look at the first case below.

The episode when GDP was above 3% was first between 1872-1876. The first crisis happened in 1877 (Column J). This is the next year the episode of 3% growth ended.

Therefore, from the columns L-M-N-O, I would like "1" to be at 1877 under Column O. The logic is that I first look at when the crisis happened (Arrow A). Then I look how does it corresponds to the conditions at columns L-M-N-O (that is Arrow B). And this then allocates the "1" to Column O.

Similar rational I present for the remaining Columns below.

# Re: Formula to see how often and how long something happens?

Thank you for the explanation. But when next numbers( 1 in column J in rows 1892,1897,1899,1901) are not clear. Is next sequence of crises (for next years)  repeats this logic or not?