Updating Average of most recent cells?

Josh S.

Hello! Quick question I've been stumped on. I have a sheet that tracks hours worked on different projects each month in a year. I want there to be a cell that displays the average of the most recent 3 months. Of course, I'd want this to update when a new month is entered. That value would be used along with the "hours allocated" value to determine the percent of the allocated hours worked.

In case context helps: we want to have each employee fill out this sheet with each of their projects. The manager would have a "master report" of sorts that displays the last two columns for each project ("Three Month Average Hours Worked" and "Percent Allocated Hours Worked").

Paul Newcome

You would use something along the lines of

=AVG(INDEX(January@row:December@row, 1, COUNTIFS(January@row:December@row, @cell <> "")), INDEX(January@row:December@row, 1, COUNTIFS(January@row:December@row, @cell <> "") - 1), INDEX(January@row:December@row, 1, COUNTIFS(January@row:December@row, @cell <> "") - 2))

Paul Newcome

You would use something along the lines of

=AVG(INDEX(January@row:December@row, 1, COUNTIFS(January@row:December@row, @cell <> "")), INDEX(January@row:December@row, 1, COUNTIFS(January@row:December@row, @cell <> "") - 1), INDEX(January@row:December@row, 1, COUNTIFS(January@row:December@row, @cell <> "") - 2))

Josh S.

https://community.smartsheet.com/discussion/comment/453944#Comment_453944

Perfect, thank you!

Brian_Richardson

@Paul Newcome just wanted to say that's so clever! I need to save this one for future use. I was thinking of some awful set of IF statements checking each month in turn.

Paul Newcome

@Josh S. Happy to help. 👍️

@Brian_Richardson This will only work if they are filled out in order though and may need some adjustments for the first couple of months (when there aren't three filled out yet). If there could be blanks in between, we would have to use the COLLECT function. For the early part of the year, maybe an IFERROR somewhere. Not around each INDEX though to uo0tput a zero because then that would skew things. Maybe instead a nested IF for the first two months and then the AVG for months 3 though 12. Haven't had a chance yet to get that part completely figured out.

Brian_Richardson

Yeah I was thinking the same- I had a similar situation with a budget sheet here and things broke when columns started moving around.

I think collect would be tough. Frankly it’s probably back to nested IF if columns are going to move as you need to lock in the column names at that point.

@Josh S the moral of the story is- don’t let these monthly columns get rearranged or separated.

Dealing with the first three months adjust formula like:

=IF(March@row=“”,AVG(January@row:February@row), IF(February@row=“”,January@row,AVG(INDEX(January@row:December@row, 1, COUNTIFS(January@row:December@row, @cell <> "")), INDEX(January@row:December@row, 1, COUNTIFS(January@row:December@row, @cell <> "") - 1), INDEX(January@row:December@row, 1, COUNTIFS(January@row:December@row, @cell <> "") - 2))))

Josh S.

You both are super helpful, thank you! I'm still new to learning all this so I really appreciate it!

Paul Newcome

@Brian_Richardson The only adjustments I would suggest is that those look like "Smart Quotes" to me unless they are just in italics. Not sure how familiar you are with those, but they will break a formula. Then I would switch my IFs around so that evaluating for February comes first. If Feb is blank, the "If March is blank" argument would cover that, and you'd still end up averaging in a blank for Feb.

My approach would have been…

=IF(COUNTIFS(January@row:December@row, @cell <> "") < 3, AVG(COLLECT(January@row:February@row, January@row:February@row, @cell <> "")), AVG(INDEX(…………………………………………………….)))

Paul Newcome

@Josh S. No worries at all. This is a great place to learn.

@Brian_Richardson Are you going to be in Seattle for ENGAGE this year? If so, I might have to pick your brain on some Bridge challenges. I recently caught the Bridge bug, and have been living in there the past month and a half. Haha.

Brian_Richardson

Absolutely @Paul Newcome- in fact I’m speaking on advanced Bridge using Call API and JavaScript. Find “The Future of Workflows” if you want to come. But otherwise I’d love to meet in person outside of that session! Maybe we can grab lunch? Or I’ll see you at Allison’s Overachiever/Community shindig?

Brian.richardson@ironmountain.com if you’d like to make arrangement.