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

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.

0 11 1,127
11 REPLIES 11

Have you tried to use suitable SELECT formula which will count these โ€œblankโ€ values away?

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

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

Aaaโ€ฆ you are trying to calculate the average in the same record, correct?

Yes you got it!

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

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

but what is the 1.0 * for?

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

SUM(

IF(ISBLANK([โ€ฆ1]), 0, 1),

IF(ISBLANK([โ€ฆ2]), 0, 1),

โ€ฆ )

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

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

Youโ€™re welcome

Top Labels in this Space