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

(Dufil OTA) #1

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

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

(Dufil OTA) #3

+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) #4

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

(Dufil OTA) #5

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

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.