Trying to Create a Dynamic Formula and need some guidance

GILLL023

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?

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!

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!

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