Multiple Sheets for Average
I am trying to put together a master metric sheet. Within this sheet, I need to pull the averages from two separate sheets into one. We currently have a flow that when it is completed on one sheet, it will automatically populate to an "archive sheet". Both of these sheets need to be captured in the average calculation.
I am able to get it from the one sheet using AVG and COLLECT, but wasn't sure if there is a way to average another range.
Any guidance would be appreciated!
Comments
-
You would do a similar AVG/COLLECT for the other sheet and then drop both of those into another AVG function.
=AVG(AVG(COLLECT({Sheet 1}, .................)), AVG(COLLECT({Sheet 2}, .................)))
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!
-
Awesome! Let me give that a try. I appreciate the quick response!
-
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!
-
Hey Paul!
So I incorporated this into my sheet and I encountered a problem. It is only pulling the average if i have a value in the second sheet. There are instances where the second sheet wont have any of the numbers until it is pushed over to that sheet, and it will not populate a value if the numbers are only in the first sheet. Any recommendations?
-
I'm not sure I follow. Is the formula returning a blank if there are no numbers on sheet 2?
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!
-
i have it set up to include IFERROR and will N/A when it does not produce a number. When I take off the IFERROR, it gives me #DIVIDEBYZERO
-
Can you provide the exact formula?
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!
-
=IFERROR(AVG(AVG(COLLECT({9 - Master Completed Executed int close}, {5 - Master Issued BOPs Month}, "1", {5 - Master Issued BOPs Year}, "2020"), AVG(COLLECT({9 - Master Completed Executed BOPs Int Close out}, {9 - Master Completed Executed Month}, "1", {9 - Master Completed Executed Year}, "2020")))), "N/A")
-
Ok. And how exactly is the data in each of the ranges being populated? Is it manually or a formula? If it is a formula populating that data, then would you be able to post it/them?
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!
-
Month: =MONTH([Production Date]@row)
Year: =YEAR([Production Date]@row)
Master BOP Close Int.: =[Internal Close Date]@row - [Production Date]@row]
Master completed just takes the number from the Master BOP Close Int. when it moves to that sheet since the formulas dont move with it
I also updated the one reference in the main formula - for some reason the 1st reference was showing 9-, but it should have been 5-.
=IFERROR(AVG(AVG(COLLECT({5-Master Issue BOPs Close int.}, {5 - Master Issued BOPs Month}, "1", {5 - Master Issued BOPs Year}, "2020"), AVG(COLLECT({9 - Master Completed Executed BOPs Int Close out}, {9 - Master Completed Executed Month}, "1", {9 - Master Completed Executed Year}, "2020")))), "N/A")
-
Ok. Try removing the quotes from around the numbers in your AVG/COLLECT. When you put quotes around numbers, you are searching (or generating) text strings whereas no quotes around numbers are numerical values.
Your source data formulas are all producing numerical values.
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!
-
I tried that. Still gives me N/A, even though there are values in the first sheet.
-
Try this...
=SUM(AVG(COLLECT({Sheet 1}, ................)), AVG(COLLECT({Sheet 2}, ...............) / 2
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!
-
#DIVIDE BY ZERO error occurs
-
Sorry. I got ahead of myself there.
=SUM(IFERROR(AVG(COLLECT({Sheet 1}, ................)), 0), IFERROR(AVG(COLLECT({Sheet 2}, ...............)), 0)) / 2
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
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!