Puzzling results from Conditional Formatting rules & ranges

[JenniferMurphy]

I am trying to construct a conditional formatting rule & range that will display an equal sign (=) whenever the data in a cell is equal to the data in the cell immediately above it.

In the table below, the data in D6:D13 is replicated in Columns E-H. All 4 columns have the identical data. In each of the Columns E-G, I applied the conditional formatting rules indicated in Row 3 over the ranges indicated in Row 4. Columns F & G have the correct results. Column E does not. I do not understand why not.

R/C	C	D	E	F	G
3	Rule	None	=E7=E6	=F7=F6	=G6=G5
4	Range	None	$E$6:$E$13	$F$7:$F$13	$G$6:$G$13
5	=D6=D5?	0	0	0	0
6		25	=	25	25
7		25	25	=	=
8		30	30	30	30
9		35	=	35	35
10		35	=	=	=
11		35	35	=	=
12		40	=	40	40
13		40	40	=	=

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Fluff]

With the formulae you have shown cols E&G are correct, but col F is not.
Are you sure that the formulae shown are what you are using?

 

[JenniferMurphy]

With the formulae you have shown cols E&G are correct, but col F is not.
Are you sure that the formulae shown are what you are using?

I just double checked and they are correct.

Since attachments are not allowed here (sigh), I have uploaded the workbook to this Dropbox folder.

https://www.dropbox.com/sh/gkp5iv3di7qjiij/AAAVieCy_htdQeSbXeXVajIia?dl=0

But my primary question is why are the result in Col E incorrect?

 

[Fluff]

I hadn't noticed that you are using different ranges, hence the different results.
That said the result in Col E is not incorrect.
If E7=E6 then E6 will be formatted as that is the first cell in the applies to range.
Whereas for Col F F7 is the first cell in the applies to range, hence F6 does not get formatted.

 

[JenniferMurphy]

I hadn't noticed that you are using different ranges, hence the different results.
That said the result in Col E is not incorrect.
If E7=E6 then E6 will be formatted as that is the first cell in the applies to range.
Whereas for Col F F7 is the first cell in the applies to range, hence F6 does not get formatted.

Yes, I know that the results in E are incorrect. The question is why?

Are you saying that the result of the first test, =E7=E6, is stored in E6 (not E7) because E6 is the first cell in the range?

If so, then as it moves through the range, the next test would be =E8=E7 (because both addresses are fully relative, right?) and that result would be stored in E7. Is that correct?

That seems to be what is happening.

So it really doesn't matter whether the rule is "=E7=E6" or "=E6=E7". Either way it will compare the same two cells and put the result in E6. Right?

Thanks for the help.

 

[Fluff]

Yes, I know that the results in E are incorrect. The question is why?

I actually said

That said the result in Col E is NOT incorrect.

Are you saying that the result of the first test, =E7=E6, is stored in E6 (not E7) because E6 is the first cell in the range?

If so, then as it moves through the range, the next test would be =E8=E7 (because both addresses are fully relative, right?) and that result would be stored in E7. Is that correct?

That seems to be what is happening.

So it really doesn't matter whether the rule is "=E7=E6" or "=E6=E7". Either way it will compare the same two cells and put the result in E6. Right?

That's right.

 

[JenniferMurphy]

I actually said that the result in Col E is NOT incorrect.

(chuckle) You are right. What I meant was that it was incorrect from what I wanted it to do.

Many years ago when I was writing assembler code, and we would often code "AR" (Add Register) when we meant "NR" (And Register), we used to joke that there ought to be a DWIM command (Do What I Meant).

Thanks for the help

 

[Fluff]

Glad to help & thanks for the feedback.

Whilst a DWIM command would be nice, there wouldn't be a need for this site

 

[JenniferMurphy]

Glad to help & thanks for the feedback.

Whilst a DWIM command would be nice, there wouldn't be a need for this site

Are you kidding? I could even screw that up. In fact, if the DWIM command was trying to read my mind and makes sense of it, it would get a headache and do the wrong thing.

Cheers