How do you calculate a two factor weighted average?

Question

I have a spreadsheet with 3 columns. A contains an importance factor, a number from 1 to 5. B contains a percentage, the amount of the completed task. C contains the number of days it is taking to do/finish the task.

taskA 3|67%|30
taskB 1|98%|70

I want a total completed percentage for the project, where the project is made of those two tasks.

I can do it for 1 factor. For instance, with days as the weight:
=B1*C1/($C$1+$C$2)+B2*C2/($C$1+$C$2)
or 89% complete
But how do I factor the fact that taskB is less important (it's value is 1)? Thank you!

Answer 1

Hmm. It seems as if you have two choices:

a) Replace C everywhere in your formula by A
b) Replace C everywhere in your formula by the product of C and A.

Which makes more sense is a business call rather than a purely mathematical one.

In general, this is something of a business question. If my answer isn't good enough for you, you might want to re-ask in another section accordingly.

Also -- it may be easier to normalize the sum of the weights to 1. I.e., divide each of the weights by the overall sum of the weights, so that you have new weights that add up to one. The formulae wind up being simpler that way.