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

Options

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", "")))))

Best Answer

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Executive Alchemist
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    @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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Executive Alchemist
    Options

    @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
    Carson Penticuff ✭✭✭✭✭✭
    Options

    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
    edited 04/05/24
    Options

    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
    Options

    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
    Carson Penticuff ✭✭✭✭✭✭
    Options

    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
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    @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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!