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)))
Best 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, ..........................)))
Answers
-
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?
-
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 "".
-
@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, ..........................)))
-
@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" -
This suggestion got rid of the "unparseable" error but didn't populate anything in the cell
-
This one worked :) YAY!
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!