Running Tally of Consecutive Yeses and Nos. (Where minus 3, plus 1 = +1)

[ThanksAlot]

I am trying to build a formula that will register the number of consecutive days a given task is either completed or not completed. The most recent cell (C1) should show a positive or negative value. A positive value will be the number of consecutive days the task wasc ompleted. A negative value will be the number of consecutive days the task was not completed.

As an example, I want to track the consecutive number of days that Joe went to work and stayed on the job at least 7 hours.

Holidays and other off days are disregarded. Every day he does go to work, Joe can work any numbers of hours he wishes. The only calculations desired is the number of consecutive days that he did and did notwork at least 7 hours.

Working fewer than seven hours adds minus (-) 1 to the table. Working 7, 8, 9, 10, etc. adds +1 to the table.

Assuming that every day is a work day, if Joe worked Mon, Tue, Wed, Thurs, Fri, Sat, Sun, etc., and he worked 7 hours on Monday, cell C1 will = +1. Working 8 hours on Tuesday also = +1, and it is added to cell C1 for a running total of +2. Joe works 10 hours on Wed, so cell C1 = +3.<o></o>

Joe worked 1 hour on Thurs, and cell C1 now = minus (-)1. He worked 6 hours on Fri, and cell C1now = minus (-) 2. Joe worked 4 hours on Saturday, and so cell C1= minus (-) 3.

Joe worked 9 hours on Sunday, and cell C1 is back to+1, and so on.<o></o>

The data is copied and pasted into the table. Column A shows the running number of daysworked. Column B shows the total numberof hours worked year to date. <o></o>
___<o></o>
I don’t know if the following helps, or not.<o></o>

IF Today's Cell A1 (TA1) is greater thanYesterday's Cell A1 (YA1)…and…<o></o>

If Today's Cell B1 (TB1) is greater thanYesterday's Cell B1 (YB1)…where…<o></o>

Cell TB1 = YB1+ 1, or YB1 + 2, or YB1 + any no. greaterthan zero = YB1 + 1…and…

Yesterday's Cell B1 (YB1) = any negative number…then…

Today’s Cell C1 = +1<o></o>

Continuing…<o></o>

If Today's Cell A1 (TA1) is greater than Yesterday'sCell A1 (YA1)…and…

If Today's Cell B1 (TB1) is greater thanYesterday's Cell B1 (YB1)…where…
<o></o>
Cell TB1 = YB1+ 1, or YB1 + 2, or YB1 + any numbergreater than zero = YB1 + 1…and…

Yesterday's Cell B1 (YB1) = any positive number…then…

Then Today’s Cell C1 = YB1 + 1<o></o>
<o></o>

ThanksAlot, Jon<o></o>

 

[Scott Huish]

Not sure I follow all that, can you post some sample data and expected results?

 

[ThanksAlot]

Scott, thanks for taking a look. I will try a better example. (In the table, below, Tues is column S, and Wed is column T.)
<o></o>
A team wants to keep track of the number of consecutive games Joe makes (or does not make) a 3-point shot. If he makes a 3-pointer, then cell T1 = S1=+1. As long as Joes makes at least 1 three-pointer in a game in which he plays, cell T1 increases by 1. Five games in a row and cell T1 =5.
<o></o>
In Games 6 (on Wed), Joe does not make a three-point shot. Cell T1 now = (minus)-1. Cell T1 does not =0 because, now, Joe is beginning a string of games where he makes NO 3-point shots. In the next game Joe plays, he does NOT make any 3-point shots. T1=(minus)-2. In the next game, Joe makes NO 3 point shots. T1=(minus)-3.
<o></o>
This continues until Joe plays in a game where he makes at least 1 3-point shot. When he does, cell T1-1. The next day he makes another, so T1=2.
<o></o>
The table shows the tally for the last three games. It looks like this.
<o></o>
[TABLE="class: MsoNormalTable, width: 395"]
<TBODY>[TR]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"]

Mon<o></o>​

[/TD]
[TD="width: 65, bgcolor: transparent"]

Tue<o></o>​

[/TD]
[TD="width: 65, bgcolor: transparent"]

Wed<o></o>​

[/TD]
[TD="width: 65, bgcolor: transparent"]<v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=Picture_x0020_106 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 20; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1045"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_47 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 19; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1044"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_64 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 18; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1043"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_65 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 17; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1042"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_66 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 16; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1041"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_67 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 15; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1040"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_68 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 14; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1039"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_69 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 13; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1038"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_71 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 12; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1037"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_73 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 11; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1036"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_74 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 10; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1035"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_75 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 9; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1034"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_76 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 8; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1033"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_77 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 7; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1032"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_78 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 6; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1031"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_79 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 5; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1030"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_80 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 4; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1029"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_81 style="HEIGHT: 1.5pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 3; MARGIN-TOP: 15pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1028"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_70 style="HEIGHT: 0.75pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 2; MARGIN-TOP: 19.5pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1027"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><v:shape id=Picture_x0020_72 style="HEIGHT: 0.75pt; POSITION: absolute; MARGIN-LEFT: 47.25pt; Z-INDEX: 1; MARGIN-TOP: 19.5pt; VISIBILITY: visible; WIDTH: 2.25pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75" o:spid="_x0000_s1026"><v:imagedata o:title="" src="file:///C:\Users\Kathryn\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><o></o>
[TABLE="class: MsoNormalTable"]
<TBODY>[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[TD="width: 102"]

<o></o>​

Played
[/TD]
[TD="width: 100"]

3-Pointerter<o></o>​

​

​

[/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]

JOE<o></o>​

[/TD]
[TD="width: 65"]

4<o></o>​

[/TD]
[TD="width: 65"]

5<o></o>​

[/TD]
[TD="width: 65"]

-1<o></o>​

[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]

YES<o></o>​

[/TD]
[TD="width: 100, bgcolor: transparent"]

NO<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]

TOM<o></o>​

[/TD]
[TD="width: 65"]

-1<o></o>​

[/TD]
[TD="width: 65"]

-2<o></o>​

[/TD]
[TD="width: 65"]

-3<o></o>​

[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]

YES<o></o>​

[/TD]
[TD="width: 100, bgcolor: transparent"]

NO<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]

MARY<o></o>​

[/TD]
[TD="width: 65"]

10<o></o>​

[/TD]
[TD="width: 65"]

11<o></o>​

[/TD]
[TD="width: 65"]

12<o></o>​

[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]

YES<o></o>​

[/TD]
[TD="width: 100, bgcolor: transparent"]

YES<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 65, bgcolor: transparent"]

BILL<o></o>​

[/TD]
[TD="width: 65"]

2<o></o>​

[/TD]
[TD="width: 65"]

-1<o></o>​

[/TD]
[TD="width: 65"]

1<o></o>​

[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]

YES<o></o>​

[/TD]
[TD="width: 100, bgcolor: transparent"]

YES<o></o>​

[/TD]
[/TR]
</TBODY>[/TABLE]
<o></o>
Each day, the data in the columns is moved to the left to make room for the next game’s tally. Monday will disappear and Tue, and Wed will be moved left. Thurs will appear where Wed is in this table.
<o></o>
Thanks. I hope I explained it well enough.