DRSteele
Well-known Member
- Joined
- Mar 31, 2015
- Messages
- 2,640
- Office Version
- 365
- Platform
- Windows
Can someone please help me?
I wish to calculate the number of occurrences for each trader where the streak of the declared number of trades (I used the variable 5 here) resulted in a winning percentage greater than the hurdle rate (I used 60% here). So for Dan, there was only one time (transactions 1 through 5) where any string of 5 trades resulted in at least 60% of the points available (which is 5x2=10 pts). Cam succeeded in all 16 of the possible 5-transaction strings.
I manually reckoned the answers in ColumnV by selecting every possible string of 5 transactions for each trader and then determining from the status bar at the bottom of Excel how many strings summed to 6 or greater. But I would really like a single formula in ColumnV to report the number of successes for each trader; in this example that number is between 0 and 16.
<tbody>
</tbody>
I wish to calculate the number of occurrences for each trader where the streak of the declared number of trades (I used the variable 5 here) resulted in a winning percentage greater than the hurdle rate (I used 60% here). So for Dan, there was only one time (transactions 1 through 5) where any string of 5 trades resulted in at least 60% of the points available (which is 5x2=10 pts). Cam succeeded in all 16 of the possible 5-transaction strings.
I manually reckoned the answers in ColumnV by selecting every possible string of 5 transactions for each trader and then determining from the status bar at the bottom of Excel how many strings summed to 6 or greater. But I would really like a single formula in ColumnV to report the number of successes for each trader; in this example that number is between 0 and 16.
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | trader // transaction | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | surpass hurdle? |
2 | abe | 1 | 1 | 1 | 2 | 0 | 2 | 1 | 0 | 0 | 1 | 1 | 2 | 1 | 0 | 2 | 2 | 2 | 1 | 2 | 0 | yes, 8 times |
3 | bob | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 1 | 2 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 2 | 0 | 2 | no |
4 | cam | 2 | 2 | 1 | 2 | 2 | 2 | 2 | 1 | 2 | 0 | 2 | 2 | 2 | 2 | 1 | 1 | 2 | 2 | 2 | 1 | yes, all 16 times |
5 | dan | 1 | 1 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | yes, 1 time |
6 | edd | 2 | 0 | 2 | 0 | 1 | 0 | 2 | 1 | 1 | 2 | 0 | 1 | 0 | 2 | 1 | 0 | 2 | 2 | 1 | 2 | yes, 4 times |
7 | ||||||||||||||||||||||
8 | hurdle | 60% | ||||||||||||||||||||
9 | for trades | 5 | ||||||||||||||||||||
10 | max points per trade | 2 |
<tbody>
</tbody>
Sheet14
Last edited: