Help

Nested IF Function Help!

Topic Labels: Formulas
1447 4
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Weigle
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello!

I am starting to create an Asset Management System using AirTable (sidenote: It’s so much easier and cleaner looking here than anywhere else I’ve tried to build this!), and I’m a little stuck while trying to create a nested IF function for a Status column.

I have two columns, “Operational Status” and “Check Out Status”, to show whether an asset is working or not, and whether an operator in the field has checked it out for use. I’d like to have a third column that shows the overall status of each asset, that combines the statuses of the other two columns.

The conditions I’d like to have met are:

  • IF {Operational Status}=“In Service”, AND(IF({Check Out Status}=“Available”, “Available”))

  • If {Check Out Status}=“Checked Out”, “Checked Out”

  • IF{Operational Status}=“Out For Repair”, “Unavailable”

  • IF{Operational Status}=“Retired”, “Retired”

I feel like I’m close to figuring this out, but it’s stumped me since last night! Does anyone have any suggestions as to input this formula?

4 Replies 4
bdelanghe
7 - App Architect
7 - App Architect

You could use SWITCH() and IF():

SWTICH({Operational Status},
    “Out For Repair”,“Unavailable”,
    “Retired”,“Retired”
) &

IF({Check Out Status}=“Checked Out”,“Checked Out”) &

IF(AND(
    {Operational Status}=“In Service”,
    {Check Out Status}=“Available”),
    “Available"
)

The logic here is a little weird. Normally you’d use {Operational Status} & ':' & {Check Out Status} and make a SWITCH() based on the matrix of all possibilities. The equation above can lead to collisions, but if all statuses are truly exclusive of each other you should be fine.

Thank you for your help! I still can’t quite get it to work. I’m going to keep playing with it and see if I can come up with something.

Thanks again!!

I agree that SWITCH() would be easier than IF(), but in my view, IF() doesn’t need to be any part of it. Let me know if this creates the result you want:

SWITCH(
    {Operational Status},
    "In Service", {Check Out Status},
    "Out for Repair", "Unavailable",
    "Retired", "Retired"
)

That worked!!! Thank you so much!!! :grinning_face_with_big_eyes: