Need help with an IF ELSEIF formula

[Liz001]

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

[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 · Accepted solution]

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.

[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!