Forum Discussion
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(ISBLANK([COLUMN2])), "Phase 2",
IF(ISBLANK([COLUMN2]), AND(NOT(ISBLANK([COLUMN1])), "Started",
IF(IS(BLANK([COLUMN1]), AND(NOT(ISBLANK([COLUMN0])), "Assigned", "Awaiting Assignment"))))))))))))
COULMN1-5 are date columns and COULMN0 is a choice column.
I can get them to work separately but I can't get to work all together. Any tips on what I am doing wrong?
- 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"))- 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)
