=AVG(COLLECT Formula Referencing Another Sheet

Wesley.baquero

Hello!

So I have a master sheet copied into 2 other sheets where people will make updates to in several row sections. I want the master sheet to reflect the averages of what they select in each row section (in percentages) into the master sheet's identical section (sorry if that does not make much sense, not sure how else to explain it). I was thinking into a =AVG(COLLECT formula but everything I try comes out as an error.

I tried the following formula only referencing one of those copied sheets:

=AVG(COLLECT({% Done}, [% Done Calc.]:[%Done Calc.], {Phases}, [Phases]:[Phases]

Carson Penticuff

Can you post a screenshot of your master sheet and one of the "copied" sheets, redacted if necessary?

Wesley.baquero

Master Sheet above

the other two are Transition out 1 sheet and Transition Out 2 (they look identical to this one)

Carson Penticuff

I'm having a little trouble wrapping my head around exactly what you are trying to do, but let's see if I understand correctly.

In your master sheet, you have task 1, task 2, etc. The "copied" sheets are identical, and the % complete for these items will be filled in separately? So, does this mean that, for example, task 2 could be entered as 20% complete in one copied sheet and 40% complete in the other copied sheet?

Am I in the ballpark?

Wesley.baquero

Yes sir! apologies for the confusing explenation.

say Task 1 on copied sheet #1 is 20% and Task 1 on copied sheet #2 says 50%, I would like to have a formula that averages task 1 on both copied sheets into the task 1 on the master sheet.

Carson Penticuff

Okay, I think I get it. I am going to give you two options.

Option 1: This will ignore empty cells. So, if Item1 has 50% entered in your copy 1 sheet but is blank in your copy 2 sheet, the result will be 25%.

=AVG(COLLECT({% Done - Copy 1}, {Item - Copy 1}, [Item]@row), COLLECT({% Done - Copy 2}, {Item - Copy 2}, [Item]@row))

Option 2: This will treat any blank cells as if they are 0%. So 50% in copy 1 and a blank cell in copy 2 will result in 25%

=AVG(IF(COUNTIFS({Item - Copy 1}, [Item]@row, {% Done - Copy 1}, NOT(ISBLANK(@cell))) = 1, COLLECT({% Done - Copy 1}, {Item - Copy 1}, [Item]@row), 0), IF(COUNTIFS({Item - Copy 2}, [Item]@row, {% Done - Copy 2}, NOT(ISBLANK(@cell))) = 1, COLLECT({% Done - Copy 2}, {Item - Copy 2}, [Item]@row), 0))

You will of course have to tailor the column names to your specific sheets and you will have to setup cross sheet references as well.