DRSteele
Well-known Member
- Joined
- Mar 31, 2015
- Messages
- 2,640
- Office Version
- 365
- Platform
- Windows
Can someone please help me?
I have over 43000 rows of data in an ExcelTable. I need an efficient forumla that determines streaks of zeros & ones and uses Excel Table nomenclature.
In this sample, there are two pieces of data (i.e., wtn high and wtn low) and four corresponding columns (in yellow) that require formulas.
For the streak high and streak low columns, I need this: If the data point is a 0, I want to show a -1 for the streak, and if the subsequent data point is 0, show -2, etc. If the data point is 1,
I have over 43000 rows of data in an ExcelTable. I need an efficient forumla that determines streaks of zeros & ones and uses Excel Table nomenclature.
In this sample, there are two pieces of data (i.e., wtn high and wtn low) and four corresponding columns (in yellow) that require formulas.
For the streak high and streak low columns, I need this: If the data point is a 0, I want to show a -1 for the streak, and if the subsequent data point is 0, show -2, etc. If the data point is 1,
I want to show a 1 for the streak, and if the subsequent data point is 1, show 2, etc.
Then for the sh end and sl end columns, I want to show the maximum value of the current streak.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
<strike>
</strike>[/FONT]
Then for the sh end and sl end columns, I want to show the maximum value of the current streak.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | K | L | N | O | P | Q | |||
7 | date | wtn high | wtn low | streak high | streak low | sh end | sl end | ||
8 | 1/1/1900 | 0 | 0 | -1 | -1 | 0 | 0 | ||
9 | 1/2/1900 | 0 | 0 | -2 | -2 | 0 | 0 | ||
10 | 1/3/1900 | 0 | 0 | -3 | -3 | 0 | 0 | ||
11 | 1/4/1900 | 0 | 0 | -4 | -4 | 0 | -4 | ||
12 | 1/5/1900 | 0 | 1 | -5 | 1 | -5 | 0 | ||
13 | 1/6/1900 | 1 | 1 | 1 | 2 | 1 | 0 | ||
14 | 1/7/1900 | 0 | 1 | -1 | 3 | -1 | 0 | ||
15 | 1/8/1900 | 1 | 1 | 1 | 4 | 0 | 4 | ||
16 | 1/9/1900 | 1 | 0 | 2 | -1 | 0 | -1 | ||
17 | 1/10/1900 | 1 | 1 | 3 | 1 | 0 | 0 | ||
18 | 1/11/1900 | 1 | 1 | 4 | 2 | 0 | 0 | ||
19 | 1/12/1900 | 1 | 1 | 5 | 3 | 0 | 0 | ||
20 | 1/13/1900 | 1 | 1 | 6 | 4 | 0 | 0 | ||
21 | 1/14/1900 | 1 | 1 | 7 | 5 | 0 | 0 | ||
22 | 1/15/1900 | 1 | 1 | 8 | 6 | 0 | 0 | ||
23 | 1/16/1900 | 1 | 1 | 9 | 7 | 0 | 0 | ||
24 | 1/17/1900 | 1 | 1 | 10 | 8 | 0 | 0 | ||
25 | 1/18/1900 | 1 | 1 | 11 | 9 | 0 | 0 | ||
26 | 1/19/1900 | 1 | 1 | 12 | 10 | 0 | 0 | ||
27 | 1/20/1900 | 1 | 1 | 13 | 11 | 0 | 0 | ||
28 | 1/21/1900 | 1 | 1 | 14 | 12 | 0 | 0 | ||
29 | 1/22/1900 | 1 | 1 | 15 | 13 | 0 | 0 | ||
30 | 1/23/1900 | 1 | 1 | 16 | 14 | 0 | 0 | ||
31 | 1/24/1900 | 1 | 1 | 17 | 15 | 0 | 15 | ||
32 | 1/25/1900 | 1 | 0 | 18 | -1 | 18 | -1 | ||
33 | 1/26/1900 | 0 | 1 | -1 | 1 | 0 | 1 | ||
34 | 1/27/1900 | 0 | 0 | -2 | -1 | -2 | 0 | ||
35 | 1/28/1900 | 1 | 0 | 1 | -2 | 0 | -2 | ||
Sheet1 |
</strike>[/FONT]