CODEWITHSUNDEEP
excelexcel-formula
Dragonfly
Dragonfly

Reputation: 217

How to create a formula for a matrix in Excel

Imagine a team of 3 people, a head, a senior development and a developer. Imagine there are only 2 projects which they work on. Each person splits his time between the 2 projects. For example in the Excel table below, the "Head" is spending 80% of his time on Project A and 20% of his time on Project B.

Currently I apply a weighting to a project using the formula (Total % spent on Project / No of people), therefore for the below example the weight on Project A is 80+30+10 = 120 / 3 = 40. For Project B the weight is 63. This is a straightforward formula =sum(B3:B99) / countA(A3:A99) ... I sum and count up to row 99 as roles can be added and removed at any point and this leaves the formula generic.

Role Project A Project B
Weight 40 63
Head 80 20
SDev 30 70
Dev 10 90

Now the problem with the above is that in reality the roles have a weighting too, which means that the 100% of a Head role has more weight than the 100% of a Dev role. I need to reflect this in the formula somehow. Therefore let's assume the following role weights: Head = 3, SDev = 2, Dev = 1. How could I work this out in a formula? The formula would need to be generic enough that I could add and remove people from the Excel sheet at any time.

Upvotes: 1

Views: 44

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

Use SUMPRODUCT:

=SUMPRODUCT(B3:B99,$D$3:$D$99)/ COUNTA(A3:A99)

enter image description here

Upvotes: 1

Related Questions