Help with this IF this then this formula

Please help me figure out what I am doing wrong with this formula:


=IF((([Size of Install]@row = "Small - Single Server, no HA, no NLB, HPAC PP+IA+, no HPAC Scan, no HPAC mobile connector, includes PPM/PDM, WJA, UPD, 500 printers or more - remote/travel extra, for small AlertX configuration only, single server for PP, IA2", 16), IF([Size of Install]@row = "Medium - Multi Server, HA, NLB, HPAC PP+IA+, HPAC Scan, HPAC mobile connector, includes PPM/PDM, WJA, UPD, 1000-5000 printers- remote/travel extra, Global, Azure/AWS", 24), IF([Size of Install]@row = "Large - Multi Server, HA, NLB, HPAC PP+IA+, HPAC Scan, HPAC mobile connector, includes PPM/PDM, WJA, UPD, >5000 printers- - remote/travel extra, Global, Azure/AWS", 40)))



Tags:

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Lets try cleaning up a few parenthesis to see if that helps first...

    =IF([Size of Install]@row = "Small - Single Server, no HA, no NLB, HPAC PP+IA+, no HPAC Scan, no HPAC mobile connector, includes PPM/PDM, WJA, UPD, 500 printers or more - remote/travel extra, for small AlertX configuration only, single server for PP, IA2", 16, IF([Size of Install]@row = "Medium - Multi Server, HA, NLB, HPAC PP+IA+, HPAC Scan, HPAC mobile connector, includes PPM/PDM, WJA, UPD, 1000-5000 printers- remote/travel extra, Global, Azure/AWS", 24, IF([Size of Install]@row = "Large - Multi Server, HA, NLB, HPAC PP+IA+, HPAC Scan, HPAC mobile connector, includes PPM/PDM, WJA, UPD, >5000 printers- - remote/travel extra, Global, Azure/AWS", 40)))


    Are you getting some kind of error message, or are you getting an unexpected output?

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    Hi, Stacy.

    Your formula had an extra pair of ( ). It should work after you remove them.

    But also try the following...

    = IF( FIND("Small",[Size of Install]@row), 16, IF( FIND("Medium",[Size of Install]@row), 24, IF( FIND("Large",[Size of Install]@row), 40, "")))

    In plain English... If you find "Small" in [Size of Install]@row then 16. If you find "Medium" in [Size of Install]@row, then 24. If you find "Large" in [Size of Install]@row then 40. Otherwise, use "".

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Toufong Vang There should be 3 closing parenthesis at the end of the formula because there are 3 IF statements. You also need to include some kind of argument for the FIND functions as they only output a numerical value.


    =IF(FIND("Small", [Size of Install]@row) > 0, 16, ..........................)))

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 05/25/22

    @Paul Newcome is correct about the closing parentheses. In @Stacy Stoffel's formula, the extra parenthesis is here after "16", which Paul corrected.


    As for how FIND() is used in my formula, FIND("Small", [Size of Install]@row) returns the number 1 because that is the starting position of the text, and 0 if the text is not present. In the IF() statement, 1 and 0 are Boolean equivalents of "true" and "false". We're essentially saying, "if TRUE then 16, else if TRUE then 24, else if TRUE then 40, else BLANK"

  • Stacy Stoffel
    Stacy Stoffel ✭✭✭✭✭

    This suggestion got rid of the "unparseable" error but didn't populate anything in the cell



  • Stacy Stoffel
    Stacy Stoffel ✭✭✭✭✭

    This one worked :) YAY!


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Stacy Stoffel In the formula I provided, I only got rid of some extra parenthesis. Because you have so much "specific text" listed for each of the criteria, it may be that there is a typo somewhere in each of them. That would be the reason why it would output a blank as opposed to an error.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!