Page 2 of 2 FirstFirst 1 2
Results 11 to 17 of 17

Thread: IF statement not working - I'm using .xlsx file

  1. #11
    Acolyte bobjglover@gmail.com's Avatar
    Join Date
    Aug 2015
    Posts
    47
    Articles
    0
    Excel Version
    1901

    Also - conditional formatting



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I wanted to add this image as well, for some reason 2 of the 4 conditional formatting rules are not working? Status (yrs), H works off of Days Left, G. 2 of the formats that I setup are not working, yet the formula is correct in all cells and the same value in the conditional rule.
    Attached Files Attached Files

  2. #12
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,646
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by bobjglover@gmail.com View Post
    the challenge with this is I am unable to call out "between"? for instances I am hoping to identify contracts that are 30 days and under as" Very Near/Yellow" and 30-90 days "Getting Close/Orange" and the remainder over 90 "Ok/Green"?
    Try the formula on numbers from 0 to 40 and you'll find that 'between' is catered for:
    Click image for larger version. 

Name:	2019-12-10_220655.jpg 
Views:	10 
Size:	51.5 KB 
ID:	9485


    A workbook rather than pictures of a workbook is infinitely preferable.

  3. #13
    Acolyte bobjglover@gmail.com's Avatar
    Join Date
    Aug 2015
    Posts
    47
    Articles
    0
    Excel Version
    1901
    Quote Originally Posted by p45cal View Post
    This is equivalent to your existing formula:
    =INDEX({"Expired","Soon","OK"},MATCH(G12,{0,1,31}))
    You can extend it thus:
    =INDEX({"Expired","Very Near expiry","Getting close","11 days to go","about 16 days","3 weeks","26 days","OK"},MATCH(G12,{0,1,6,11,16,21,26,31}))
    I know this is incorrect but I need to add in between for example - =INDEX({"Expired","30-","30+","60+"},MATCH(G12,{0,<=30,>30,=>60})) can you adjust?

  4. #14
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,646
    Articles
    0
    Excel Version
    365
    Click image for larger version. 

Name:	2019-12-18_233408.jpg 
Views:	16 
Size:	57.3 KB 
ID:	9507

  5. #15
    Acolyte bobjglover@gmail.com's Avatar
    Join Date
    Aug 2015
    Posts
    47
    Articles
    0
    Excel Version
    1901
    Quote Originally Posted by p45cal View Post
    Click image for larger version. 

Name:	2019-12-18_233408.jpg 
Views:	16 
Size:	57.3 KB 
ID:	9507
    Ok, thank you this handles 99%, I would like to account for any contracts that have expired and gone past the "0" mark and into Negative days? I tried to add "-0" in the MATCH but clearly doesn't work. and lastly if there are no beginning or End dates that the cell shows blank until the fields are populated.
    Attached Files Attached Files

  6. #16
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,646
    Articles
    0
    Excel Version
    365
    see attached.
    Attached Files Attached Files

  7. #17
    Acolyte bobjglover@gmail.com's Avatar
    Join Date
    Aug 2015
    Posts
    47
    Articles
    0
    Excel Version
    1901

    P45 - Frig gin genius!

    Quote Originally Posted by p45cal View Post
    see attached.
    OUTSTANDING! thanks so much for workin it until complete!

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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