Multiple Sheets for Average

Brianna Kempfer

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!

Paul Newcome

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}, .................)))

Brianna Kempfer

Awesome! Let me give that a try. I appreciate the quick response!

Paul Newcome

Happy to help. 👍️

Brianna Kempfer

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?

Paul Newcome

I'm not sure I follow. Is the formula returning a blank if there are no numbers on sheet 2?

Brianna Kempfer

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

Paul Newcome

Can you provide the exact formula?

Brianna Kempfer

=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")

Paul Newcome

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?

Brianna Kempfer

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")

Paul Newcome

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.

Brianna Kempfer

I tried that. Still gives me N/A, even though there are values in the first sheet.

Paul Newcome

Try this...

=SUM(AVG(COLLECT({Sheet 1}, ................)), AVG(COLLECT({Sheet 2}, ...............) / 2

Brianna Kempfer

#DIVIDE BY ZERO error occurs

Paul Newcome

Sorry. I got ahead of myself there.

=SUM(IFERROR(AVG(COLLECT({Sheet 1}, ................)), 0), IFERROR(AVG(COLLECT({Sheet 2}, ...............)), 0)) / 2