A smarter AVERAGE() and COUNTIF(). - I have a...

[CCMET_Metrotest]

A smarter AVERAGE() and COUNTIF(). - I have an issue where I cannot do a simple AVERAGE() computation.

I have a form where there are 20 number fields and when I do an Average() of all the 20 fields it will average based on 20 values even though I only put numbers on 5 of the fields. I am not sure why this is happening possibly the default value of a number is 0? and this 0 is placed in the field that’s why it gets counted?

Maybe we can have a smarter AVERAGE() function? OR a COUNTIF() function that we can specify to count fields that have values “> 0” . I have checked the docs and appsheet does not seem to have the COUNTIF() function.

[Aleksi_Alkio]

Have you tried to use suitable SELECT formula which will count these “blank” values away?

[CCMET_Metrotest]

@Aleksi_Alkio Hey Aleksi nice talking to you again. I have tried a select statement but I get an error "LIST has elements of mismatched types " So I have “tried” but my method is wrong.

The result of the expression below will be the divisor for the Sum of all the fields mentioned.

COUNT(SELECT({[Gauge Density 1 (kg/m3)],

[Gauge Density 2 (kg/m3)], [Gauge Density 3 (kg/m3)], [Gauge Density 4 (kg/m3)], [Gauge Density 5 (kg/m3)], [Gauge Density 6 (kg/m3)], [Gauge Density 7 (kg/m3)], [Gauge Density 8 (kg/m3)], [Gauge Density 9 (kg/m3)], [Gauge Density 10 (kg/m3)], [Gauge Density 11 (kg/m3)], [Gauge Density 12 (kg/m3)], [Gauge Density 13 (kg/m3)], [Gauge Density 14 (kg/m3)], [Gauge Density 15 (kg/m3)], [Gauge Density 16 (kg/m3)], [Gauge Density 17 (kg/m3)], [Gauge Density 18 (kg/m3)], [Gauge Density 19 (kg/m3)], [Gauge Density 20 (kg/m3)]}, [_THIS] > 0))

[CCMET_Metrotest]

@Aleksi_Alkio Just to confirm this worked like a charm! Thank You so much! You do not have an idea how much time I spent on this simple issue.

[Aleksi_Alkio]

Aaa… you are trying to calculate the average in the same record, correct?

[CCMET_Metrotest]

Yes you got it!

[Aleksi_Alkio]

1.0*([Gauge Density 1 (kg/m3)]+[Gauge Density 2 (kg/m3)])/(IFS([Gauge Density 1 (kg/m3)]>0,1)+IFS([Gauge Density 2 (kg/m3)]>0,1))

[CCMET_Metrotest]

Ah this makes sense I never thought I could add IFS.

but what is the 1.0 * for?

[Aleksi_Alkio]

If you want to have that average as decimal. The “problem” with the average in generally is because the app is treating the blank number field as zero.

[Adam-google]

Within arithmetic expressions blank values are treated as zeros. I don’t think there’s a concise way to count the non-blank fields within the row, as select is expecting a table and column to search rather than an inline list. You may have to do something like

SUM(

IF(ISBLANK([…1]), 0, 1),

IF(ISBLANK([…2]), 0, 1),

… )

[CCMET_Metrotest]

@Aleksi_Alkio Thanks for your help. You have saved the day again.

I am sure this will work so thank you in advance.

[Aleksi_Alkio]

You’re welcome