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}, .................)))
-
Awesome! Let me give that a try. I appreciate the quick response!
-
Happy to help. 👍️
-
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?
-
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?
-
=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?
-
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.
-
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
-
#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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!