+ Reply to Thread
Results 1 to 10 of 10

Conditional Formatting, Predicted Number against Actual by Actual Date

  1. #1
    Registered User
    Join Date
    03-25-2010
    Location
    Hastings, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Conditional Formatting, Predicted Number against Actual by Actual Date

    Distributor Monthly Summary Josh test.xlsx

    Hey Guys,

    In B17 is the predicated figure for the end of month, in C17 - H17 is the actual figure so far.

    Would it be possible to have cell's C17-H17 show green if the actual figure is tracking ahead or in line with where it should be for the current time of the month and red if not?

    If so how would I go about that?

    Many Thanks

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting, Predicted Number against Actual by Actual Date

    Not sure that i got your goal..Something like this?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    03-25-2010
    Location
    Hastings, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Conditional Formatting, Predicted Number against Actual by Actual Date

    I'm sorry I should have specified the sheet!

    The calculation needs to happen in the Distributor Summary Sheet it probably makes more sense now!

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting, Predicted Number against Actual by Actual Date

    Ahaa..

    Try this in Conditional formatting rules

    =TEXT(TODAY(),"mmm")=$C2

  5. #5
    Registered User
    Join Date
    03-25-2010
    Location
    Hastings, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Conditional Formatting, Predicted Number against Actual by Actual Date

    How do I go about doing that?

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,697

    Re: Conditional Formatting, Predicted Number against Actual by Actual Date

    Hit F1 - Enter "conditional formatting" It's all there

  7. #7
    Registered User
    Join Date
    03-25-2010
    Location
    Hastings, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Conditional Formatting, Predicted Number against Actual by Actual Date

    I'm on a Mac, if I got into conditional formatting and add new rule all I get is drop downs nowhere to enter a formula?

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting, Predicted Number against Actual by Actual Date


  9. #9
    Registered User
    Join Date
    03-25-2010
    Location
    Hastings, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Conditional Formatting, Predicted Number against Actual by Actual Date

    I really appreciate all your help but it isn't seeming to be having the desired effect, Im sure its I'm doing

  10. #10
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting, Predicted Number against Actual by Actual Date

    And I appreciate very much your kindness that's why i do my best trying to help you.

    When we use use the text function with dates i try to don't post a sample worksheet because in Greece we have different local settings for months-dates that in fact are text. So in Conditional Formmating rules you will see this formula.

    =TEXT(TODAY();"μμμ")=C$2

    μμμ in Greek=mmm in English.

    so do it like this.=TEXT(TODAY(),"mmm")=C$2

    Also in row 2 i used the Greek name of the months. Replace these with English ones...

    Hope that helps..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1