My AVERAGE formula is including blank columns, giving inaccurate results. ty!

AppSheet AppSheet Q&A
1

This formula (entered into App Formula) seems to be including blank columns in the calculation giving inaccurate results. How do I adjust it so that it ignores blank columns that have not been entered yet? Sometimes it seems to work, but then when I fill in more columns it stays the same or is inaccurate. I need it to remain accurate throughout the day. tyvm!!

All data gathering columns are set as “Number,” and the AVERAGE column is set as “Decimal.”

AVERAGE(LIST([Wake mood], [Mood-6a], [Mood-8a], [Mood-10a], [Mood-12n], [Mood-2p], [Mood-4p], [Mood-6p], [Mood-8p], [Mood-10p], [End mood]) - LIST (0))

I’ve read the other Topics about this but haven’t seen the solution yet. ty!

@Steve :innocent:

2

I might have found the Solution in this excellent comment by @SkrOYC from 2022. Sorry I missed this earlier and doubled-up this Topic.

Adding the -LIST(““) to my formula seems to be ignoring the blank columns now. I am not allowing zeros in my ENUM data columns, so I’m not certain that I need the -LIST(0) part, but I’m leaving it in for now.

*However, it still seems to be calculating incorrectly, it gives 2.50 when the math should be 2.43.

Thank you Oscar & AppSheet!

image
image972×517 29.6 KB

3

The problem here is list subtraction also removes duplicate values, so if [Mood-10a] and [Mood-2p] have the same value, the second occurrence will be removed, AVERAGE() won’t see it, and your result will be wrong.

Since you have only a relatively small number of columns, you’d probably be better off with:

AVERAGE(
  (LIST([Wake mood]) - LIST(NUMBER("")))
  + (LIST([Mood-6a]) - LIST(NUMBER("")))
  + (LIST([Mood-8a]) - LIST(NUMBER("")))
  + (LIST([Mood-10a]) - LIST(NUMBER("")))
  + (LIST([Mood-12n]) - LIST(NUMBER("")))
  + (LIST([Mood-2p]) - LIST(NUMBER("")))
  + (LIST([Mood-4p]) - LIST(NUMBER("")))
  + (LIST([Mood-6p]) - LIST(NUMBER("")))
  + (LIST([Mood-8p]) - LIST(NUMBER("")))
  + (LIST([Mood-10p]) - LIST(NUMBER("")))
  + (LIST([End mood]) - LIST(NUMBER("")))
)
4 Likes
4

Oh this is GREAT, once again Sir Steve! I would have never realized that my formula would exclude duplicates, which, with 1,2,3,4,5 as my only ENUM choices is guaranteed!

You must have an extra brain in there somewhere, way smarter than any AI, which I did ask for help before asking you/AppSheet people. lol

Thank you so much!

2 Likes
5

I am also (in other columns) ranking & tracking data & averages for Achievement, Closeness, Enjoyment and Average All, but I can adjust your thoughtful Solution to fit all of those.

[Avg All] will include 38 or 39 columns of the same EMUM data, if that makes a difference, since you mentioned that I have a “small number of columns.”

Thanks again!

1 Like
6

While I have your attention Steve, I also have a [Day Score] that I’d like to set up similar to how you see stock prices displayed as compared to their previous prices, something like +3.25% displayed in green, or -1.74% displayed in red as compared to previous Average data. In case you have guidance on that related calculation. I am not really certain on the best way to compare today’s Average rankings to previous Averages, or how this is actually handled in the stock Tickers. I’d like to make it familiar to how people are used to seeing compared data. tyvm!

I suppose I could bump this question to a new Topic, but it might be easier here. I could still add it as a new Topic if it makes sense.

7

New topic, please.

2 Likes
8

Will do, thanks!

2 Likes
9

Correct!

2 Likes
10

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.