Excel 2003 Conditional formatting challenge question

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

1) 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?

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

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" wrote:

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.=A1A8 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 FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=A1A8
4. Click Format ButtonPattern 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 FormatConditional Formatting
8. For Condition1Select 'Formula Is' and enter the below formula
=A8A1
9. Click Format ButtonPattern and select your color
10. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"John C." wrote:

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


"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"John C." wrote:

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

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. =A1A8 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 =C6C13. 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
---------------
Jacob Skaria


"John C." wrote:

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

1) 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?

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

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" wrote:

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.=A1A8 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 FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=A1A8
4. Click Format ButtonPattern 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 FormatConditional Formatting
8. For Condition1Select 'Formula Is' and enter the below formula
=A8A1
9. Click Format ButtonPattern and select your color
10. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"John C." wrote:

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


"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"John C." wrote:

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