# Multiple Sheets for Average

Options
✭✭✭✭

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!

«1

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Can you provide the exact formula?

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Try this...

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

• ✭✭✭✭
Options

#DIVIDE BY ZERO error occurs

• ✭✭✭✭✭✭
Options

Sorry. I got ahead of myself there.

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!