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))
Updating Average of most recent cells?
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").
Tags:
Best Answer
-
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))
Answers
-
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))
-
Perfect, thank you!
-
@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.
-
@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.
-
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))))
-
You both are super helpful, thank you! I'm still new to learning all this so I really appreciate it!
-
@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(…………………………………………………….)))
-
@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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 434 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!
Trending in Formulas and Functions
- New program proposals are submitted via a form and collected on Sheet 1. When approved by a team leader, the row is copied to Sheet 2. I want to capture the date the copied occurred, which I thought w…
- I need to update a value in a new column. How would I look at a date column that shows dates between 01/01/2025 to 03/31/2025 and place the value of Q1_2025 into another cell?
- Hi, I'm looking to write a formula using references to a rate sheet (screenshot attached with sensitive information redacted). We have several types of rates here, and also ones that are per word, whi…