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?
Best Answer
-
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!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
Answers
-
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!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
-
Thanks so much for the feedback. Using what you sent i was able to get it working on my sheet!
-
Great!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!