Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting Formula Help! (2002)

    I have a spreadhsheet containing financial data in columns D-L (Jan-Sept). I would like to have conditional formatting formulas to do the following:
    1. When the September value is $100,000 or more greater than the Jan-Aug (column D-K) average, format the September cell in Pink pattern/shading.
    2. When the September value is $100k or less than the Jan-Aug average, format the September cell in Green pattern/shading.

    Can someone help with formulas to do this?????

    Thanks.

  2. #2
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting Formula Help! (2002)

    Hans, I pasted these formulas into the worksheet. This did not work. My source data for this formatting is in a pivot table. Does this make a difference? For example, I have a row of data that is all around $55k and consequently the average should be right at $55k. It highlighted this September cell (L...) green...

    Other ideas?

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

    Re: Conditional Formatting Formula Help! (2002)

    Can you post (a stripped down copy of) the workbook? Remove or alter sensitive data.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting Formula Help! (2002)

    Hans, thanks. Here ya go.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting Formula Help! (2002)

    As near as I can see, the conditional formatting is doing exactly what you said you wanted it to do. For example, the average of A6:H6 is 54,780.24. For September to be more than 100,000 greater than that it would have to be greater than 154,780.24 but it is only 55,669.37 and is therefore formatted green. If this is not what you wanted, would you give a better explanation.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting Formula Help! (2002)

    Legare, perhaps my explanation was unclear. My attempt was to highlight in pink in situations where the September column was $100k or more than the average of the Jan-Aug data.

    For example, if Jan-Aug is 50k and Sept is 150 k, I wanted to highlight in pink (overbudget).
    If Jan-Aug is $150k and Sept is 50k I wanted to highlight in green (in the money).

    I modified Han's formula to make it work.

    Thanks to all...

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

    Re: Conditional Formatting Formula Help! (2002)

    Edited by HansV to correct error

    Select the data values in column L. In the following, enter the formulas as if they are for the active cell. I will assume that L2 is the active cell.

    Select Format | Conditional Formatting...
    Select Formula Is in the first dropdown.
    Enter the formula
    <code>
    =L2>=AVERAGE(D2:K2)+100000
    </code>
    Click Format...
    Activate the Pattern tab.
    Select a suitable pink color.
    Click OK.
    Click Add >>
    Select Formula Is in the first dropdown.
    Enter the formula
    <code>
    =L2<=AVERAGE(D2:K2)-100000
    </code>
    Click Format...
    Activate the Pattern tab.
    Select a suitable green color.
    Click OK.
    Click OK again.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> It would be nice if you provided some feedback to the replies you receive. Otherwise, other Loungers have no idea whether the answers were helpful. Thank you.[ <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Conditional Formatting Formula Help! (2002)

    The second formula obviously should have had -100000 instead of +100000. I hope you found that yourself. I have corrected my original reply.

Posting Permissions

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