There's a quiet frustration in watching your data do 95 percent of what you need, only to stumble on the last five percent. That's exactly where this GROUPBY question lands, and it's worth pausing over because it exposes a gap that feels small but matters more than it looks. The user has cumulative sales data, they've already figured out how to pull the end-of-day totals with MAX, and then they hit a wall: the average value at the close of day isn't a maximum of anything. It's simply the last value in a sequence, and GROUPBY doesn't hand that to you by default.
So what's the practical move here? The answer isn't a clever new function or a workaround that feels like a hack. It's about reframing how you think about the problem. If MAX gives you the final cumulative total because the numbers only grow, the average doesn't behave that way. It can rise, fall, or hold steady as the day progresses. That means you can't ask for the largest value; you have to ask for the value tied to the latest timestamp. In plain terms, you need to identify the row where the time is the maximum for that date, then pull the average from that same row. That's a classic lookup pattern, and once you see it that way, the solution becomes less about fighting GROUPBY and more about pairing it with a formula that respects the data's actual structure.
The deeper point here is that the tool isn't failing you. GROUPBY is doing exactly what it was designed to do, which is aggregate values across groups. The limitation isn't a flaw in the function; it's a mismatch between the function's logic and the shape of your data. And that's a useful thing to recognize, because it shifts the conversation away from "how do I force this" and toward "how do I model my data so the result I want is the natural output." In this case, that means using something like XLOOKUP or FILTER to find the last average per date, or restructuring your source data so the end-of-day average is a column you can simply reference.
What we'd encourage you to take from this is not a specific formula, though that's the immediate need. It's the habit of pausing when a function doesn't cooperate and asking whether the problem is the tool or the approach. The user who asked this is already ahead because they articulated the issue clearly and recognized the gap between what they had and what they needed. That clarity is half the battle. The other half is remembering that spreadsheets reward flexibility, and the moment you feel boxed in by a function's default behavior is often the moment to step back and reimagine the data structure itself. The next time you hit a wall like this, try asking what the last value represents in your data's story, and build your formula around that meaning rather than forcing a generic aggregation to fit.