Malformed IF statement - BUT the colors are fine!

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:

Malformed IF.jpg

Error Message

Accepted answers

SPetrie

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

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

Malformed IF-Cmt.jpg

0427-Newt.yxmd

All comments

Luke_C

Hi @Newt

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

SPetrie

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

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

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

Malformed IF-Cmt.jpg

0427-Newt.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703