Solution for Excel 2016 number of consecutive cells in row = 3 does not quite work

[Gregory123987]

I had needed a solution to automatically provide conditional formatting if there were consecutive days with values. Unfortunately, the solution that was found did not highlight the 3rd consecutive cell for the current month if there were more than 2 cells ending the previous month. The 9 hidden columns to the left of column M have a value if column J (previous month) has a value.

The same issue applies for 5 and 10 cells as well, but I can fix this myself if someone can help with a solution for the 3rd consecutive cell issue I just described. A link to the previous thread is below.

FYI - I did not do a proper job of testing it as time was of the essence. It has been a year since then and I noticed the issue as I was making changes to the spreadsheet. Thank you in advance for your help.

Spoiler: Previous solution

It took me a while but I managed to figure out a solution. Here it for your info. Thanks for your help.

Added hidden columns, View attachment 19961

Expanded conditional formatting to include applicable columns, wrapped Column with And and added cell reference such as the following to change from
=COLUMN()=(MIN(IF(($M18:$AG18<>"")*($M18:$AH18<>"")*($M18:$AI18<>""),COLUMN($M$16:$AG$16)+2))) to
=AND($J18<3,(COLUMN()=(MIN(IF(($T18:$AP18<>"")*($U18:$AQ18<>"")*($V18:$AR18<>""),COLUMN($T$16:$AP$16)+2)))))

 

[jasonb75]

As you're effectively starting again it would be beneficial to provide a summary of the relevant information (with a visual example) rather than cross linking to multiple threads with some detail pointlessly hidden behind spoiler tags.

As the hidden columns appear to be a significant point, I would advise making it clear in your example which columns are hidden and how they should affect the outcome.

 

[Gregory123987]

As you're effectively starting again it would be beneficial to provide a summary of the relevant information (with a visual example) rather than cross linking to multiple threads with some detail pointlessly hidden behind spoiler tags.

As the hidden columns appear to be a significant point, I would advise making it clear in your example which columns are hidden and how they should affect the outcome.

Thank you for your advice, I will do that.

 

[jasonb75]

One thought that I just had whilst looking at your earlier sample. If you're saying that there are 9 hidden columns between (for example) Jan 31st and Feb 1st then that would mean that they can not be evaluated as consecutive.

It might be possible if the headers are proper dates (day month and year) formatted as day, but not of they are entered as day only.

 

[Gregory123987]

EDITED WITH CORRECT SHEET.

Here is the xl2bb from my spreadsheet. All rows / columns are unhidden.

What is supposed to happen is each month a person is able to reach achievements for 3, 5 and 10 consecutive days, which includes the consecutive days from the previous month. When they do, the cell is supposed to be highlighted in the appropriate color.

Some explanations to answer some questions that may probabaly come up:

Cols W:AE are the hidden columns that illustrate the consecutive days from the previous month as counted in col J to accommodate the conditional highlighting formulas.
Col P determines the ending consecutive columns for the current month which are linked to Column J on next month's sheet.
Cols AF:BE collects the number of laps run for each person and contain the conditional formulas to highlight the cells when applicable.

Rows 1 thru 5:

1. There were no consecutive days the previous month (which is true even if the person missed only the last day) and works as it should for the current month.
2. There were no consecutive days the previous month and works as it should for the current month, even when a day (in the current month) was missed.
3. There were no consecutive days the previous month and works as it should for the current month, even when a day (in the current month) was missed more than once.
4. > 2 and yellow highlighting does not work cell AJ27.
5. > 4 and yellow highlighting does not work cell AJ28, and orange highlighting does not work in AL28.
6. Shows 9 consecutive days and highlights for day 10, yet illustrates no highlighting for the current month, which there should be for days 3 and 5.
7. Shows perfect attendance yet illustrates no highlighting for the current month, which there should be for days 3, 5 and 10.

Row 34 shows how the highlighting should be with previous consecutive days.

And, @jasonb75, they are numbers only.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF
1																																INCENTIVES KEY																PARTICIPANT STATISTICS
2																																Color		Achievement						Incentive
3																																		1st Day						Virtual pat on the back								Number of Previous Participants:									0
4																																		3rd Consecutive Day*						Running Shoes								New Participants this Month:									7
5																																		5th Consecutive Day*						Team Shirt								YTD Particpants:									7
6																																		10th Consecutive Day*						$10 Gift Card								Average # Participants per Day:									7
7																																		50 Laps This Month						31 Day VIA Pass								Total New Members this Month:									0
8																						Consecutive days
9																						3			Applicable Conditional formulas in cell AF24							=AND($J24<3,(COLUMN()=(MIN(IF(($AD24:$BC24<>"")*($AE24:$BD24<>"")*($AF24:$BE24<>""),COLUMN($AD$21:$BC$21)+2)))))
10																						5										=AND($J24<5,COLUMN()=(MIN(IF(($AB24:$BA24<>"")*($AC24:$BB24<>"")*($AD24:$BC24<>"")*($AE24:$BD24<>"")*($AF24:$BE24<>""),COLUMN($AB21:$BA21)+4))))
11																						10										=AND($J24<10,COLUMN()=(MIN(IF(($W24:$AV24<>"")*($X24:$AW24<>"")*($Y24:$AX24<>"")*($Z24:$AY24<>"")*($AA24:$AZ24<>"")*($AB24:$BA24<>"")*($AC24:$BB24<>"")*($AD24:$BC24<>"")*($AE24:$BD24<>"")*($AF24:$BE24<>""),COLUMN($W$21:$AV$21)+9))))
12
13
14
15	January 2021
16							Prior Month Totals						This Month Totals										Hidden Columns Consecutive Days Markers for Conditional Formulas									# Participants per Day Totals
17							0	0	0	49	0	0	173	107	173	Hidden column	107	173	0	0	0	0										7	3	7	7	7	7	7	7	7	7	7	7	7	6	6	6	6	6	7	7	7	7	7	7	7	7
18							PRIOR Month						This Month			For counting Consecutive entries up to the last day for the month to be read on the next month. If the last day is blank consecutive days will be 0	YEAR TO DATE															LAPS RUN
19																																F	S	M	T	W	TH	F	S	M	T	W	TH	F	S	M	T	W	TH	F	S	M	T	W	TH	F	S
20							These columns come from the previous month						These columns go to the next month						Hidden Row													1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26
21							Total Laps	Total Miles	Total # Days	Consecutive Days	Total Miles YTD	Total Days YTD	Total Laps Run	Total Miles Run	# of Days		Total Miles	Total Days					9	8	7	6	5	4	3	2	1	1	2	4	5	6	7	8	9	11	12	13	14	15	16	18	19	20	21	22	23	25	26	27	28	29	30
22
23			Person	1			0	0	0	0	0	0	21	13	21	8	13	21														1	1	1	1	1	1	1	1	1	1	1	1	1						1	1	1	1	1	1	1	1
24			Person	2			0	0	0	0	0	0	25	16	25	24	16	25														1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
25			Person	3			0	0	0	2	0	0	25	16	25	24	16	25												1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
26			Person	4			0	0	0	3	0	0	25	16	25	24	16	25											1	1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
27			Person	5			0	0	0	4	0	0	25	16	25	24	16	25										1	1	1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
28			Person	6			0	0	0	9	0	0	26	16	26	26	16	26					1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
29			Person	7			0	0	0	31	0	0	26	16	26	26	16	26					1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
30							0	0	0	0	0	0	0	0	0	0	0	0
31							0	0	0	0	0	0	0	0	0	0	0	0
32							0	0	0	0	0	0	0	0	0	0	0	0
33
34			Person	22			0	0	0	9	0	0	26	16	26	26	16	26					1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
Jan

Cell Formulas
Range	Formula
BE3	BE3	=COUNTIF(H23:H32,">0")
BE4	BE4	=COUNTIF(M23:M32,">0")
BE5	BE5	=SUM(BE3:BE4)
BE6	BE6	=IF(SUM(AF17:BE17)>0,SUM(AF17:BE17)/COUNTIF(AF17:BE17,">0"),0)
BE7	BE7	=COUNTIF(B23:B32,"Y")
Q17:V17,G17:O17	G17	=SUM(G23:G32)
AF17:BE17	AF17	=COUNT(AF23:AF32)
M34,M23:M32	M23	=SUM($AF23:$BE23)
N23	N23	=$M23*0.6213
O23	O23	=COUNT($AF23:$BE23)
P34,P23:P32	P23	=IF(M23<>0,($BE$20-(MAX(($AF23:$BE23="")*($AF$20:$BE$20)))),0)
Q34,Q23:Q32	Q23	=+N23+K23
R34,R23:R32	R23	=SUM(L23+O23)
N34,N24:N32	N24	=M24*0.6213
O34,O24:O32	O24	=COUNT(AF24:BE24)
W34:AE34,W23:AE32	W23	=IF($J23=0,"",IF($J23>=W$21,1,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
W34:AE34	Expression	=AND($J34<10,COLUMN()=(MIN(IF(($M34:$AL34<>"")*($N34:$AM34<>"")*($O34:$AN34<>"")*($P34:$AO34<>"")*($Q34:$AP34<>"")*($R34:$AQ34<>"")*($S34:$AR34<>"")*($T34:$AS34<>"")*($U34:$AT34<>"")*($V34:$AT34<>""),COLUMN($M$21:$AL$21)+9))))	text	NO
AB34:AE34	Expression	=AND($J34<5,COLUMN()=(MIN(IF(($R34:$AQ34<>"")*($S34:$AR34<>"")*($T34:$AS34<>"")*($U34:$AT34<>"")*($V34:$AT34<>""),COLUMN($R32:$AQ32)+4))))	text	NO
AD34:AE34	Expression	=AND($J34<3,(COLUMN()=(MIN(IF(($T34:$AS34<>"")*($U34:$AT34<>"")*($V34:$AT34<>""),COLUMN($T$21:$AS$21)+2)))))	text	NO
C34:D34	Expression	=ISFORMULA(C34)	text	NO
M34	Cell Value	>=50	text	NO
W23:AE32	Expression	=AND($J23<10,COLUMN()=(MIN(IF(($M23:$AL23<>"")*($N23:$AM23<>"")*($O23:$AN23<>"")*($P23:$AO23<>"")*($Q23:$AP23<>"")*($R23:$AQ23<>"")*($S23:$AR23<>"")*($T23:$AS23<>"")*($U23:$AT23<>"")*($V23:$AT23<>""),COLUMN($M$21:$AL$21)+9))))	text	NO
AB23:AE32	Expression	=AND($J23<5,COLUMN()=(MIN(IF(($R23:$AQ23<>"")*($S23:$AR23<>"")*($T23:$AS23<>"")*($U23:$AT23<>"")*($V23:$AT23<>""),COLUMN($R21:$AQ21)+4))))	text	NO
AD23:AE32	Expression	=AND($J23<3,(COLUMN()=(MIN(IF(($T23:$AS23<>"")*($U23:$AT23<>"")*($V23:$AT23<>""),COLUMN($T$21:$AS$21)+2)))))	text	NO
AF26:BE32	Expression	=AND($J26<10,COLUMN()=(MIN(IF(($W26:$AV26<>"")*($X26:$AW26<>"")*($Y26:$AX26<>"")*($Z26:$AY26<>"")*($AA26:$AZ26<>"")*($AB26:$BA26<>"")*($AC26:$BB26<>"")*($AD26:$BC26<>"")*($AE26:$BD26<>"")*($AF26:$BE26<>""),COLUMN($W$21:$AV$21)+9))))	text	NO
AF25:BE32	Expression	=AND($J25<5,COLUMN()=(MIN(IF(($R25:$AN25<>"")*($S25:$AO25<>"")*($T25:$AP25<>"")*($U25:$AQ25<>"")*($V25:$AR25<>""),COLUMN($R23:$AN23)+4))))	text	YES
AF23:BE32	Expression	=AND($J23<3,(COLUMN()=(MIN(IF(($AD23:$BC23<>"")*($AE23:$BD23<>"")*($AF23:$BE23<>""),COLUMN($AD$21:$BC$21)+2)))))	text	NO
AJ23:AJ32	Expression	=AND($J23=0,(COUNTA($AF23:$AI23)=0),NOT(ISBLANK($AJ23)))	text	NO
AI23:AI32	Expression	=AND($J23=0,(COUNTA($AF23:$AH23)=0),NOT(ISBLANK($AI23)))	text	NO
C23:D32	Expression	=ISFORMULA(C23)	text	NO
AK23:AK32	Expression	=AND($J23=0,(COUNTA($AF23:$AJ23)=0),NOT(ISBLANK($AK23)))	text	NO
AL23:AL32	Expression	=AND($J23=0,(COUNTA($AF23:$AK23)=0),NOT(ISBLANK($AL23)))	text	NO
M23:M32	Cell Value	>=50	text	NO
AM23:AM32	Expression	=AND($J23=0,(COUNTA($AF23:$AL23)=0),NOT(ISBLANK($AM23)))	text	NO
AN23:AN32	Expression	=AND($J23=0,(COUNTA($AF23:$AM23)=0),NOT(ISBLANK($AN23)))	text	NO
AO23:AO32	Expression	=AND($J23=0,(COUNTA($AF23:$AN23)=0),NOT(ISBLANK($AO23)))	text	NO
AP23:AP32	Expression	=AND($J23=0,(COUNTA($AF23:$AO23)=0),NOT(ISBLANK($AP23)))	text	NO
AQ23:AQ32	Expression	=AND($J23=0,(COUNTA($AF23:$AP23)=0),NOT(ISBLANK($AQ23)))	text	NO
AR23:AR32	Expression	=AND($J23=0,(COUNTA($AF23:$AQ23)=0),NOT(ISBLANK($AR23)))	text	NO
AS23:AS32	Expression	=AND($J23=0,(COUNTA($AF23:$AR23)=0),NOT(ISBLANK($AS23)))	text	NO
AT23:AT32	Expression	=AND($J23=0,(COUNTA($AF23:$AS23)=0),NOT(ISBLANK($AT23)))	text	NO
AU23:AU32	Expression	=AND($J23=0,(COUNTA($AF23:$AT23)=0),NOT(ISBLANK($AU23)))	text	NO
AV23:AV32	Expression	=AND($J23=0,(COUNTA($AF23:$AU23)=0),NOT(ISBLANK($AV23)))	text	NO
AW23:AW32	Expression	=AND($J23=0,(COUNTA($AF23:$AV23)=0),NOT(ISBLANK($AW23)))	text	NO
AX23:AX32	Expression	=AND($J23=0,(COUNTA($AF23:$AW23)=0),NOT(ISBLANK($AX23)))	text	NO
AY23:AY32	Expression	=AND($J23=0,(COUNTA($AF23:$AX23)=0),NOT(ISBLANK($AY23)))	text	NO
AZ23:AZ32	Expression	=AND($J23=0,(COUNTA($AF23:$AY23)=0),NOT(ISBLANK($AZ23)))	text	NO
BA23:BA32	Expression	=AND($J23=0,(COUNTA($AF23:$AZ23)=0),NOT(ISBLANK($BA23)))	text	NO
BC23:BC32	Expression	=AND($J23=0,(COUNTA($AF23:$BB23)=0),NOT(ISBLANK($BC23)))	text	NO
BE23:BE32	Expression	=AND($J23=0,(ISBLANK($AF23:$BD23)),NOT(ISBLANK($BE23)))	text	NO
BD23:BD32	Expression	=AND($J23=0,(COUNTA($AF23:$BC23)=0),NOT(ISBLANK($BD23)))	text	NO
BB23:BB32	Expression	=AND($J23=0,(COUNTA($AF23:$BA23)=0),NOT(ISBLANK($BB23)))	text	NO
AH23:AH32	Expression	=AND($J23=0,(COUNTA($AF23:$AG23)=0),NOT(ISBLANK($AH23)))	text	NO
AF26:BE32	Expression	=AND($J26<5,COLUMN()=(MIN(IF(($AB26:$BA26<>"")*($AC26:$BB26<>"")*($AD26:$BC26<>"")*($AE26:$BD26<>"")*($AF26:$BE26<>""),COLUMN($AB23:$BA23)+4))))	text	NO
AG23:AG32	Expression	=AND($J23=0,(COUNTA($AF23)=0),NOT(ISBLANK($AG23)))	text	NO
AF23:AF32	Expression	=AND($J23=0,NOT(ISBLANK($AF23)))	text	NO
AF23:BE24	Expression	=AND($J23<10,COLUMN()=(MIN(IF(($W23:$AV23<>"")*($X23:$AW23<>"")*($Y23:$AX23<>"")*($Z23:$AY23<>"")*($AA23:$AZ23<>"")*($AB23:$BA23<>"")*($AC23:$BB23<>"")*($AD23:$BC23<>"")*($AE23:$BD23<>"")*($AF23:$BE23<>""),COLUMN($W$21:$AV$21)+9))))	text	NO
AF23:BE24	Expression	=AND($J23<5,COLUMN()=(MIN(IF(($AB23:$BA23<>"")*($AC23:$BB23<>"")*($AD23:$BC23<>"")*($AE23:$BD23<>"")*($AF23:$BE23<>""),COLUMN($AB21:$BA21)+4))))	text	NO
AF25:BE25	Expression	=AND($J25<10,COLUMN()=(MIN(IF(($W25:$AV25<>"")*($X25:$AW25<>"")*($Y25:$AX25<>"")*($Z25:$AY25<>"")*($AA25:$AZ25<>"")*($AB25:$BA25<>"")*($AC25:$BB25<>"")*($AD25:$BC25<>"")*($AE25:$BD25<>"")*($AF25:$BE25<>""),COLUMN($W$21:$AV$21)+9))))	text	NO
AF25:BE25	Expression	=AND($J25<5,COLUMN()=(MIN(IF(($AB25:$BA25<>"")*($AC25:$BB25<>"")*($AD25:$BC25<>"")*($AE25:$BD25<>"")*($AF25:$BE25<>""),COLUMN(#REF!)+4))))	text	NO

 

[Gregory123987]

I have edited the post with the correct sheet.

 

[jasonb75]

I thought that I had an idea of what you meant, but with the 'correct sheet' from your edited post, I'm not so sure.

> 2 and yellow highlighting does not work cell AJ27.

Should AJ27 be AP24 with the revised example? If so then I think that I have just about figured out what is needed, although I will probably not start on a solution until the morning.

One question that I don't see covered in the example. With the missing entry in the middle of the row shown below, is this what you would expect?

Manexcel.xlsx
	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU
37	1	1	1	1		1	1	1	1	1
Sheet11

 

[Gregory123987]

I thought that I had an idea of what you meant, but with the 'correct sheet' from your edited post, I'm not so sure.

Should AJ27 be AP24 with the revised example? If so then I think that I have just about figured out what is needed, although I will probably not start on a solution until the morning.

One question that I don't see covered in the example. With the missing entry in the middle of the row shown below, is this what you would expect?

Manexcel.xlsx
	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU
37	1	1	1	1		1	1	1	1	1
Sheet11

I had edited my post with the correct spreadsheet. I think you have the previous one.

 

[jasonb75]

Referring back to your earlier thread, does this condition no longer apply?

The highlighting should not occur in Cells Q2, W2, AE2 as they are not the first occurrence.

As far as I can see,

> 2 and yellow highlighting does not work cell AJ27.

AJ27 is the second occurrence, with the first occurrence being at the end of the previous month.

Also, looking at row 34 in the example, are you saying that 13 consecutive days should highlight the same as 3, 15 days the same as 5, etc?

 

[Gregory123987]

I thought that I had an idea of what you meant, but with the 'correct sheet' from your edited post, I'm not so sure.

Should AJ27 be AP24 with the revised example? If so then I think that I have just about figured out what is needed, although I will probably not start on a solution until the morning.

One question that I don't see covered in the example. With the missing entry in the middle of the row shown below, is this what you would expect?

Referring back to your earlier thread, does this condition no longer apply?

As far as I can see,

AJ27 is the second occurrence, with the first occurrence being at the end of the previous month.

Also, looking at row 34 in the example, are you saying that 13 consecutive days should highlight the same as 3, 15 days the same as 5, etc?

AJ 27 is the first occurrence for the month. It should have highlighted, but the formulas being used won't allow it (AND($J27<3,...) . This is what the issue is. If I leave out the AND($J27<3,...) it does not work correctly either.

There is only a single occurrence of each achievement during the month, and only the first occurrence of each should highlight. If there are 13 consecutive days, it would satisfy the requirements for the 3rd, 5th and 10th consecutive day achievements.