Excel 2003 Conditional formatting challenge question

[John C.]

I am familiar with how to find a min and max value in a table and change the
color of the cell and even the font to bold. However, I am stumped with this
problem. Let's say that I have data in a table from A1 to C6, and from A8 to
C13. I want to compare A1 to A8, A2 to A9....C6 to C13, and highlight the
maximum one for each comparison, so that I am comparing apples to apples.

Will someone please explain how to do this? Thanks for your consideration.

John

 

[JE McGimpsey]

One way:

Select A1:C6, with A1 the active cell. Enter this CF:

Formula is = A1 = MAX(A1,A8)

Select A8:C13, with A8 the active cell. Enter this CF:

Formula is = A8 = MAX(A1,A8)

 

[Don Guillett]

Use this idea. formula is
=a1=max($a$1:$a$8)

 

[Jarek Kujawa]

why haven't you used solutions provided to your post of 05:15 in the
NG?

was there anything wrong with them?

 

[Luke M]

Select the first table, A1:C6, with A1 being active cell. CF formula is:
=A1=MAX(A1,A8)
Set format, ok out.

Select second table, A8:C13, with A8 being active cell. CF formula is:
=A8=MAX(A1,A8)
Set format, ok out.

 

[John C.]

Luke,
Ok, I am a bit green on conditional formatting. When the formula is
"=A1=MAX(A1,A8)", why do I need the "=A1" in front of the "=MAX.."? What if
I left it out? Also, once I establish the first and second table, do i then
copy the A1 cell and simply special paste just the format in all of the
cells, and then do the same for the second table?

I saw an example on another site using =$A1=MAX($A1,$A8). How do the $
signs restrict the copy in this case?

Thanks for your assistance.

John

 

[Don Guillett]

Did you see my post
=a1=max($a$1:$a$8)
copy in range a1:a8
The absolutes restrict the area to look at

 

[David Biddulph]

Luke's formula is for the "Formula is" option in CF, whereas if you were
using =MAX(A1,A8) that would be in the "Cell value is" option.

No, you don't need to copy, and paste special format, because Luke told you:
"Select the first table, A1:C6, with A1 being active cell" before you insert
your CF conditions, and that is what he meant you to do.

The $ signs in =$A1=MAX($A1,$A8) change the column references from being
relative references to being absolute references. Look these terms up in
Excel help. That means that the whole of your range from column A to column
C will be using column A references to determine the format. Select one of
the other cells (C6, for example) and you'll see (if you have used the $
signs in the formula) that they still have the $A column references, though
the row references (without a preceding $ sign) are incremented from 1 to 6
()and from 8 to 13). By contrast if you don't include the $ signs, both row
and column references are incremented as you change row or column.

 

[Jacob Skaria]

Using MAX() will return true if both cell values are same or blank...Try the
below

To your questions
--CF expects either a true or false.=A1>A8 returns either a TRUE or FALSE
--To your question.CF applies for the entire selection.So need to copy paste.

1. Select the cell/Range (say A1:C6). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=A1>A8
4. Click Format Button>Pattern and select your color
5. Hit OK

6. Now select the cell/Range (say A8:C13). Please note that the cell
reference A8 mentioned in the formula is the active cell in the selection.
Active cell will have a white background even after selection

7. From menu Format>Conditional Formatting>
8. For Condition1>Select 'Formula Is' and enter the below formula
=A8>A1
9. Click Format Button>Pattern and select your color
10. Hit OK

If this post helps click Yes

 

[Jacob Skaria]

Typo....

CF applies and adjusts the formula for the entire selection.So *** NO ***
need to copy paste....

If this post helps click Yes

 

[John C.]

Jacob,
Though I appreciated the reponse, it didn't address my questions, which were:

1) When the formula is
2) I saw an example on another site using =$A1=MAX($A1,$A8). How do the $
Lastly, how is the active cell related the entire table that is selected,
i.e. If A1 is selected, and then I choose A1 through C6 as mu range, what
does the active cell indicate?

john

 

[Jacob Skaria]

1. If you enter a1=max(a1,b1) without the = sign in front excel will consider
this as a text string and will convert that to ="a1=max(a1,b1)". As mentioned
in my previous response CF expects either a true or false. =A1>A8 or =
A1=MAX(A1,B1) returns either a TRUE or FALSE

2. In this formula (=$A1=MAX($A1,$A8))....when the formula is applied to the
other cells since columns A is loced using a dollar sign the column never
changes..but row number changes.. This formula will not satisfy your
requirement.If you are unfamiliar with the type of referencing, below are the
different reference styles.
A1 Relative referencing. Both column and row will change if you copy or drag
the formula.
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and will not change.
$A$1 Column and row reference are fixed.

3. For CF on a selection of cells; if the formula is referenced as the
active cell; the same condition will be applied to all the cells....Once you
apply the CF after selecting A1:C6 check the CF formula from another cell say
C6. The formula adjusts to lookat =C6>C13. Here the formula is referencing to
C6 coz you have referenced the active cell in the formula....

4.Mean while did you try my suggestion.?

If this post helps click Yes