Need assistance with a formula not working properly - Thank you in advance!

Executive Alchemist

Hello everyone! I am hoping someone can assist me with identifying why this formula will not return "12" when [22. Advisory: Length of Multi-Year Project]@row is blank? What am I doing wrong? The formula correctly returns all other values. Any help would be greatly appreciated!

=IF([22a. Advisory: How will revenue be forecasted?]@row = "40/35/25 - initial with downward", "3", IF([22a. Advisory: How will revenue be forecasted?]@row = "15/30/35/20 - plan, execute, close", "4", IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", "2", IF([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", [22. Advisory: Length of Multi-Year Project]row, IF(ISBLANK([22. Advisory: Length of Multi-Year Project]@row), "12", "")))))

Andrée Starå

@Executive Alchemist

Excellent!

Glad we got it working!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Carson Penticuff

You are missing the @ symbol from this section:

[22. Advisory: Length of Multi-Year Project]row

If it still does not work, you may try plugging this formula in to verify the cells you believe to be blank are actually blank. If that column is populated through a formula, data shuttle, etc, they can sometimes be misleading.

Andrée Starå

Hi @Executive Alchemist

I hope you're well and safe!

You were missing one @.

Try something like this.

=IF([22a. Advisory: How will revenue be forecasted?]@row = "40/35/25 - initial with downward", "3", IF([22a. Advisory: How will revenue be forecasted?]@row = "15/30/35/20 - plan, execute, close", "4", IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", "2", IF([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", [22. Advisory: Length of Multi-Year Project]@row, IF(ISBLANK([22. Advisory: Length of Multi-Year Project]@row), "12", "")))))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Executive Alchemist

Thank you so much for the try! Now it is only returning "12" and nothing else. Any other suggestions? Really appreciate the try!

Andrée Starå

@Executive Alchemist

You're more than welcome!

Can you write out what your formula should do? I'm not sure it's correct.

Should the bolded section not have a number as the answer?

=

IF([22a. Advisory: How will revenue be forecasted?]@row = "40/35/25 - initial with downward", "3",

IF([22a. Advisory: How will revenue be forecasted?]@row = "15/30/35/20 - plan, execute, close", "4",

IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", "2",

IF([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", [22. Advisory: Length of Multi-Year Project]@row,

IF(ISBLANK([22. Advisory: Length of Multi-Year Project]@row), "12", "")))))

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Executive Alchemist

@Andrée Starå, thank you so much! If the field [22. Advisory: Length of Multi-Year Project]@row has a number, we want it to return that number in that field, if it is blank, then we want it to return "12". Does that make sense?

Carson Penticuff

If you are always getting "12" as a result, none of the statements for [22a. Advisory: How will revenue be forecasted?]@row are matching. Have you double checked to make sure there are no typos in those checks?

"40/35/25 - initial with downward"

"15/30/35/20 - plan, execute, close"

"70/30 - 1st Half"

"Evenly - fees divided by the number of months"

Executive Alchemist

Evenly - fees divided by the number of months

40/35/25 - initial with downward

15/30/35/20 - plan, execute, close

70/30 - 1st Half

Copy and pasted from the dropdown list. I have checked these many times as well. Not sure why they are not working. Now the formula is returning 12 for all except it is returning blank for "Evenly..." which should return "12". Do you think the formula is in the wrong order?

=IF([22a. Advisory: How will revenue be forecasted?]@row = "40/35/25 - initial with downward", 3, IF([22a. Advisory: How will revenue be forecasted?]@row = "15/30/35/20 - plan, execute, close", 4, IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", 2, IF([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", [22. Advisory: Length of Multi-Year Project]@row, IF(ISBLANK([22. Advisory: Length of Multi-Year Project]@row), 12, "")))))

Executive Alchemist

UPDATE: @Carson Penticuff and @Andrée Starå this now returns "12" for everything:

=IF([22a. Advisory: How will revenue be forecasted?]@row = " Evenly - fees divided by the number of months", [22. Advisory: Length of Multi-Year Project]@row, IF(ISBLANK([22. Advisory: Length of Multi-Year Project]@row), 12, IF([22a. Advisory: How will revenue be forecasted?]@row = " 40/35/25 - initial with downward", 3, IF([22a. Advisory: How will revenue be forecasted?]@row = " 15/30/35/20 - plan, execute, close", 4, IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", 2)))))

Im at a loss. Thanks anyone for any insight or suggestions!

Carson Penticuff

Is the leading space in each of these intentional?

" Evenly - fees divided by the number of months"

" 40/35/25 - initial with downward"

" 15/30/35/20 - plan, execute, close"

In order to always return 12, it leads me to believe [22. Advisory: Length of Multi-Year Project]@row is always viewed as blank.

Are you able to post a screenshot of your sheet layout? (redacted if necessary)

Executive Alchemist

FINAL UPDATE @Carson Penticuff and @Andrée Starå: This formula now works! Thank you all for your suggestions and input! Please notice the "NOT" added and seemed to do the trick! Have a lovely rest of your Friday and weekend!

=IF(AND([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", ISBLANK([22. Advisory: Length of Multi-Year Project]@row)), 12, IF(AND([22a. Advisory: How will revenue be forecasted?]@row = "Evenly - fees divided by the number of months", NOT(ISBLANK([22. Advisory: Length of Multi-Year Project]@row))), [22. Advisory: Length of Multi-Year Project]@row, IF([22a. Advisory: How will revenue be forecasted?]@row = "40/35/25 - initial with downward", 3, IF([22a. Advisory: How will revenue be forecasted?]@row = "15/30/35/20 - plan, execute, close", 4, IF([22a. Advisory: How will revenue be forecasted?]@row = "70/30 - 1st Half", 2, "")))))

Andrée Starå

@Executive Alchemist

Excellent!

Glad we got it working!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!