Need help with an IF ELSEIF formula

I need to average only columns where there is a number that is not equal to zero. I tried the following IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF formula, but I am getting an error “Malformed function form” – what am I doing wrong here? This is the formula that is not working:

If [Q4 PY] = 0 THEN Average ([Q1], [Q2], [Q3], [Q4]) ELSEIF [Q4 PY] = 0 AND [Q1]=0 THEN Average ([Q2], [Q3], [Q4]) ELSEIF [Q4 PY] = 0 AND [Q1]=0 AND [Q2]=0 THEN Average ([Q3], [Q4]) ELSEIF [Q4 PY] = 0 AND [Q1]=0 AND [Q2]=0 AND [Q3]=0 THEN [Q4] ELSE Average ([Q4 PY],[Q1], [Q2], [Q3], [Q4]) ENDIF

Settings

Tips and Tricks

Accepted answers

Claje

Hi,

The method you're trying to use can work, but it has some challenges, and the logic can be a little complex as you have found.

I'd try to do this using the Transpose and Summarize tools instead. There's a great aggregate function in Summarize called "Average - Ignore 0's", which does exactly what you're looking for.

I've attached a quick example workflow.

claje_averageNo0s.yxmd

All comments

jrc7

Hello,

If I’m not mistaken, it’s because you have nested AND statements that are in the third to last argument with out brackets to contain them. Try this and see if it works:

IF [Q4 PY] = 0 THEN Average ([Q1], [Q2], [Q3], [Q4]) ELSEIF [Q4 PY] = 0 AND [Q1]=0 THEN Average ([Q2], [Q3], [Q4]) ELSEIF [Q4 PY] = 0 AND [Q1]=0 AND [Q2]=0 THEN Average ([Q3], [Q4]) ELSEIF ([Q4 PY] = 0 AND [Q1]=0) AND ([Q2]=0 AND [Q3]=0) THEN [Q4] ELSE Average ([Q4 PY],[Q1], [Q2], [Q3], [Q4]) ENDIF

Thableaus

Hi @Liz001

Are you sure your formula is not working?

I created the same columns here and simply copied and pasted what you have and it worked fine (no error).

Have you checked for data types? Are they all numeric types?

Cheers,

Claje

Hi,

The method you're trying to use can work, but it has some challenges, and the logic can be a little complex as you have found.

I'd try to do this using the Transpose and Summarize tools instead. There's a great aggregate function in Summarize called "Average - Ignore 0's", which does exactly what you're looking for.

I've attached a quick example workflow.

claje_averageNo0s.yxmd

Liz001

This worked! Thank you so much!

Liz001

Yes, I looked at that formula several times, and had another person look at my workflow. For some reason the formula did not work, but the solution that Claje posted worked great so I am good. Thank you so much for looking into this!

Liz001

I tried the brackets you suggested and the error went away, however, the formula did not do what I was expecting it to do, it was still averaging values that were zero. The solution that Claje posted worked. Thank you for your prompt reply. It was my first posting to the Community and you all were so eager to help!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703