Help with nested function needed

[Azr4el]

Hello All

I have this function which is actually working fine, more or less.

This is the function in cell G9: 

=IF(OR(C9="Abandoned",C9="Completed"),"",IF(B9="","",IF(F9="",E9-TODAY(),F9-TODAY())))

It does what I want except when it comes to "Completed". Cell G9 is empty if "Abandoned" is chosen in cell C9 but if "Completed" is chosen it calculates the number of days left.

C9 is a dropdown menu of 5 items and in addition cell styles is applied. D, E and F are formatted cells (Date).

Is there anybody out there who can help me fix the error? I am using Office 365 on a Mac.

Thank you

[#2]

Then C9 is not equal to Completed. Did you use formula evaluation?

[#3]

@Azr4el 

Perhaps there is a space after (or before) Completed?

[#4]

@Azr4el 

Hide error values and error indicators in cells

IFERROR function

IS functions

With your permission, I also recommend the VLOOKUP function for such cases.

Hope this information could help you.

Wish you a nice day.

Nikolino

I know I don't know anything (Socrates)

[#5]

@Hans Vogelaar Thanks for bringing this up; I checked and confirm that it is , unfortunately, not that easy.

[#6]

Hello Detlef. Thanks for your idea. Indeed, I only did now an error check with the following outcome: "the error check is complete for the entire sheet". I also tried to trace the error: "the trace error command requires that the active cell contain an error value".
Maybe I am wrong and you meant another command then "error checking"?

[#7]

@NikolinoDE: many thanks as well for your input and providing the links. I was not aware of the "error" functions and will carefully read the articles.
Good point: VLOOKUP. Thanks for reminding me, I was so focused on that "if" that I didn't even consider other solutions. Will try and come back in case I fail.

[#8]

@Azr4el 

It's seems that formula evaluation is not available on a Mac.

https://support.microsoft.com/en-us/office/evaluate-a-nested-formula-one-step-at-a-time-59a201ae-d1d...

[#9]

So kind, thanks for checking on that! Will find a way to change the system and follow up on this as well and keep you all posted.

[#10]

Hi Detlef. I was able to run the formula evaluation. First it looked a bit strange but then I realised that @Hans Vogelaar was not completely wrong. I had a space AFTER "completed" in my drop-down-list.
@NikolinoDE: I will still see whether I can integrate the VLOOKUP function just to know myself.

A huge and sincere Thank you to everybody's input, very much appreciated.