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))
-
@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. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!