Need help writing a formula

Options
Dheeraj.c16
Dheeraj.c16 ✭✭
edited 08/24/23 in Formulas and Functions

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'.

Best Answers

  • Dheeraj.c16
    Dheeraj.c16 ✭✭
    Answer ✓
    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



  • Ken Armstrong
    Ken Armstrong ✭✭✭✭✭✭
    edited 06/02/23 Answer ✓
    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

    Certified Smartsheet Administrator

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

  • Ken Armstrong
    Ken Armstrong ✭✭✭✭✭✭
    Answer ✓
    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

    Certified Smartsheet Administrator

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

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @Dheeraj.c16

    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.

  • Ken Armstrong
    Ken Armstrong ✭✭✭✭✭✭
    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

    Certified Smartsheet Administrator

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

  • Ken Armstrong
    Ken Armstrong ✭✭✭✭✭✭
    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

    Certified Smartsheet Administrator

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

  • Dheeraj.c16
    Dheeraj.c16 ✭✭
    Answer ✓
    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



  • Ken Armstrong
    Ken Armstrong ✭✭✭✭✭✭
    edited 06/02/23 Answer ✓
    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

    Certified Smartsheet Administrator

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

  • Dheeraj.c16
    Options

    @Ken Armstrong It worked. Thank you so much

  • Ken Armstrong
    Ken Armstrong ✭✭✭✭✭✭
    Answer ✓
    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

    Certified Smartsheet Administrator

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!