IF statement returns #UNPARSEABLE

Options

Having trouble finding the error in my below formula. It is returning #UNPARSEABLE

=IF(([2021 Target]@row- [Start Date]@row)/365*12>=12, IF(SUM([May-21]@row:[Jun-20]@row)/12<0,0, SUM([May-21]@row:[Jun-20]@row)/12*12),IF(SUM([May-21]@row:[Jun-20]@row)/(([2021 Target]@row- [Start Date]@row)/365*12)<0,0,SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row)/365*12)*12))

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Options

    It's a little hard not knowing the context of what you're after, but I'll give it a shot.

    If you could post a screenshot that'd be helpful. If you're on a Windows machine, Windows Key+Shift+S will allow you to drag over your screen and take a screenshot with your mouse. Then you can paste (CTRL+V) in a comment here.

    Questions:

    • Is the formula "divide by 365 and multiply by 12" there to get months between dates?
      • Example in bold here: IF(([2021 Target]@row- [Start Date]@row)/365*12>=12
    • Is Target a Date type column?


    I think your issue is that the first "argument" for your very first IF statement is set up like this

    =IF(([2021 Target]@row- [Start Date]@row)/365*12>=12, __________________
        IF(SUM([May-21]@row:[Jun-20]@row)/12<0,0,SUM([May-21]@row:[Jun-20]@row)/12*12),
            IF(SUM([May-21]@row:[Jun-20]@row)/(([2021 Target]@row- [Start Date]@row)/365*12)<0,0,
                SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row)/365*12)*12))
    

    Basically, what this is saying is:

    1. IF the 2021 Target (Date?) minus the Start Date divided by 365 and multiplied by 12 is greater than or equal to 12, then: ________________[, otherwise?]
    2. IF the sum of rows May-21 to Jun-20 divided by 12 are less than 0, show 0, otherwise show the sum of rows May-21 to Jun-20 divided by 12 and multiplied by 12

    That big blank spot there is where I would usually expect to see a definitive answer, i.e. "0", or show "", etc.

    Hope that helps!

    If this answered your question, please press "Yes" below - it helps the community (and Google searchers) find solutions much faster.

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 07/29/21
    Options

    I'm not going to try to figure out what you are doing, as I don't have a clue just looking at the formula. I'll just fix/simplify the math, and get rid of formula errors to get you a result. * is my thoughts, # is formula suggestions. I've posted the final at the bottom.

    =IF(([2021 Target]@row- [Start Date]@row)/365*12>=12, 

    *if duration is greater than 1 year, then true, else false. More easily written as such:

    #=IF(([2021 Target]@row- [Start Date]@row)/365>=1,


    IF(SUM([May-21]@row:[Jun-20]@row)/12<0,

    *if sum of values between may and june /12 < 0, then true, else false. Only true when sum is negative, /12 can be taken out

    #IF(SUM([May-21]@row:[Jun-20]@row)<0,

    0, 

    SUM([May-21]@row:[Jun-20]@row)/12*12),

    *Sum of values between may and june. /12*12 = 1. Can be taken out for same result.

    #SUM([May-21]@row:[Jun-20]@row)),


    IF(SUM([May-21]@row:[Jun-20]@row)/(([2021 Target]@row- [Start Date]@row)/365*12)<0,

    *if sum from may to jun divided by target duration /365*12 < 0 then true, else false.

    *only true if one of the sums is negative. There is also a missing parenthesis that is a cause of the

    *unparseable. An equal way of writing it would be

    #if(SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row) < 0,

    0,

    SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row)/365*12)*12))

    *sum may to jun divided by target duration divided by 365*12*12

    *extra close parenthesis after first 12 with no open, that wouldn't effect the formula anyway

    #SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row)/365*12*12


    =IF([2021 Target]@row- [Start Date]@row)/365>=1,IF(SUM([May-21]@row:[Jun-20]@row)<0,IF(SUM([May-21]@row:[Jun-20]@row)<0,0,SUM([May-21]@row:[Jun-20]@row)),if(SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row) < 0,0,SUM([May-21]@row:[Jun-20]@row)/([2021 Target]@row- [Start Date]@row)/365*12*12

  • Melissa Torrez
    Melissa Torrez ✭✭✭✭✭
    Options

    @L_123@Brett Wyrick

    1. IF the 2021 Target (Date?) minus the Start Date divided by 365 and multiplied by 12 is greater than or equal to 12, then: _IF true then sum 12 months of revenue _______________[, otherwise?]
    2. IF the sum of rows May-21 to Jun-20 divided by 12 are less than 0, show 0, otherwise show the sum of rows May-21 to Jun-20 divided by 12 and multiplied by 12

    The blank is if true then sum May21@row:Jun20@row; if false then take the average of the months the employee has been with the company. For example, if it is 8 months then sum of revenue divided by 8*12

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!