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!

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Looking for assistance with a formula
Hi all, I am new to formulas and functions in Smartsheet and have a large sheet that I would like to summarize using an "average" formula/function. I need to include at least 2 separate criteria to filter correctly and I am having trouble combining the avg and collect functions as suggested in the functions list. I have…
Need help in formula function
I'm not able to get this formula to function in Smartsheet. The material ordered date is 9/3/2025. Am I missing something to get the date rage to populate? =TEXT([Material Ordered Date]@row +28,"MM/DD/YYYY")&"-"&TEXT([Material Ordered Date]@row +42, "MM/DD/YYYY")
I am trying to find a value in a table based on 2 criteria.
I am trying to find a value in a table based on 2 criteria. One of the values is dynamic from a drop down menu, while the other is static value in a column. The first value is found in column "Building type" and the other is found in Resource type. The table I am referencing is also shown. The result would be shown in the…