# Need help writing a formula

Options
edited 08/24/23

In a column titled Status, I have one status as Active 1x and the other status as Active 2x. I created a new column to show values either 1 or 2 in a cell depending on the status. I need a formula that shows - if the status is Active 1x, then the cell should show '1', and if the status is Active 2x, the cell should show '2'.

Options

@Ken Armstrong The formula didn't work. I'm posting a screenshot to show my status column and the column that says test formula is where I need the values (1 or 2)

@Mike TV Here is the screen shot

• ✭✭✭✭✭✭
Options

@Dheeraj.c16 Try this now that I can see this snap I can correct:

=IFERROR(IF([Status*]@row = "Active (1x)", 1, IF([Status*]@row = "Active (2x)", 2)),"")

Let me know if that worked

Ken Armstrong

Smartsheet Operations Analyst, Carelon Medical Benefits Management

Be Firm! Be Fair! Be Friendly! Be Honest!!!

• ✭✭✭✭✭✭
Options

@Dheeraj.c16 Please make sure you accept my answer so other community members can see that this is resolved.

You are very welcome and feel free to reach out if you need anything else.

Ken Armstrong

Smartsheet Operations Analyst, Carelon Medical Benefits Management

Be Firm! Be Fair! Be Friendly! Be Honest!!!

• ✭✭✭✭✭✭
Options

Could you please provide a screenshot of your sheet so we know what 1x and 2x mean specifically? It could mean a few different things and I'm not sure if you really mean the cell has "1x" in it or something else.

• ✭✭✭✭✭✭
Options

=IF([Status]@row = "Active 1x", 1, IF([Status]@row = "Active 2x", 2)). This should work and you can set this as a column formula and let it run.

Ken Armstrong

Smartsheet Operations Analyst, Carelon Medical Benefits Management

Be Firm! Be Fair! Be Friendly! Be Honest!!!

• ✭✭✭✭✭✭
Options

If you want you can add an IFERROR statement so any blank cells will show blank and not no match. Here it is:

=IFERROR(IF([Status]@row = "Active 1x", 1, IF([Status]@row = "Active 2x", 2)),"")

Ken Armstrong

Smartsheet Operations Analyst, Carelon Medical Benefits Management

Be Firm! Be Fair! Be Friendly! Be Honest!!!

Options

@Ken Armstrong The formula didn't work. I'm posting a screenshot to show my status column and the column that says test formula is where I need the values (1 or 2)

@Mike TV Here is the screen shot

• ✭✭✭✭✭✭
Options

@Dheeraj.c16 Try this now that I can see this snap I can correct:

=IFERROR(IF([Status*]@row = "Active (1x)", 1, IF([Status*]@row = "Active (2x)", 2)),"")

Let me know if that worked

Ken Armstrong

Smartsheet Operations Analyst, Carelon Medical Benefits Management

Be Firm! Be Fair! Be Friendly! Be Honest!!!

• Options

@Ken Armstrong It worked. Thank you so much

• ✭✭✭✭✭✭
Options

@Dheeraj.c16 Please make sure you accept my answer so other community members can see that this is resolved.

You are very welcome and feel free to reach out if you need anything else.

Ken Armstrong

Smartsheet Operations Analyst, Carelon Medical Benefits Management