Malformed IF statement - BUT the colors are fine!

[Newt]

I've written an IF statement where the colors are fine -- no black or squiggly underlines anywhere -- but in the workflow it gets flagged as a malformed If statement at a Character position that is Larger than the sum total of all characters and spaces in the statement (as counted by Word).

I have to average numbers for the "three most recent years" in a situation where some years may have null entries:

[Luke_C]

Hi @Newt 

You don't need the standalone 'if's after each elseif

[SPetrie · Accepted solution]

I dont know how you have the flow setup, but I feel like you may be able to do away with the formula altogether. Something like this would be easier to read and update.

[Qiu · Accepted solution]

@Newt 
As commented by @Luke_C , we dont need the "If" after "elseif".

And I think we should go another not so brutal-force way.
First we can transpose the data and keep the order of year then remove null values, then take the first three, which gives the lastest 3 years of non-null.
At last, with a Summarize and Join tool, you are good to go.

This one is dynamic, still will work even the years increases or the column names change.

[Qiu]

@SPetrie 
I think we almost replied at the same time and yet the same approach.

I am now feeling much confident. :)

[Newt]

THANKS!

I had hoped for an elegant way to do this, vs. the laborious/error-prone way I was using.  Your way is very elegant.  Thanks again!

[Newt]

THANKS!

I had hoped for an elegant way to do this, vs. the laborious/error-prone way that I was using.  Your way is very elegant.  Thanks again!

[Newt]

Thanks so much for showing the *right* way to get the answer.  My IF statements were a kludge....

[Qiu]

@Newt 
Glad to be helpful.