Results 1 to 3 of 3
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Conditional Formatting (Excel 2010)

    The attached is to be part of the Lottery Workbook that I've gotten help on before. The idea is to highlight the winning jackpots by conditional formatting. A jackpot is a winner if the NEXT jackpot for the same lottery is for a lower amount than it is itself. (When there is a winner, the lottery jackpot resets to its minimum amount.)

    The Note column is just there to make it easy to see the winners.

    The existing worksheet works properly, with one big exception. The amount of the next jackpot isn't known until several hours after each drawing. Until that time, it is entered as a zero. In the example worksheet, the jackpots for 05/06/15 and 05/08/15 are unknown, so are entered as zeroes. The jackpots for 05/02/15 and 05/05/15 erroneously show up as winners, when their status really isn't known.

    I don't know how to go about fixing this. Maybe there could be an IF or an AND in the conditional formatting rules, but I don't know if that is even allowed. Maybe there could be an additional formatting rule that overrides the existing one if the next jackpot is zero.

    An easy but inelegant way would be to put IF statements in the Notes column, hide that column, then conditionally format the jackpots based on the contents of the hidden column.

    I'm relying on the Excel Masters to show me the way to improvement.
    Attached Files Attached Files
    Last edited by Lou Sander; 2015-05-23 at 21:02.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Lou,

    How about

    =AND($C2>$C4,$C4<>0)

    as your conditional formula.

    Maud
    Last edited by Maudibe; 2015-05-23 at 22:33.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Lou Sander (2015-05-24)

  4. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    That does it!

    I guess I could have figured it out myself, but Excel 2010's conditional formatting is still something of a mystery to me.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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