Trying to Create a Dynamic Formula and need some guidance

Hi,

Working on an advanced Dynamic Formula where selections on the sheet drive the formula results where we have several different criteria driving different selections.

[Type] a selection would be either "A" or "B" AND [Milestone] selection is driving a Reference column to indicate the YEAR (ex. Milestone 1 = FY23, Milestone 2 = FY24)

If [TYPE] = A and [Milestone] = "Milestone 1" Then enter [FY23Rate]#

If [TYPE] = A and [Milestone] = "Milestone 2" Then enter [FY24Rate]#

If [TYPE] = B and [Milestone] = "Milestone 1" Then enter [FY23RateB]#

[TYPE] = B and [Milestone] = "Milestone 1" Then enter [FY24RateB]#

So essentially my question is how do i write the formula so that it will use both of the two selections to factor into the result?

Find more posts tagged with

Accepted answers

Amber Eakin

Hi @GILLL023 - You'll need nested IFs with the AND function. Give this a try:

=IF(AND([TYPE], A, [Milestone], "Milestone 1"), [FY23Rate]#, IF(AND([TYPE], A, [Milestone], "Milestone 2"), [FY24Rate]#, IF(AND([TYPE], B, [Milestone], "Milestone 1"), [FY23RateB]#, IF(AND([TYPE], B, [Milestone], "Milestone 2"), [FY24RateB]#))))

Let me know if that works!

All comments

Amber Eakin

Hi @GILLL023 - You'll need nested IFs with the AND function. Give this a try:

Let me know if that works!

GILLL023

Thanks so much for the feedback. Using what you sent i was able to get it working on my sheet!

Amber Eakin

Great!

GILLL023

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.