Forum Discussion
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!
Please check in attached. You need to drag cells from three columns at once.
11 Replies
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.
- 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?).
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.
And I added Conditional Formatting
Please check in attached file.
