Share cost of activities between participants in spreadsheet

Question

Say you have a spreadsheet you use to fairly share the cost of activities between people (example google doc):

       A        B     C     D     E
1    Activity Cost  User1 User2 User3
2    1        100   1     1     1
3    2        50    0     2     0.5

So this means: Everyone shares the cost of activity A equally. For acitvity B User1 doesn't pay, User2 pays 2 and User3 pays 0.5 (i.e. User2 pays 80% and User3 20%)

For every row and user (e.g. User1 and row 2) you can easily calculate everything a user has to pay:

=C1/SUM($C2:$E2)*$B1

But if you want to calculate what every user has to pay you need another matrix with all these formula's and sum them. Can you also calculate what every user has to pay directly in a formula?

Answer 1

It can probably be simplified even more, but my suggestion is to use SUMPRODUCT in this way:

1. Add a "Total Shares" in Column I to total cols E-H.

2. To calculate the cost per person, and skip the intermediate matrix, you can use the formula below in E15 and copy across.

   =SUMPRODUCT(E2:E4,1/$I2:$I4,$C2:$C4)

This will, as the name infers, add up a series of products. The user's # of shares * 1/ Total Shares (i.e. dividing by total shares) * the cost of each event

It will do this for matching positions in each array (i.e. values in the same row) and add them up.

Note: above cell references are to your Google doc, not your above example.