Modify a Conditional Formatting for highlighting values falling in Range

[naira]

Hi,

I have data in multiple rows (Row 2 to Row 6) and columns (Col. A to Col. M).

Either of the 2 formulae given below are used for conditional formatting in Row 2. The formulae compare whether any of the cells in rows 3, 4, 5 and 6 contains the same value falling within a particular range of any of the cell values in row 2. If the condition is true, then the corresponding cell in row 2 is highlighted. The range (i.e. plus or minus from values in row 2) is defined in cell O2.

This is the preferred formula for use, since a single formula for conditional formatting can cover multiple rows. Would prefer if a solution can be provided for this Formula

 COUNT(MMULT(--(ABS(A2-$A$2:$M$6)<=$O$2),TRANSPOSE(COLUMN($A$2:$M$6)^0))^0)=ROWS($A$2:$M$6)

=AND(SUMPRODUCT(--(ABS($A3:$M3-A2)<=$O$2)),SUMPRODUCT(--(ABS($A4:$M4-A2)<=$O$2)),SUMPRODUCT(--(ABS($A5:$M5-A2)<=$O$2)),SUMPRODUCT(--(ABS($A6:$M6-A2)<=$O$2)))

The formulae work correctly as long as all the 5 rows have data in it (which is correct since I am using AND condition for validation). However, sometimes, one of the rows might be Blank or may contain Zero value.

In such condition, I want the formulae (AND operator) to ignore the rows containing blank or zero values and consider only the rows containing actual values for the purpose of validation. For example, in case row 5 is empty, then the formula should compare values in row 2, only with values in rows 3, 4 and 6, and if found true, then highlight the corresponding cells in row 2. Row 5 should be ignored.

Note: Sample file attached. Screenshot of sample data is given below.
Also, I had posted this request at ExcelForum.com Since no useful reply received there, I am posting the request here.

1. Sample data for First formula:

sample_2 formulae.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	A	B	C	D	E	F	G	H	I	J	K	L	M		Range
2	86	93	99	79	57	56	95	73	68	65	72	62	72		2
3	86	68	74	84	74	90	63	74	66	78	83	53	57
4	80	57	84	82	54	80	86	97	77	59	78	65	62
5	93	83	91	82	86	61	82	50	87	79	69	80	84
6	81	82	87	79	56	80	96	76	99	98	91	92	82		Check that the Conditional Formatting fails in case:
7															1. Delete all data in all cells in any of the rows in Col. A to M
8															2. Enter Zero in all cells in any of the rows from Col. A to M
9															Formula:	COUNT(MMULT(--(ABS(A2-$A$2:$M$6)<=$O$2),TRANSPOSE(COLUMN($A$2:$M$6)^0))^0)=ROWS($A$2:$M$6)
10
11	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
12	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE
13	TRUE	FALSE	TRUE	FALSE	FALSE	TRUE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	FALSE
14	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	TRUE	TRUE
15	TRUE	FALSE	TRUE	TRUE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
FORMULA 2

Cell Formulas
Range	Formula
A11:M15	A11	=COUNT(MMULT(--(ABS(A2-$A$2:$M$6)<=$O$2),TRANSPOSE(COLUMN($A$2:$M$6)^0))^0)=ROWS($A$2:$M$6)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:M6	Expression	=COUNT(MMULT(--(ABS(A2-$A$2:$M$6)<=$O$2),TRANSPOSE(COLUMN($A$2:$M$6)^0))^0)=ROWS($A$2:$M$6)	text	NO

Sample data for second formula:

sample_2 formulae.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	A	B	C	D	E	F	G	H	I	J	K	L	M		Range
2	86	93	99	79	57	56	95	73	68	65	72	62	72		2
3	86	68	74	84	74	90	63	74	66	78	83	53	57
4	80	57	84	82	54	80	86	97	77	59	78	65	62
5	93	83	91	82	86	61	82	50	87	79	69	80	84
6	81	82	87	79	56	80	96	76	99	98	91	92	82
FORMULA 1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:M2,O2	Expression	=AND(SUMPRODUCT(--(ABS($A3:$M3-A2)<=$O$2)),SUMPRODUCT(--(ABS($A4:$M4-A2)<=$O$2)),SUMPRODUCT(--(ABS($A5:$M5-A2)<=$O$2)),SUMPRODUCT(--(ABS($A6:$M6-A2)<=$O$2)))	text	NO

 

[Fluff]

Maybe

=SUM(SIGN(MMULT(--(ABS(A2-$A$2:$M$6)<=$O$2),TRANSPOSE(COLUMN($A$2:$M$6)^0))))=SUM(SIGN(MMULT(--($A$2:$M$6>0),TRANSPOSE(COLUMN($A$2:$M$6)^0))))

 

[naira]

Maybe

=SUM(SIGN(MMULT(--(ABS(A2-$A$2:$M$6)<=$O$2),TRANSPOSE(COLUMN($A$2:$M$6)^0))))=SUM(SIGN(MMULT(--($A$2:$M$6>0),TRANSPOSE(COLUMN($A$2:$M$6)^0))))

Hi!

Thanks for your reply.
Yes the formula works now with an error control, i.e. the conditional formatting works even if some row is blank or has zero value. But it is only working partly.
In the below data set, I have noticed following errors:

1. With the difference of 2, ideally nothing should be highlighted in the below data set, but 76 is being highlighted in row 1 and 147 and 75 in row 2. (it seems that Row 5 is being for calculations, since there are other data in rows 4 & 6 that fall close to 76 & 149.
2. In case I delete everything in row 5, then data around the values 76 & 149 are highlighted. (it seems that Row 5 is being ignored for calculations).
However, now additionally 83 (in cell G3 is highlighted for some reason).
3. In case I change value in cell E5 from 74 to 75, then all cells having value around 76 are properly highlighted.
4. In case I change value in cell I5 from 115 to 148, then all cells having value around 149 are properly highlighted.

Could you kindly see why the cells are not being highlighted properly?

sample_2 formulae.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	A	B	C	D	E	F	G	H	I	J	K	L	M		Range
2	101	76	113	81	144	120	146	122	116	55	108	144	149		2
3	113	97	133	129	61	62	83	112	147	75	127	140	57
4	129	96	81	77	149	100	122	126	150	105	90	138	58
5	66	93	143	80	74	129	95	129	115	111	100	145	60
6	77	140	150	119	138	129	142	100	95	127	85	125	149
FORMULA 2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:M6	Expression	=SUM(SIGN(MMULT(--(ABS(A2-$A$2:$M$6)<=$O$2),TRANSPOSE(COLUMN($A$2:$M$6)^0))))=SUM(SIGN(MMULT(--($A$2:$M$6>0),TRANSPOSE(COLUMN($A$2:$M$6)^0))))	text	NO

 

[Fluff]

I don't understand what you are saying.
B2 is highlighted because every row has a number between 74 to 78 inclusive.

 

[naira]

I don't understand what you are saying.
B2 is highlighted because every row has a number between 74 to 78 inclusive.

Oh, Ok.

But 77 in Cell D4, 74 in E5 and 77 in A6 are still not being highlighted by conditional formatting.

Could you kindly check why this is happening?

 

[Fluff]

D4 is not highlighted, because it does not meet the conditions

 

[naira]

D4 is not highlighted, because it does not meet the conditions

Hi Fluff,

Thanks for your prompt reply on a Sunday morning.
Seems I probably did not give my requirements correctly. Sorry for not be clear.

The requirement was that:

1. All the values in row 2 are the reference values. Therefore row 2 is the Reference row.
- Row 2 in this case and 76 being the reference value.
2. All other rows are the Checking rows.
- Rows 3,4,5,6 are the checking rows
3. Any value (in the checking rows) that falls within +2 or -2 of the values in the reference row should be highlighted.
- Therefore 75 in row 2, 77 in row 3, 74 in row 5 and 77 in row 6 should be highlighted by the conditional formatting formula.
4. Reference Value in the first row, which is common (i.e., +2 or -2 ) across all the checking rows in the reference row should also be highlighted.
- Therefore 76 in row 1 should also be highlighted.

Therefore, 76 in row 1, 75 in row 2, 77 in row 3, 74 in row 5 and 77 in row 6 should be highlighted by the conditional formatting formula. (It seems only values less than 76 are highlighted not more than 76).

Also, I noticed that 77 in rows 4 and 6 are highlighted properly if I delete all the values OR make everything zero in row 5. (This is working correctly as per my original stated request.

 

[Fluff]

Unfortunately I do not know how to do that, hopefully someone else will be able to help.

 

[naira]

Unfortunately I do not know how to do that, hopefully someone else will be able to help.

ok. will seek help elsewhere.

Also, could you kindly elaborate why you felt that 77 in D4 did not meet the condition? I am intrigued.

In case you could elaborate, perhaps I might work on an alternative solution.

Thanks for all your efforts.

 

[Fluff]

Because there is nothing in the row below that is within 2 of 77

ok. will seek help elsewhere.

If you ask this question on any other sites, please don't forget to post a link to it here.