Multiple formula

Hello everyone,

I tried to use multiple formulas, but for some reason it is working together (one-by-one they are working). What I want to do with this: I have a machine checking sheet, where the sheet send me a notification, if I work with any of my machines. Every machine has a group nr (1-5), what defines ow often I have to do the review. The times for every group: Group Nr 1: 243 days, Nr 2: 212 days, Nr 3: 182 days, Nr 4: 120 days, Nr 5: 90 days.

The format of the cells: The group Nr. is a drop down list, the Last check is date, and the output, what I want to get is also a date.

This is my formula:

=IF(AND([Group Nr]@row = "1"); [Last check]@row + 243), IF(AND([Group Nr]@row = "2"); [Last check]@row + 212), IF(AND([Group Nr]@row = "3"); [Last check]@row + 182), IF(AND([Group Nr]@row = "4"); [Last check]@row + 120), IF(OR([Group Nr]@row = "5"); [Last check]@row + 90)

The result is #UNPARSEABLE. What did I missed? How I should put functions together?

Bence

Find more posts tagged with

Formulas

Accepted answers

Samuel Mueller

@Bence In Smartsheet you will never need a semi colon in a formula. I'm also not sure you need the "and" piece, it doesn't seem like you are comparing two statements. try below

=IF([Group Nr]@row = "1", [Last check]@row + 243, IF([Group Nr]@row = "2", [Last check]@row + 212, IF([Group Nr]@row = "3", [Last check]@row + 182, IF([Group Nr]@row = "4", [Last check]@row + 120, IF([Group Nr]@row = "5", [Last check]@row + 90)))))

This is considered a nested if statement. An if statement has the syntax "=if(expression, result if true, result if false)", the expression can be an "and" statement, but you only need that if you need multiple expressions to be true for the overall expression to be true

=if(and(expression 1, expression 2), result if true, result if false)

a Nested if evaluates each expression in order

=if(expression1, result if true, if(expression2, result if true, if(expression3, result if true, result if false)))

It will check if expression1 is true, if it is the first true result will be displayed, if it is false the second expression will be evaluated, and so forth

All comments

Samuel Mueller

@Bence In Smartsheet you will never need a semi colon in a formula. I'm also not sure you need the "and" piece, it doesn't seem like you are comparing two statements. try below

=if(and(expression 1, expression 2), result if true, result if false)

a Nested if evaluates each expression in order

=if(expression1, result if true, if(expression2, result if true, if(expression3, result if true, result if false)))

It will check if expression1 is true, if it is the first true result will be displayed, if it is false the second expression will be evaluated, and so forth

Bence

@Samuel Mueller thank you, it worked! Also a bit feedback, it was misunderstandable to use semi colons. (I know, it was not the only problem) (the editor shows it with semi colons)

Samuel Mueller

strange the editor shows semi colons! But I'm glad you got it working. Let me know if you need anything else.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.