Forum Discussion
Solved
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...
Please check in attached. You need to drag cells from three columns at once.
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.
- 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.
Thank you for the file, will check. Nope, 2016 doesn't support dynamic arrays.