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.

Date	Day	Machine	Friday Identifier
7/27/2018	Friday	Machine 1	4
7/27/2018	Friday	Machine 2	4
7/27/2018	Friday	Machine 3	4
7/27/2018	Friday	Machine 4	4
7/27/2018	Friday	Machine 5	4
7/27/2018	Friday	Machine 6	4
8/3/2018	Friday	Machine 1	3
8/3/2018	Friday	Machine 2	3
8/3/2018	Friday	Machine 3	3
8/10/2018	Friday	Machine 1	2
8/10/2018	Friday	Machine 2	2
8/10/2018	Friday	Machine 3	2
8/10/2018	Friday	Machine 4	2

<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> </tbody>

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!