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

(CCMET Metrotesting) #1

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) #2

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

(CCMET Metrotesting) #3

@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 Metrotesting) #4

@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) #5

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

(CCMET Metrotesting) #6

Yes you got it!

(Aleksi Alkio) #7

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 Metrotesting) #8

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

but what is the 1.0 * for?

(Aleksi Alkio) #9

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.


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


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

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

… )

(CCMET Metrotesting) #11

@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) #12

You’re welcome