Forum Discussion
Formula to see how often and how long something happens?
Please check in attached. You need to drag cells from three columns at once.
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.
- 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?
https://wetransfer.com/downloads/9f27e1d553e5edd97560880045df4edc20210320141130/a4628fb90e76dfc830db067979255bb120210320141147/ec68aePlease check in attached. You need to drag cells from three columns at once.
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).