Forum Discussion
IF FORMULA - Help Needed please
Your answer doesn't seem to cover all the possibilities. You have to realize, that you, since you're IN the situation, consider the answer to the following question is obvious and therefore not worth stating.
This is a question I would be asking if we were sitting down face to face, and I apologize if it seems so obvious to you:
Is it ever the case that two or more of the cells L4, P4 and V4 will have values in them?
I understand what happens if any one of them has a value, but to me it's not clear whether two or more might have a value and what happens then if those values are inconsistent.
And since that still needs answering, let me pose another, which gets at the basic design.
In your most recent post you referred to those cells by labels (perhaps a column heading) and those labels themselves are
- Completed
- Pending
- Pending Approval
meaning that whoever is entering the data needs to pick a column that corresponds to the state of pending or completion--in other words., that entry already presumes the answer to the question this IF function is proposing to answer--so WHY do you even need a formula that answers the question??!!
That is a serious question.That's a big part of why I asked before if it is possible for you to post a copy of the spreadsheet itself. Especially since two of the cells apparently are going to contain dates that reflect--or appear to reflect--almost identical data: "Pending" and "Pending Approval" It just sounds like a confusing design, and part of what NikolinoDE and I (and others on this site) offer is help in making sure spreadsheet designs make sense, not just that formulas work.
SGeorgie Seeing that image does not allay my questions about the basic design....that aside, I'm wondering if you could just employ the IFS function, rather than nesting IFs several levels deep. The hyperlink here will take you to a resource that describes IFS.
- i attach dummy data
- i am not sure i can wrk out how to do IFS as all examples are based on 1 cell not multiple
i am not sure i can wrk out how to do IFS as all examples are based on 1 cell not multiple
Those are only examples, not the only possible ways to construct the conditions in an IFS formula.
The key point, as contrasted with IF, is that in IFS you have to enter the conditions in an order/sequence in recognition of the fact that:
- The IFS function does not go through each and every condition.
- The IFS function stops evaluating as soon as it meets a condition/consequence in which the condition is satisfied.
Also important, once you've got it working, IFS is a lot more "readable" to the human eye and brain, than a deeply nested set of IF conditions.
You can easily construct something like
=IFS(AND(condition1,condition2),consequenceA,OR(condition3,condition4),consequenceB,.......)making it as complicated with those AND / OR combinations as the situation warrants.
- so my formula which works for the most part is
=IFS(OR(AND(ISBLANK(K4),ISBLANK(O4),ISBLANK(U4))),"Not Completed",OR(K4>=TODAY()),"Compliant",OR(AND(K4<TODAY(),OR(O4<TODAY(),ISBLANK(O4)),OR(U4<TODAY(),ISBLANK(U4)))),"Expired",OR(AND(K4<TODAY(),O4>=TODAY())),"Pending",OR(AND(K4<TODAY(),U4>=TODAY())),"Pending")
but it not showing Expired for any row
and if there is a completed expiry date in the past AND a pending in the future it says Compliant but it should say Pending
Please can you help get this formula right Mathetes and/or Oliver
