Advice setting up formula/system to compare current average data to previous average data

Stephen_Mattison · December 31, 2025, 8:11pm

I’m using this formula (thank you @Steve !) to accurately calculate an average Mood for today, ignoring any duplicates or blank fields.

But then in my [Day score] column, I want to compare today’s average Mood data to yesterday’s average Mood data. I’d like to set it up similar to how the stock ticker shows, for example: “+2.55%” or “-1.25%”. I can then use Format Rules to change [Day score] to either red, if negative, or green if positive.

Thank you!

*The bullet-points seen here should all be “+” symbols in the formula

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(“”)))
)

Yaskelly · December 31, 2025, 11:08pm

Best practice: store the daily average once, then calculate the day-over-day % change separately (cleaner formulas + better performance).

Daily average (reuse your existing formula)

Create a column (real or virtual) called [DailyAvgMood] and use your existing AVERAGE(LIST(...)) formula there.

Yesterday’s daily average
ANY(
SELECT(
MoodLog[DailyAvgMood],
[Date] = (TODAY() - 1)
)
)

Percentage change (Number)
IF(
OR(
ISBLANK([DailyAvgMood]),
ISBLANK([YesterdayAvg]),
[YesterdayAvg] = 0
),
“”,
([DailyAvgMood] - [YesterdayAvg]) / [YesterdayAvg]
)

Display as +2.55% / -1.25%
IF(
ISBLANK([DayScorePct]),
“”,
IF(
[DayScorePct] > 0,
“+” & TEXT([DayScorePct], “0.00%”),
TEXT([DayScorePct], “0.00%”)
)
)

Format Rules

Green → [DayScorePct] > 0
Red → [DayScorePct] < 0

Stephen_Mattison · January 1, 2026, 12:40pm

Okay, I’m having trouble with the last part of getting the percent change into [Day score].

I’ve got my virtual column [VYesterdayAvgAll] working with this formula: (ty!) But it is not displaying [Avg all] from yesterday, it’s just blank.

ANY(
SELECT(
Activity Diary[Avg All],
[Date] = (TODAY() - 1)
)
)

And I’ve got my virtual column [VPercentChangeAvgAll] working with this formula: (ty!)

IF(
OR(
ISBLANK([Avg All]),
ISBLANK([VYesterdayAvgAll]),
[VYesterdayAvgAll] = 0
),
“”,
([Avg All] - [VYesterdayAvgAll]) / [VYesterdayAvgAll]
)

Now I just need to display the percent change in my normal column [Day score]. It feels like this will be easy by just pulling [VPercentChangeAvgAll] into my normal column [Day score] but that didnt work. Your formula seen below breaks the App and gives me this Error “Column ‘Day score’ in Table ‘Activity Diary_Schema’ of Column Type ‘Percent’ has an invalid expression in the Formula field.‘=IF( ISBLANK([VPercentChangeAvgAll]), “”, IF( [VPercentChangeAvgAll] > 0, “+” & TEXT([VPercentChangeAvgAll], “0.00%”), TEXT([VPercentChangeAvgAll], “0.00%”) ) )’. TEXT function with two arguments requires a temporal type and text representing a date format” [Day score] is set to TYPE “Percent” and both virtual columns are TYPE “Decimal”

Also, I see where this formula will add the “+” for a positive change, but I don’t see where it will add the “-” for a negative change.

Also, why do I need the “0.00%” in there, it feels like this will display “0.00%” which I did get in [Day score] while I was adjusting the formula/trying it a little differently before breaking the App. (tyvm for your help, I think I am very close to having this work!!)

Also, it feels like I might want to include the “+” or “-” part into my virtual columns [VPercentChangeAvgAll], and then just display that calculation data in my normal column [Day score].

This formula below my normal column [Day score] breaks the App, with the Error seen above:

IF(
ISBLANK([VPercentChangeAvgAll]),
“”,
IF(
[VPercentChangeAvgAll] > 0,
“+” & TEXT([VPercentChangeAvgAll], “0.00%”),
TEXT([VPercentChangeAvgAll], “0.00%”)
)
)

Steve · January 1, 2026, 1:11pm

Oh, another AI bot.

Stephen_Mattison · January 1, 2026, 1:15pm

Oh darn! I thought something looked off. How/why are Bots adding help comments? Arrg! tyvm!

Steve · January 1, 2026, 1:33pm

Real (not virtual) column Day score change:

(
  [Day score]
  - LOOKUP(
    ([_THISROW].[date-column] - 1),
    "table",
    "date-column",
    "Day score"
  )
)

Replace table and date-column as appropriate.

Format rule condition for red: [Day score change] < 0.0 and for green: [Day score change] > 0.0.

Stephen_Mattison · January 1, 2026, 1:48pm

TYVM! Making changes now, Excited for this to work! TYVM!

Stephen_Mattison · January 1, 2026, 2:19pm

Okay, TYVM!! I’m feeling very close, but I probably adjusted your formula wrong because it’s just multiplying today’s [Avg All] and giving me “226%” instead of the difference from the previous day’s [Avg All].

In my screenshot, the top row is the newest day, the next row is the day before…

I used use a virtual column [VDate] to hold TODAY() because in my Sheet and displayed in my app (to save space) I have CONCATENATED [VDay] + [VDate] to give me “Wed, 12/31/2025” because seeing the day of the week will be historically helpful. I would not mind making this all normal columns if that would help.

And, since I am making the [Day score] Percent column green and red depending on positive or negative, I suppose I don’t need to bother including the “-” or “+” symbols, fine with me.

This is the formula I put in normal column [Day score] that is giving me “226%” instead of the comparison to yesterday’s [Avg All]. I did try using [Day score] in the top line of your formula (as you had it) but it gives me the same “226%”

(
[Avg All]

LOOKUP(
([_THISROW].[VDate] - 1),
“Activity Diary”,
“VDate”,
“Day score”
)
)

Stephen_Mattison · January 1, 2026, 2:34pm

Oops, sorry, I missed that you wanted me to add a new {Day score change] normal column.

I’m doing that now & will follow up. tyvm!!

Stephen_Mattison · January 1, 2026, 2:37pm

But I’m not sure if I do need a new [Day score change] column since I already have {Day score] where I want the change to appear. I have no problem adding a new normal column if I need to.

[Day score] should show the % change between today’s [Avg All] and yesterday’s [Avg All].

TY!

Steve · January 1, 2026, 3:19pm

This?

(
  [Avg All]
  / ANY(
    SELECT(
      Activity Diary[Avg All],
      (([_THISROW].[VDate] - 1) = [VDate])
    )
    + {1.0}
    - LIST(DECIMAL(""), 0.0)
  )
)

SELECT(...) gets all [Avg All] values for the day before [VDate] of this row. This will be a list of zero or more values, but should be only one.
... + {1.0} ensures the list has at least one non-zero value. We’ll be using this list to get a divisor, a divisor cannot be zero (cannot divide by zero).
... - LIST(DECIMAL(""), 0.0) removes empty and zero values from the list, since we can’t validly divide by either.
ANY(...) gives us the first value in the list constructed by (3). If the previous day had a non-zero [Avg All] value, it’ll be this; otherwise, it’ll be 1.0.
[Avg All] / ANY(...)) computes the percentage difference between [Avg All] of this row and the value computed by (4).

Steve · January 1, 2026, 3:52pm

YIKES! 4. . was a typo, for sure! Yikes, yikes, yikes! I need to proofread better!

I’ve corrected the expression above.

Stephen_Mattison · January 2, 2026, 3:23am

Hey Steve, great news, I’ve got it all figured out and working great, thanks again for your excellent help!

First, I calculate the change in score form yesterday to today with this formula in the normal column [Change Day score]

(
[Avg All]
/ ANY(
SELECT(
Activity Diary[Avg All],
(([_THISROW].[Short date] - 1) = [Short date])
)

{100}
LIST(DECIMAL(“”), 0.0)
)
)

And then I calculate the % of change with this formula in the normal column [Day score]

([Change Day score] - 1) * 100 & “%”

Then Format Rules change the color, exactly how I wanted, sweet! Thanks again!

*I have it sorted by date Descending, so the newest day is on top.

Steve · January 2, 2026, 1:40pm

Well done!

system · January 9, 2026, 1:41pm

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

Topic		Replies	Views
How to Calculate Percentage Change From Last Entry to Most Recent Entry? AppSheet Q&A expressions	18	42	July 2, 2020
Complex - Average 4 previous rows, divide, multiple, subtract, and round AppSheet Q&A ux , databases , expressions	2	15	July 25, 2021
Calculate meter reading difference between 2 different row entry AppSheet Q&A expressions	6	15	August 15, 2020

Advice setting up formula/system to compare current average data to previous average data

Daily average (reuse your existing formula)

AI Suggested topics