Formula Assistance

Rory K Power

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")))

Toufong Vang

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!

Toufong Vang

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!