Complicated IF formula

I am trying to create a column formula that does the following. If the application is Declined or Withdrawn then cost is 0. If not, then based on each course we offer, it puts the cost of the course. This is the formula I have as of right now but it is very messy and not working.

=OR(IF(IF(OR([Application Status]@row = "Declined", [Application Status]@row = "Withdrawn", [Application Status]@row = "Expired"), 0), IF([PLTW Course Teacher was Trained In]@row = "Launch Classroom Teacher Training", 500), IF([PLTW Course Teacher was Trained In]@row = "Launch Lead Teacher Training", 700), IF([PLTW Course Teacher was Trained In]@row = OR("Energy and the Environment", "Green Architecture", "Magic of Electrons", "Science of Technology"), 750), IF([PLTW Course Teacher was Trained In]@row = OR("App Creators", "Automation and Robotics", "Computer Science for Innovators and Makers", "Design and Modeling", "Flight and Space", "Medical Detectives"), 1200), 2400))

Find more posts tagged with

Formulas

Accepted answers

All comments

MichaelTCA

Hello @LoganCraft

I recommend breaking down each piece of the function to make sure they are entered in correctly.

Off the bat, your second OR function (First condition) ends after "Expired" and then you close the second IF function immediately.

IF(OR([Application Status]@row = "Declined", [Application Status]@row = "Withdrawn", [Application Status]@row = "Expired"), 0)

You have a condition and when it's true, enter 0, with no false statement (which is ok). Then you immediately close the IF statement. This will result in, If the application status is one of these options, enter 0, or false. Which is technically the same thing if the column is a Boolean, but the function will error if you are mixing the results of variable types. A Text/Number column would display FALSE, if the IF function resulted in a false statement. If you are looking for the number 0 to apply to something else, you will error every function using that value. Such as a SUM(1,0)=1. SUM(FALSE,0) is an error.

Break it down into individual IF statements and then piece them together. Make sure each function works before you nest it into another function.

LoganCraft

https://community.smartsheet.com/discussion/comment/418830#Comment_418830

So these individual statements all work separately now.

=IF(OR([Application Status]@row = "Declined", [Application Status]@row = "Withdrawn", [Application Status]@row = "Expired"), 0)

=IF([PLTW Course Teacher was Trained In]@row = "Launch Classroom Teacher Training", 500)

=IF([PLTW Course Teacher was Trained In]@row = "Launch Lead Teacher Training", 700)

=IF(OR([PLTW Course Teacher was Trained In]@row = "Energy and the Environment", [PLTW Course Teacher was Trained In]@row = "Green Architecture", [PLTW Course Teacher was Trained In]@row = "Magic of Electrons", [PLTW Course Teacher was Trained In]@row = "Science of Technology"), 750)

=IF(OR([PLTW Course Teacher was Trained In]@row = "Automation and Robotics", [PLTW Course Teacher was Trained In]@row = "App Creators", [PLTW Course Teacher was Trained In]@row = "Computer Science for Innovators and Makers", [PLTW Course Teacher was Trained In]@row = "Design and Modeling", [PLTW Course Teacher was Trained In]@row = "Flight and Space", [PLTW Course Teacher was Trained In]@row = "Magic of Electrons"), 1200)

I'm confused about how to tie them all together with a nested formula. First check 1, if true, output; then check 2 if true, output; then check 2, if true, output; then check 4, if true, output; then check 5, if true; output; the output the false statement at the end.

Dakota Haeffner

I don't typically open with a OR statement, give this a try.

=IF(OR([Application Status]@row = "Declined", [Application Status]@row = "Withdrawn", [Application Status]@row = "Expired"), 0, IF([PLTW Course Teacher was Trained In]@row = "Launch Classroom Teacher Training", 500, IF([PLTW Course Teacher was Trained In]@row = "Launch Lead Teacher Training", 700, IF(OR([PLTW Course Teacher was Trained In]@row = "Energy and the Environment", [PLTW Course Teacher was Trained In]@row = "Green Architecture", [PLTW Course Teacher was Trained In]@row = "Magic of Electrons", [PLTW Course Teacher was Trained In]@row = "Science of Technology"), 750, IF(OR([PLTW Course Teacher was Trained In]@row = "App Creators", [PLTW Course Teacher was Trained In]@row = "Automation and Robotics", [PLTW Course Teacher was Trained In]@row = "Computer Science for Innovators and Makers", [PLTW Course Teacher was Trained In]@row = "Design and Modeling", [PLTW Course Teacher was Trained In]@row = "Flight and Space", [PLTW Course Teacher was Trained In]@row = "Medical Detectives"), 1200, 2400)))))

MichaelTCA

@LoganCraft

Each IF function has a Condition, True Value, and a False Value. It doesn't look like any of the functions are using a False value so in this case, you would add each function to the false value of the previous. (If that makes sense)

=IF(OR([Application Status]@row = "Declined", [Application Status]@row = "Withdrawn", [Application Status]@row = "Expired"), 0, IF([PLTW Course Teacher was Trained In]@row = "Launch Classroom Teacher Training", 500, IF([PLTW Course Teacher was Trained In]@row = "Launch Lead Teacher Training", 700, IF(OR([PLTW Course Teacher was Trained In]@row = "Energy and the Environment", [PLTW Course Teacher was Trained In]@row = "Green Architecture", [PLTW Course Teacher was Trained In]@row = "Magic of Electrons", [PLTW Course Teacher was Trained In]@row = "Science of Technology"), 750, IF(OR([PLTW Course Teacher was Trained In]@row = "Automation and Robotics", [PLTW Course Teacher was Trained In]@row = "App Creators", [PLTW Course Teacher was Trained In]@row = "Computer Science for Innovators and Makers", [PLTW Course Teacher was Trained In]@row = "Design and Modeling", [PLTW Course Teacher was Trained In]@row = "Flight and Space", [PLTW Course Teacher was Trained In]@row = "Magic of Electrons"), 1200, "No False Statement Here")))))

LoganCraft

@Dakota Haeffner Thank you!!! That seems to have worked

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.