if statement when dealing with empty/blank/null cells

[ajm]

hi all, inputting a nested if statement to test whether we are on time, behind, or completed certain projects. if complete you get a green, if % complete is 0 or BLANK and the start date has passed, you get a RED, and so on. its the second test above that is causing issues.

if [#"% Complete"]=100
then "GREEN" 
else if [#"% Complete"]=0 or Text.Length([#"% Complete"])=0 and DateTime.Date( DateTime.LocalNow() )>[Start Date for Next Activity]
then "RED"
else if DateTime.Date( DateTime.LocalNow() ) - ([Start Date for Next Activity]+(([#"% Complete"]/100)*[#"# Days for Next Activity"]))>14
then "RED"
else "GREEN"

i get no syntax errors but the contents of the new column end up being "Error". when clicked on, i get the following:
Expression.Error: We cannot convert the value null to type Logical.Details: Value= Type=[Type]

so, how do i write in the test for empty/blank? i have tried [#"% Complete"]="" and [#"% Complete"]=null in place of the Text.Length([#"% Complete"])=0

second question, while i am at it. should i bracket the OR conditions in the second argument? it should read, if either of these AND Today is greater than the start date, NOT if %Complete = 0 OR both of these.

 

[Micron]

You have to be 100% explicit for everything. If I say to you, "do this if A or B" you would likely understand it. A computer interprets everything like that statement as separate conditions, so what it "hears" is
"do this if A"
"or B"
If I walked up to you and said "or B" you'd think I was a bit nuts perhaps. Also, Text property is a property of something (a range, cell, form control, something). You can't omit it otherwise there is no way to know what it refers to. You are also correct in that grouping logical operations can be important lest they be grouped in a way that you don't intend.

Your expression is a bit too hard for me to follow, but applying the above, maybe like

else if ([#"% Complete"]=0 or [#"% Complete"]Text.Length([#"% Complete"])=0) and DateTime.Date( DateTime.LocalNow() )>[Start Date for Next Activity]

If one or both of the grouped expressions returns True, the result is True. If both are False it returns False. So you can interpret it as
If True And date1>date2 Then
HTH