Forum Discussion
Solved
Need Help Getting Calculated Column Formula to Work Properly
=IF(NOT(ISBLANK([COLUMN5])),"Referred", IF(ISBLANK([COLUMN5]),AND(NOT(ISBLANK([COLUMN4])),"Completed", IF(ISBLANK([COLUMN4]), AND(NOT(ISBLANK([COLUMN3])),"Phase 3", IF(ISBLANK([COLUMN3]), AND(NOT(...
- I think you're over complicating your formula with the extra checks in the AND statements. Assuming that your Columns pass values forwards.. e.g. If Column 5 is the last column and if it's got a date in, you'll always show Completed, then this should work.
=IF(NOT(ISBLANK(Column5)),"Referred",IF(NOT(ISBLANK(Column4)),"Completed",IF(NOT(ISBLANK(Column3)),"Phase 3",IF(NOT(ISBLANK(Column2)),"Phase 2",IF(NOT(ISBLANK(Column1)),"Started",IF(NOT(ISBLANK(Column0)),"Assigned","Awaiting Assignment"))))))
Nested IFs are horrible to write, but I find it's easier if you build them up one IF at a time.. So for your scenario, I did the first IF first and then check that works.
=IF(NOT(ISBLANK(Column5)),"Referred","IF FAIL VALUE")
The IF will finish as soon as it sees a value in Column 5, so we don't need the AND statements, and we can just replace the "IF FAIL VALUE" entry with the next IF test. This let's you make sure that you've nested the correct amount of Brackets. Test each IF as you go..
=IF(NOT(ISBLANK(Column5)),"Referred",IF(NOT(ISBLANK(Column4)),"Completed","IF FAIL VALUE"))
I think you're over complicating your formula with the extra checks in the AND statements. Assuming that your Columns pass values forwards.. e.g. If Column 5 is the last column and if it's got a date in, you'll always show Completed, then this should work.
=IF(NOT(ISBLANK(Column5)),"Referred",IF(NOT(ISBLANK(Column4)),"Completed",IF(NOT(ISBLANK(Column3)),"Phase 3",IF(NOT(ISBLANK(Column2)),"Phase 2",IF(NOT(ISBLANK(Column1)),"Started",IF(NOT(ISBLANK(Column0)),"Assigned","Awaiting Assignment"))))))
Nested IFs are horrible to write, but I find it's easier if you build them up one IF at a time.. So for your scenario, I did the first IF first and then check that works.
=IF(NOT(ISBLANK(Column5)),"Referred","IF FAIL VALUE")
The IF will finish as soon as it sees a value in Column 5, so we don't need the AND statements, and we can just replace the "IF FAIL VALUE" entry with the next IF test. This let's you make sure that you've nested the correct amount of Brackets. Test each IF as you go..
=IF(NOT(ISBLANK(Column5)),"Referred",IF(NOT(ISBLANK(Column4)),"Completed","IF FAIL VALUE"))
=IF(NOT(ISBLANK(Column5)),"Referred",IF(NOT(ISBLANK(Column4)),"Completed",IF(NOT(ISBLANK(Column3)),"Phase 3",IF(NOT(ISBLANK(Column2)),"Phase 2",IF(NOT(ISBLANK(Column1)),"Started",IF(NOT(ISBLANK(Column0)),"Assigned","Awaiting Assignment"))))))
Nested IFs are horrible to write, but I find it's easier if you build them up one IF at a time.. So for your scenario, I did the first IF first and then check that works.
=IF(NOT(ISBLANK(Column5)),"Referred","IF FAIL VALUE")
The IF will finish as soon as it sees a value in Column 5, so we don't need the AND statements, and we can just replace the "IF FAIL VALUE" entry with the next IF test. This let's you make sure that you've nested the correct amount of Brackets. Test each IF as you go..
=IF(NOT(ISBLANK(Column5)),"Referred",IF(NOT(ISBLANK(Column4)),"Completed","IF FAIL VALUE"))
- Thanks Paul! I knew I had to be over complicating things.
SomeArmyVet you also had rogue commas before the AND. A more efficient way of writing your code would be:
=IF(AND(ISBLANK(Column5),NOT(ISBLANK(Column4))),"Completed",
IF(AND(ISBLANK(Column4),NOT(ISBLANK(Column3))),"Phase 3",
IF(AND(ISBLANK(Column3),NOT(ISBLANK(Column2))),"Phase 2",
IF(AND(ISBLANK(Column2),NOT(ISBLANK(Column1))),"Started",
IF(AND(ISBLANK(Column1),NOT(ISBLANK(Column0))),"Assigned",
"Awaiting Assignment")))))Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)- Thanks Rob! I will add that to my "how not to write a formula" notes!
