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

Joined
Aug 3, 2015
Messages
57
Reaction score
0
Points
0
Excel Version(s)
1901
Hello Guru's,

Hope you can assist, this is returning Red parens on the nested part and not working. g3 is the same as @Days left column - I am guessing this is a new function of xlsx. describing a cell when you = it?

=IF(g3<0, "Expired",if([@[Days Left]]<30, "Expiring Soon", ""))

Thanks - Bob
 
Excel versions have functions, not whether it is an xlsx, xlsm, xlsb, or xlam.

What does the data look like, and what is nit working with that formula (I didn't understand what you wrote).
 
Bob,

Thanks for the quick response, see attached for what i am trying to do and what the formula is doing. It should be fairly simple not sure why it isn't functioning?
 

Attachments

  • Example.xlsx
    11.7 KB · Views: 13
Hi,

The formula is in D2 of the attachment...looks like this - =IF(c3<0,"Expired",IF(c3<30,"Expiring soon","")). So I got this one to work but it doesn't do exactly what I need =IF((AND(G4<=0, G4<=30)), "Expired", ""). I need the formula to return 1 of 3 values "Expired", "Soon", or nothing in the cell. So I am trying to work with this one and have the same challenge as the first one - =IF(g3<=0, "Expired", IF(g3<=30, "Soon", IF(g3>30, "Ok ", "")))
 
Nope; no formulae at all, no table. Wrong file I expect.
ps. 31 Feb?!
 
D2 is a header in the table. Please attach the correct workbook and we can take it from there.
 
My apologies wrong file,. But it did force me to figure it out on the 1st round of the "If" statement. =IF(G12<=0, "Expired", IF(G12<=30, "Soon", IF(G12>30, "Ok ", ""))) I need to extend it to do more functions, can I post here?
 
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}))
 
Index...never would have thought of that, ty! So the MATCH #s in brackets are equivalent to what I can put days out? I think 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"?
 
Also - conditional formatting

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.
 

Attachments

  • Conditional Formating example.docx
    242.3 KB · Views: 4
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:
2019-12-10_220655.jpg


A workbook rather than pictures of a workbook is infinitely preferable.
 
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?
 

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.
 

Attachments

  • Example.xlsx
    12.7 KB · Views: 3
see attached.
 

Attachments

  • vbaExpress10345Example.xlsx
    12.5 KB · Views: 5
Back
Top