Help with this IF this then this formula

Stacy Stoffel

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

Paul Newcome

@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

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

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

@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

@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

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

Stacy Stoffel

This one worked :) YAY!

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.