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

ThanksAlot

New Member
Joined
Apr 29, 2013
Messages
13
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:p></o:p>

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:p></o:p>

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:p></o:p>
___
<o:p></o:p>
I don’t know if the following helps, or not.
<o:p></o:p>

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

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


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:p></o:p>

Continuing…
<o:p></o:p>


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:p></o:p>

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:p></o:p>
<o:p></o:p>

ThanksAlot, Jon
<o:p></o:p>
 
Last edited:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Not sure I follow all that, can you post some sample data and expected results?
 

ThanksAlot

New Member
Joined
Apr 29, 2013
Messages
13
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:p></o:p>
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:p></o:p>
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:p></o:p>
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:p></o:p>
The table shows the tally for the last three games. It looks like this.
<o:p></o:p>

Mon<o:p></o:p>

Tue<o:p></o:p>

Wed<o:p></o:p>
<v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="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><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><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:p></o:p>

<TBODY>
</TBODY>


<o:p></o:p>
Played
3-Pointerter<o:p></o:p>​

JOE<o:p></o:p>

4<o:p></o:p>

5<o:p></o:p>

-1<o:p></o:p>

YES<o:p></o:p>

NO<o:p></o:p>

TOM<o:p></o:p>

-1<o:p></o:p>

-2<o:p></o:p>

-3<o:p></o:p>

YES<o:p></o:p>

NO<o:p></o:p>

MARY<o:p></o:p>

10<o:p></o:p>

11<o:p></o:p>

12<o:p></o:p>

YES<o:p></o:p>

YES<o:p></o:p>

BILL<o:p></o:p>

2<o:p></o:p>

-1<o:p></o:p>

1<o:p></o:p>

YES<o:p></o:p>

YES<o:p></o:p>

<TBODY>
</TBODY>
<o:p></o:p>
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:p></o:p>
Thanks. I hope I explained it well enough.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,249
Messages
5,576,943
Members
412,753
Latest member
Coach_Olson
Top