How Can I Perform a weighted average of 2 columns?

Question

I need to set up a number of mini-project sites that have "Task", "Effort (md)" and "%complete" columns. Below these listings I need a weighted total % complete. This is done by multiplying effort x %complete and summing this product over all tasks, then dividing this sum by the sum of all efforts to get "effective % complete" for total mini-project.

This allows a PM to put the detailed task entry and progress reporting in the hands of those who will be doing the work, while obtaining a higher level number that supports project reporting and financials (e.g. earned value estimates).

Can this calculation be done by SharePoint? If so, how?

Answer 1

You can add a calculated column to your list for [Effort] * [Percent Complete]. Unfortunately, you cannot create a view and select that calculated column for a Sum under Total. You can, however, create a Data View Web Part using SharePoint Designer and add the sum that way. Here is a post on how to Get the sum of a calculated column.