Multiple IF/AND Statements Formula

Brennan Montoni

Hi All,

I have a field "Annual Hours Saved". I want to create another column with the following logic:

If Annual Hours Saved is 0 to 10, then put 1

If Annual Hours Saved is 11 to 20, then put 2

If Annual Hours Saved is 21 to 50, then put 3

If Annual Hours Saved is more than 51, then put 4

Any help would be appreciated! Thanks in advance!

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

Hey @Brennan Montoni

Try this

=IF([Annual Hours Saved]@row<0, 1, IF([Annual Hours Saved]@row<=10,1,IF([Annual Hours Saved]@row<=20,2, IF([Annual Hours Saved]@row<=50, 3, IF([Annual Hours Saved]@row>50,4)))))

With no screenshot, I wasn't sure if your [Annual Hours Saved] could ever be negative. I accounted for that as a 1. Change if incorrect.

Kelly

All comments

Amanda Alv

Hi there,

Using multiple AND statements within an IF statement will get you there; the formula below will give you those answers and let you convert it to a column formula if that is more helpful.

=IF(AND([Annual Hours Saved]@row >= 0, [Annual Hours Saved]@row <= 10), "1", IF(AND([Annual Hours Saved]@row >= 11, [Annual Hours Saved]@row <= 20), "2", IF(AND([Annual Hours Saved]@row >= 21, [Annual Hours Saved]@row <= 50), "3", IF([Annual Hours Saved]@row >= 51, "4"))))

Kelly Moore

Hey @Brennan Montoni

Try this

=IF([Annual Hours Saved]@row<0, 1, IF([Annual Hours Saved]@row<=10,1,IF([Annual Hours Saved]@row<=20,2, IF([Annual Hours Saved]@row<=50, 3, IF([Annual Hours Saved]@row>50,4)))))

With no screenshot, I wasn't sure if your [Annual Hours Saved] could ever be negative. I accounted for that as a 1. Change if incorrect.

Kelly

Brennan Montoni

Thanks Kelly!

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.