Help with Average Formulas
Hi! I am trying to calculate a 3 months, 6 months and 1 year averages of my team utilization percentage. Each team member is a row and then each month is a column. The utilization percentages are coming from other sheets.
Thank you in advance!
Best Answer
-
Hi,
Would something like this work:
=AVG([08.2024]@row:[June-24]@row)
This will give the average of the columns 08.2024, 07.2024, and June-24 in your sheet. You can follow this format to calculate the 6 month and 12 month averages as well.
If you right click a cell in your 3 month average column, then click Convert to Column Formula you can apply the formula to the entire column as well.
The only downside to this approach is that you will need to update the formula every month when you add new data.
SSFeatures
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds more features into SmartSheet.
- Report PDF generator that supports grouped and summarized reports
- Automatic sorting, sorting with filters, saving sort settings
- Hiding and unhiding columns, and spell checking
Answers
-
Hi,
Would something like this work:
=AVG([08.2024]@row:[June-24]@row)
This will give the average of the columns 08.2024, 07.2024, and June-24 in your sheet. You can follow this format to calculate the 6 month and 12 month averages as well.
If you right click a cell in your 3 month average column, then click Convert to Column Formula you can apply the formula to the entire column as well.
The only downside to this approach is that you will need to update the formula every month when you add new data.
SSFeatures
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds more features into SmartSheet.
- Report PDF generator that supports grouped and summarized reports
- Automatic sorting, sorting with filters, saving sort settings
- Hiding and unhiding columns, and spell checking
-
OMG! it worked! thank you so much!!
-
You're welcome! I'm glad that it worked!
SSFeatures
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds more features into SmartSheet.
- Report PDF generator that supports grouped and summarized reports
- Automatic sorting, sorting with filters, saving sort settings
- Hiding and unhiding columns, and spell checking
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
