I am trying to use AVERAGE function to calcul...

[Dufil_OTA]

I am trying to use AVERAGE function to calculate average of some data inputs. however those inputs vary and some can be zero. appsheet does not have the AVERAGEA() Function in excel which checks if an input is zero and omits it from the average calculation… pls. how do I achieve this in appsheet?

[Steven_Coile]

AVERAGE(SELECT(MyTable[SomeColumn], ([SomeColumn] <> 0)))

[litdbis2]

What if I want to calculate the average value of three columns in the same record in the 4th column? Could you please help?

[luisandres]

Hi! I used this formula in a virtual column to get the average of 3 columns, and note if there are blank columns, I hope it helps you

([blanco 1]+[blanco 2]+[blanco 3])/sum(list(if(isnotblank([blanco 1]),1,0),
if(isnotblank([blanco 2]),1,0),
if(isnotblank([blanco 3]),1,0),

[Dufil_OTA]

+Steve Coile I have several columns, I used AVERAGE(LIST()) function, how do I use a LIST and a SELECT at the same time?

[Steven_Coile]

@Dufil_OTA It’s not clear what you mean. Could you elaborate?

[Dufil_OTA]

I have 8 columns, i want to calculate average of these columns. i used this formula: AVERAGE(LIST([L1S1], [L1S2], [L1S3], [L2S1], [L2S2], [L2S3], [L3S1], [L3S2])) however, some columns may not have data, i want the average function to omit columns without data.

[Steven_Coile]

The only way I can think of is kinda ugly:

AVERAGE(

(LIST([L1S1]) - LIST(""))

• (LIST([L1S2]) - LIST(""))

• (LIST([L1S3) - LIST(""))

• (LIST([L2S1]) - LIST(""))

• (LIST([L2S2]) - LIST(""))

• (LIST([L2S3]) - LIST(""))

• (LIST([L3S1]) - LIST(""))

• (LIST([L3S2]) - LIST("")) )

1. LIST([L1S1]) creates a list with a single item, the value from the L1S1 column.

2. (LIST([L1S1]) - LIST("")) removes all blank items from the one-item list. Since the list contains only a single item, “all blank items” will at most be only one item. If the one item isn’t blank, nothing is removed. The resulting “list” will then contain no or only one item.

3. … + (LIST(…) - LIST("")) repeats (1) and (2) for each additional column value, merging the small (zero or one-item) lists into a larger list. The resulting list will only contain the non-blank column values.

4. AVERAGE(…) averages the values in the constructed list.

It might occur to you, “why not create a single list from the column values then remove the blank ones with a single list subtraction?” A legitimate question, but because of the way list subtraction works in AppSheet, you could lose data in the computation. The list subtraction operation has the side-effect of removing duplicates from the resulting list. So if the original list is LIST(2, 2, “”, 7) and you subtract LIST("") from it, the result would be LIST(2, 7). The blank item as removed as intended, but so was the duplicate 2. List addition does not have this side-effect.

[luisandres]

I have the same problem, and I can't get the form to be empty, it automatically puts me a 0 if I don't enter a value, help

[Luis_Rodriguez_]

maybe its because your column is a NUMBER type. Just out of curiosity.. why cant the zero be in your data?

[luisandres]

hello namesake, because I want to average several values, and zero is an average value, I want it to be blank so that it is not considered in the average. I needed the column to be a number to be able to average them, (decimal in this case). Another problem that I have when I cannot leave the cell blank is that the zero that is set by default can be misinterpreted as an entered value.

[Luis_Rodriguez_]

SELECT(MyTable[SomeColumn], ([SomeColumn] <> 0))  <--- this may help