IF THEN formula

AshelyBee

I'm trying to incorporate a formula in one of my sheets. I'm going to use terms as if I were referring to Excel since I'm still getting familiar with the terms in Smartsheet.

For example, if cell A2 contains a date but cell B2 doesn't have a date then I want the value in cell C2 to return "50%". If cell B2 contains a date then I want cell C2 to return "100%".

I've tried using a then IF THEN formula that I'm familiar with in Excel but I'm not too sure that it applies in Smartsheet. Does anyone have any suggestions?

Devin Lee

Hey @AshelyBee

This should work for you. You will need to change the A@row and B@row to the column names you are using. Smartsheet doesn't use percentage in their formulas so you will need to select percentage as a format for the cells in the column.

=IF(ISDATE(B@row), 1, IF(ISDATE(A@row), 0.5, ""))

This is another option in which 50% is either A or B being filled out and 100% being both

=IF(AND(ISDATE(B@row), ISDATE(A@row)), 1, IF(OR(ISDATE(B@row), ISDATE(A@row)), 0.5, ""))

Jeff Reisman

In Smartsheet, use ColumnName@row to designate the value in a column on this row. So we'll go with ColumnA, ColumnB, and ColumnC as the column names. ColumnC should be a Text/Number type column, and set it as a percent format by clicking the percent icon in the toolbar:

Then try this formula in ColumnC. We're essentially adding the result values of two formulas together:

=IF(ISDATE(ColumnA@row), 0.5) + IF(ISDATE(ColumnB@row), 0.5)

The logic is: If the value in the ColumnA cell on this row is a Date, set the value of this formula to .5 (otherwise do nothing,) PLUS, if the value in the ColumnB cell on this row is a Date, set the value of this formula to .5 (otherwise, do nothing.)

If both columns have date values, add .5 to .5 to equal 1, which in a percent column equals 100%. If only one has a date value, add .5 to nothing to get 50%. If neither is a date, there's 0% in ColumnC.

Devin Lee

Hey @AshelyBee

This should work for you. You will need to change the A@row and B@row to the column names you are using. Smartsheet doesn't use percentage in their formulas so you will need to select percentage as a format for the cells in the column.

=IF(ISDATE(B@row), 1, IF(ISDATE(A@row), 0.5, ""))

This is another option in which 50% is either A or B being filled out and 100% being both

=IF(AND(ISDATE(B@row), ISDATE(A@row)), 1, IF(OR(ISDATE(B@row), ISDATE(A@row)), 0.5, ""))

Jeff Reisman

In Smartsheet, use ColumnName@row to designate the value in a column on this row. So we'll go with ColumnA, ColumnB, and ColumnC as the column names. ColumnC should be a Text/Number type column, and set it as a percent format by clicking the percent icon in the toolbar:

Then try this formula in ColumnC. We're essentially adding the result values of two formulas together:

=IF(ISDATE(ColumnA@row), 0.5) + IF(ISDATE(ColumnB@row), 0.5)

The logic is: If the value in the ColumnA cell on this row is a Date, set the value of this formula to .5 (otherwise do nothing,) PLUS, if the value in the ColumnB cell on this row is a Date, set the value of this formula to .5 (otherwise, do nothing.)

If both columns have date values, add .5 to .5 to equal 1, which in a percent column equals 100%. If only one has a date value, add .5 to nothing to get 50%. If neither is a date, there's 0% in ColumnC.

AshelyBee

https://community.smartsheet.com/discussion/comment/315796#Comment_315796

This works! Thank you. Is there a way for me to drag the formula to other rows or would I have to copy and paste to each row that this formula applies to?

Jeff Reisman

If the formula should be in every row in that column, then just right click on the formula and select "Convert to Column Formula"

If you only need it in certain rows, click into the cell to edit the formula, select the entire formula and then hit Ctrl-C. Select the rows in that column that you want to copy into and Ctrl-V. You can Ctrl-V on any row in that column you want the formula to be in. Using the "@row" for the cell references in the formula lets you do it this way!

Glad I could help!

AshelyBee

https://community.smartsheet.com/discussion/comment/315825#Comment_315825

Works like a charm! Thank you!

Jeff Reisman

@Devin Lee

one thing I love about the Smartsheet community is seeing the different ways people approach the same problem!

It's cool that this

=IF(ISDATE(ColumnA@row), 0.5) + IF(ISDATE(ColumnB@row), 0.5)

and this

=IF(AND(ISDATE(B@row), ISDATE(A@row)), 1, IF(OR(ISDATE(B@row), ISDATE(A@row)), 0.5, ""))

do the exact same thing.

JBolan

I have read this entire thread, and cannot seem to fix this formula: =if(Intro@row),("yes"), 6, 0) . I've moved the spacing around (no space before the numbers etc) and the parentheses / brackets and everything I can think of. What am I missing?

Jeff Reisman

@JBolan The IF function is looking for a logical statement that it can test. The syntax is IF(logical statement, value if true, value if false). Also, capitalize the functions in your formulas.

So try this:

=IF(Intro@row = "yes", 6, 0)

English: IF the value in the Intro column is equal to "yes", set this cell to 6, otherwise set it to 0.

Just FYI: Logical statements can be regular expressions like Intro@row = "yes", or the results of functions, such as ISTEXT(Intro@row). The first is testing whether the cell value matches something specific, the second is testing if the cell value is composed of text (vs a number value or boolean field.)

JBolan

got it. so spaces between row and = plus between = and "yes". any chance Smartsheet is considering changing their spacing/punctuation rules to match excel? or at least improve tool tips to cover that part?

Jeff Reisman

@JBolan Spacing in formulas can be a crapshoot, but I find there is generally not an issue having spaces around operators and after commas within a formula; it also aids in readability. Smartsheet likes it this way too, and will even add spacing automatically. For instance, I can type this as a formula:

and once I hit enter, I see the correct result... and also that Smartsheet has corrected the spacing:

I would say the chances of Smartsheet changing this to match Excel is effectively ZERO. Smartsheet's spacing is far easier to read and use.

If you have a suggestion for improving tool tips, by all means share it HERE, but I'm not sure how much better it can get than what shows up for each function after you type the function and the opening parentheses. I mean, you've got the syntax, an example, a summary, an explanation of the arguments, and a link to the function's help page all right there! The highlighted portion of the syntax even shows you what part of the formula you are currently in!

BTW, you can bookmark the links in my signature for the function help pages and the formula error message page. There also the awesome Formula Handbook!

JBolan

it didn't correct the spacing for me. that was the only change made (I'd already dropped the () while waiting for a response

Jeff Reisman

https://community.smartsheet.com/discussion/comment/378022#Comment_378022

If you just removed the extraneous parentheses from =if(Intro@row),("yes"), 6, 0) to make it =if(Intro@row,"yes", 6, 0) you would still have an invalid syntax (#INCORRECT ARGUMENT SET error), but even so Smartsheet should correct the spacing to add a space between Intro@row, and "yes".

-> then hit enter:

-> then double click in the cell and see:

JBolan

thanks Jeff. knowing that's in there would solve one of the most annoying features w/smartsheet, so I'll keep an eye out for it