Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Need Help Writing Formula (Excel 2000)

    I need help writing a conditional formatting formula.

    (1) Cells A7 through A36 contain years
    (2) Cells B7 through B36 contain numbers
    (3) Cells Q7 through Q36 contain numbers
    (4) Column R is hidden
    (5) Cells R7 through R36 contain formulas that show the difference between the cells in columns Q and B ----- i.e., (=Q7-B7) through (=Q36-B36)
    (6) I need to write a conditional formatting formula for cells Q7 through Q36 that will format (with pattern color) the cell in column Q corresponding to the same year that matches MAX(R7:R36)

    Thanks for your help.
    Robert

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Help Writing Formula (Excel 2000)

    Would you mind re-phrasing point 6!
    <hr>I need to write a conditional formatting formula for cells Q7 through Q36 that will format (with pattern color) the cell in column Q corresponding to the same year that matches MAX(R7:R36)<hr>
    You want the Q column to be formatted by pattern if the max(R7:R36) is equal to the year listed in the A column of the same row?

    Do I understand correctly?
    Regards,
    Rudi

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Re: Need Help Writing Formula (Excel 2000)

    Thanks for your replies, and sorry for the confusion. I had a hard time trying to put point six into words, as you noticed.

    For example, if cell R15 (data for year 1988) is equal to MAX(R7:R36), I want cell Q15 (data for year 1988) to be formatted by pattern; if cell R21 (data for year 1994) is equal to MAX(R7:R36), I want cell Q21 (data for year 1994) to be formatted by pattern, etc. Hope this helps to make point six less confusing.

    Thanks for your help.
    Robert

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help Writing Formula (Excel 2000)

    That is what I assumed, so my reply higher up in this thread should do what you want.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need Help Writing Formula (Excel 2000)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> it should be:
    =(R7=MAX($R$7:$R$36)<font color=red>)</font color=red>
    to add the closing parenthesis or you need to remove the open parenthesis:

    =R7=MAX($R$7:$R$36)


    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help Writing Formula (Excel 2000)

    Yep, thanks. I will edit my reply.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Re: Need Help Writing Formula (Excel 2000)

    Got it! Thanks for all the help!

    Robert

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help Writing Formula (Excel 2000)

    Missing closing parenthesis added by HansV. Thanks to Steve (sdckapr) for pointing out the error.

    If I understand your intent correctly (as Rudi points out, your (6) is ambiguous):

    Select Q7:Q36 (with Q7 as active cell within the selection.
    Select Format | Conditional Formatting...
    Select Formula Is from the first dropdown list.
    Enter the following formula in the box next to it.

    <code>=(R7=MAX($R$7:$R$36))</code>

    Click Format...
    Activate the Patterns tab.
    Select the desired pattern.
    Click OK to close the Format Cells dialog.
    Click OK to close the Conditional Formatting dialog.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •