IF statement returns #UNPARSEABLE

Melissa Torrez

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

Brett Wyrick

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.

L_123

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

@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