## Thursday, February 10, 2011

I have three columns of data. They are percentages of winning stock trades for a particular set of stocks
over three days. I am trying to calculate the number that reaches more than 2% at any time over the three
days. That means that if one goes over 2% on day one, but falls below 2% on day two, the total for day 2 should
still include that stock since it did exceed the limit at one point. The same is true for day 3, if a stock exceeds
2% on day 1 or day 2, but falls below 2% on day 3, I still want to count it in day 3 as a win. The link below shows the data and my current query to count the ones that have exceeded 2% on any particular day:

my formula in cell F16 is ...
=ArrayFormula(counta(iferror(filter(\$B4:\$B14,(\$F4:\$F14>2%)+(column()>=8)*(\$H4:\$H14>2%)+(column()>=10)*(\$J4:\$J14>2%)))))/counta(\$B4:\$B14)

or

=ArrayFormula(counta(iferror(filter(\$B4:\$B14,(\$F4:\$F14>2%)+(column()>=columns(\$A:\$H))*(\$H4:\$H14>2%)+(column()>=columns(\$A:\$J))*(\$J4:\$J14>2%)))))/counta(\$B4:\$B14)

formula in cell F16 is then copied to cells H16. and J16