Thursday, February 10, 2011

yogi_ComputeWinningStockTrades


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com





This is what gmadrone asked for ...
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