Attempting to do an AVG using two identical sheets
I recently had to move from one sheet to two for one of my project sheets. Both project sheets are identical in setup BUT one sheet is the last three years (2019 forward) and the other sheet contains data from beyond three years (2018 and later).
I've created a separate rollup sheet that I have been using but I can't seem to get my AVG formulas to work when I cross-sheet reference both sheets in the formula. I use this data in charts so I really need it to be all on one sheet (for all years).
Rollup sheet columns:
Year (text/number column)
AVG Duration (days) - Year ***this is where the formula I'm trying to get to work will go
_________________________
The two source sheet columns:
Sheet 1 Duration (days) (text/number column)
Sheet 1 Snowflake (checkbox column)
Sheet 1 Go-Live Date (date column)
___________________________
Sheet 2 Duration (days) (text/number column)
Sheet 2 Snowflake (checkbox column)
Sheet 2 Go-Live Date (date column)
___________________________
This formula worked before I had two sheets:
=IFERROR(AVG(COLLECT({Sheet 1 Duration (days)}, {Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row)), 0)
I attempted to just add Sheet 2 but that isn't working....
=IFERROR(AVG(COLLECT({Sheet 1Duration (days)}, {Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row)), 0), {Sheet 2 Duration (days)}, {Sheet 2 Snowflake}, 0, {Sheet 2 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row)), 0)
I really want to make the AVG Duration (days) column in my Rollup sheet a column formula column (so I don't have to remember to update when I move sheets from Sheet 1 to Sheet 2.
Any help would be greatly appreciated.
Answers
-
Try soemthing more along the lines of...
=(SUMIFS(sheet 1) + SUMIFS(Sheet 2)) / (COUNTIFS(Sheet 1) + COUNTIFS(Sheet 2))
=(SUMIFS({Sheet 1 Duration (days)}, {Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row)) + SUMIFS({Sheet 2 Duration (days)}, {Sheet 2 Snowflake}, 0, {Sheet 2 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row))) / (COUNTIFS({Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row) + COUNTIFS({Sheet 2 Snowflake}, 0, {Sheet 2 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome - I tried your suggestion and I get a column formula syntax message - I'm really unclear why - I am not using any of the unsupported reference types. Suggestions?
-
@Paul Newcome - I got it to work - I had to change the column from a column formula to a cell formula; input the formula and then convert it back to a column formula. Final formula looks like this:
=(SUMIFS({Sheet 1 Duration (days)}, {Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row) + (SUMIFS({Sheet 2 Duration (days)}, {Sheet 2 Snowflake}, 0, {Sheet 2 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row))) / (COUNTIFS({Sheet 1 Snowflake}, 0, {Sheet 1 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row) + (COUNTIFS({Sheet 2 Snowflake}, 0, {Sheet 2 Go-Live Date}, IFERROR(YEAR(@cell), 0) = Year@row)))
Thank you for the help! Greatly Appreciated!
-
Glad you were able to get it working. Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 209 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 297 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!