Formula Assistance

Options

Good Morning Community,

I was hoping that someone could validate the below IFAND formula as I am getting an error which I cant resolve:

=IF(Status@row = "Green", "On-track"), ifand(Status@row="Yellow", "At-risk"), ifand (Status@row="Red", "Not on track"), if(Status@row="blue", "complete")))

Tags:

Best Answer

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 06/10/21 Answer ✓
    Options

    Hi, Rory.

    Try this...

    =IF(Status@row = "Green" ,"On-Track",IF(Status@row="Yellow" ,"At-risk" ,IF(Status@row="Red" ,"Not on track" ,"complete")))

    Here's what the formula looks like when formatted so that it's easier to read:

    IF(Status@row = "Green"
    	,"On-Track"
    	,IF(Status@row="Yellow"
    		,"At-risk"
    		,IF(Status@row="Red"
    			,"Not on track"
    			,"complete"
    			)
    		)
    	)
    


    Just to be different, you can also use SUBSTITUTE() since this is text and there won't be a chance of repeated statuses in the same cell.

    =SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(Status@row, "Green", "On-Track"), "Yellow", "At-Risk"),"Red", "Not on Track"),"Blue", "Complete")

    Formatted for easier reading:

    =SUBSTITUTE( 
    	SUBSTITUTE( 
    		SUBSTITUTE( 
    			SUBSTITUTE(Status@row, "Green", "On-Track")
    			, "Yellow", "At-Risk")
    		,"Red", "Not on Track")
    ,"Blue", "Complete")
    

    And here's documentation for Smartsheet functions, https://help.smartsheet.com/functions.

    Cheers!

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 06/10/21 Answer ✓
    Options

    Hi, Rory.

    Try this...

    =IF(Status@row = "Green" ,"On-Track",IF(Status@row="Yellow" ,"At-risk" ,IF(Status@row="Red" ,"Not on track" ,"complete")))

    Here's what the formula looks like when formatted so that it's easier to read:

    IF(Status@row = "Green"
    	,"On-Track"
    	,IF(Status@row="Yellow"
    		,"At-risk"
    		,IF(Status@row="Red"
    			,"Not on track"
    			,"complete"
    			)
    		)
    	)
    


    Just to be different, you can also use SUBSTITUTE() since this is text and there won't be a chance of repeated statuses in the same cell.

    =SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(Status@row, "Green", "On-Track"), "Yellow", "At-Risk"),"Red", "Not on Track"),"Blue", "Complete")

    Formatted for easier reading:

    =SUBSTITUTE( 
    	SUBSTITUTE( 
    		SUBSTITUTE( 
    			SUBSTITUTE(Status@row, "Green", "On-Track")
    			, "Yellow", "At-Risk")
    		,"Red", "Not on Track")
    ,"Blue", "Complete")
    

    And here's documentation for Smartsheet functions, https://help.smartsheet.com/functions.

    Cheers!