Multiple If Statements

vjschmid

Nested formulas and I are not friends.

This works without putting more than one statement in here, what am I doing wrong and with multiple statements?

=IF(OR([How many xyz]@row = "0-3 xyz", "$50-100K")), IF(OR([xyz?]@row = "4-10 xyz", "100-300K")))

Mike TV

@vjschmid

Let's look at a simple example. Such as a sheet with video types and results for favorites:

Let's take the first Video Type of Movie and write a simple IF statement for it:

=IF([Video Type]@row="Movie", "Forest Gump")

We've only given the TRUE result for IF the Video Type is a Movie we want it to return Forest Gump. We haven't specified what to do if it's not a Movie. Next let's write a simple formula for Cartoon:

=IF([Video Type]@row="Cartoon", "Spongebob")

Again, only giving the TRUE result for if it's a Cartoon. Now let's write one for TV Show:

=IF([Video Type]@row="TV Show", "Game of Thrones")

Now that we know what we want to nest together, let's do that. To nest IF statements, you only give the TRUE result and then you start over with the next IF where the FALSE should be. Such as this:

=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones")

Some people only give Nested IF statements TRUE values and assume that they're always going to find a match and have a TRUE result. However, to be safe some people add in a double quotes so that if all TRUE results aren't met with any matches it'll just leave the cell empty. To do that you'd add those double quotes at the end like so:

=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones", "")

Additionally, you don't have to close the loop on all your statements with ))). SmartSheet will fill those in for you. So you could just type this into the cell and you'll notice SmartSheet will end with ))) for you.

=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones", ""

Lastly, for the formula example you provided I don't think you're after an IF formula with an OR function. You're probably after something like this:

=IF([How many xyz]@row = "0-3 xyz", "$50-100K", IF([xyz?]@row = "4-10 xyz", "100-300K"

Then just let SmartSheet put in the )) at the end for you.

Mike TV

@vjschmid

Let's look at a simple example. Such as a sheet with video types and results for favorites:

Let's take the first Video Type of Movie and write a simple IF statement for it:

=IF([Video Type]@row="Movie", "Forest Gump")

We've only given the TRUE result for IF the Video Type is a Movie we want it to return Forest Gump. We haven't specified what to do if it's not a Movie. Next let's write a simple formula for Cartoon:

=IF([Video Type]@row="Cartoon", "Spongebob")

Again, only giving the TRUE result for if it's a Cartoon. Now let's write one for TV Show:

=IF([Video Type]@row="TV Show", "Game of Thrones")

Now that we know what we want to nest together, let's do that. To nest IF statements, you only give the TRUE result and then you start over with the next IF where the FALSE should be. Such as this:

=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones")

Some people only give Nested IF statements TRUE values and assume that they're always going to find a match and have a TRUE result. However, to be safe some people add in a double quotes so that if all TRUE results aren't met with any matches it'll just leave the cell empty. To do that you'd add those double quotes at the end like so:

=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones", "")

Additionally, you don't have to close the loop on all your statements with ))). SmartSheet will fill those in for you. So you could just type this into the cell and you'll notice SmartSheet will end with ))) for you.

=IF([Video Type]@row="Movie", "Forest Gump", IF([Video Type]@row="Cartoon", "Spongebob", IF([Video Type]@row="TV Show", "Game of Thrones", ""

Lastly, for the formula example you provided I don't think you're after an IF formula with an OR function. You're probably after something like this:

=IF([How many xyz]@row = "0-3 xyz", "$50-100K", IF([xyz?]@row = "4-10 xyz", "100-300K"

Then just let SmartSheet put in the )) at the end for you.

vjschmid

Thank you! Worked like a charm!!! And thanks for the great explanation.

Randy Van Winkle

If an "IF" value is referencing a range of values in the formula, what is the syntax? i.e. If between 1-100, then X, If 100-200 then Y