Formula Query

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!

Find more posts tagged with

Contacts

Formulas

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…