Create a formula to give me an average after reviewing several columns

Lucinda Bugbee

I am trying to write a complex formula for the following snippet.

I want to find the average of the % allocation if the Assigned To is Blake Steinke and the Status is not Complete. I keep getting an "Incorrect Argument Set". This is also a filtered view.

I can do this with 3 helper columns and smaller formulas:

Test Column 1. =SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], "Blake Steinke", [Status]:[Status], <>"Complete")

Test Column 2. =COUNTIFS([Assigned To]:[Assigned To], "Blake Steinke", Status:Status, <>"Complete")

Test Column 3. =[Test 1]@row / [Test 2]@row

However, I do not want to do this. Ultimately, I would like to have this formula in a sheet summary. I will have about 25 people and want to put on a dashboard.

Thanks,

JR90

@Lucinda Bugbee try this formula out:

=AVG(COLLECT([% allocation]:[% allocation], [assigned to]:[assigned to], "Blake Steinke", Status:Status, <>"Complete"))

Please let me know if it works.

JR90

@Lucinda Bugbee try this formula out:

=AVG(COLLECT([% allocation]:[% allocation], [assigned to]:[assigned to], "Blake Steinke", Status:Status, <>"Complete"))

Please let me know if it works.

Lucinda Bugbee

Yes, this worked! You are a rock star! Thank you.