Formula Query

Tim Möhle

I'm at a loss for how to perform a desired calculation within a sheet.

 

I have a list of rows that are assigned to owners (contacts).  Each row has a % complete.  I want a formula that will tell me what a given contacts overall completion rate is.

* Look up name in Owner column

* Add up all percentages in the "% Complete" column of all lines with that person's name

* Average the % to get a total % complete figure

I've considered some combination of COUNTIF and AVG, but can't figure out the right syntax.  Please help!

Paul Newcome

Try something along the lines of

 

=AVG(COLLECT([% Complete]:[% Complete], Owner:Owner, Name@row)) 

Tim Möhle

Thank you.   Not sure I'm understanding the application of "Name@row".

 

I've got this and still getting an error.

=AVG(COLLECT({%},{Owner}, Name@row))

Attached a sample of actual data columns.

Paul Newcome

Can you provide a screenshot of where you are collecting the data? What error specifically are you getting? Does [Script Writer] have multiple contacts per cell?

Tim Möhle

The % in the source file image I sent -- this is being manually set by the owner.  it's a number field.

 

Error = #UNPARSEABLE

There's one line that has 2 owners.  If this is an issue, I can change that.  All others only one have contact assignments.

 

Thank you.

Paul Newcome

Sorry. I meant where the data is being displayed. Where the averages/formulas will be.