Formula to add 1 for previous day

[DerekWooley]

Hello, I have a list of Machines on each Friday that I would like to flag with a number. The latest Friday should have a 2 and each Friday before that should be marked with one less than the last. My data is below.

[TABLE="width: 236"]
<colgroup><col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="45" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <tbody>[TR]
[TD="width: 90, bgcolor: #B8CCE4"]Date[/TD]
[TD="width: 45, bgcolor: #B8CCE4"]Day[/TD]
[TD="width: 70, bgcolor: #B8CCE4"]Machine[/TD]
[TD="width: 108, bgcolor: #B8CCE4"]Friday Identifier[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7/27/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 1[/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7/27/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 2[/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7/27/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7/27/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 4[/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7/27/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 5[/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7/27/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 6[/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8/3/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 1[/TD]
[TD="bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8/3/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8/3/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 3[/TD]
[TD="bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8/10/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8/10/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 2[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8/10/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 3[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8/10/2018[/TD]
[TD="bgcolor: transparent"]Friday[/TD]
[TD="bgcolor: transparent"]Machine 4[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for any help.

 

[Scott Huish]

Formula in D2:
=IF(A2="","",(MAX(A:A)-A2)/7+2)

Copy down.

Excel 2010
	A	B	C	D
1	Date	Day	Machine	Friday Identifier
2	7/27/2018	Friday	Machine 1	5
3	7/27/2018	Friday	Machine 2	5
4	7/27/2018	Friday	Machine 3	5
5	7/27/2018	Friday	Machine 4	5
6	7/27/2018	Friday	Machine 5	5
7	7/27/2018	Friday	Machine 6	5
8	8/3/2018	Friday	Machine 1	4
9	8/3/2018	Friday	Machine 2	4
10	8/3/2018	Friday	Machine 3	4
11	8/10/2018	Friday	Machine 1	3
12	8/10/2018	Friday	Machine 2	3
13	8/10/2018	Friday	Machine 3	3
14	8/10/2018	Friday	Machine 4	3
15	8/17/2018	Friday	Machine 5	2
Sheet1

Cell Formulas
Range	Formula
D2	=IF(A2="","",(MAX(A:A)-A2)/7+2)
D3	=IF(A3="","",(MAX(A:A)-A3)/7+2)
D4	=IF(A4="","",(MAX(A:A)-A4)/7+2)
D5	=IF(A5="","",(MAX(A:A)-A5)/7+2)
D6	=IF(A6="","",(MAX(A:A)-A6)/7+2)
D7	=IF(A7="","",(MAX(A:A)-A7)/7+2)
D8	=IF(A8="","",(MAX(A:A)-A8)/7+2)
D9	=IF(A9="","",(MAX(A:A)-A9)/7+2)
D10	=IF(A10="","",(MAX(A:A)-A10)/7+2)
D11	=IF(A11="","",(MAX(A:A)-A11)/7+2)
D12	=IF(A12="","",(MAX(A:A)-A12)/7+2)
D13	=IF(A13="","",(MAX(A:A)-A13)/7+2)
D14	=IF(A14="","",(MAX(A:A)-A14)/7+2)
D15	=IF(A15="","",(MAX(A:A)-A15)/7+2)

 

[DerekWooley]

Thank you very much. I had other weekdays between the Fridays which caused decimals so I added a max if weekday = 6 and turned it into an array formula: =if(A2="","",max(if($G2:$G$200=1,$A$2:A200))-A2)/7+2). Row G has the IF weekday = 6 put 1.

Thank you again!