CODEWITHSUNDEEP
excel
Uros Djukic
Uros Djukic

Reputation: 3

Excel Multiply one set of values with given matrix and sum results

I need to redistribute values for Old entries by using new distribution in a given table.

Example: OLD Elements with values

Need to redistribute using given % in this table:

NEW Distribution of values

So New Value of Element 1 = 99% * old1 + 7% * old2 + 3% * old3 + 26% * old5

This is not whole table, it is pretty large. There must be a simpler way than adding things up manually.

Upvotes: 0

Views: 25

Answers (1)

Dominique
Dominique

Reputation: 17491

You can use the MMULT() worksheet function for that.

Example: 

               A      B       C       D        E        F
1         Amount                              
2            100
3            200
4            500
5            400
6                                  
7    Percentages      1       2       3       4      
8                    99       7       3      26      =MMULT(B8:E8;A$2:A$5)/100
9                     1      93      34       0      =MMULT(B9:E9;A$2:A$5)/100
10                    0       0      63      74      =MMULT(B10:E10;A$2:A$5)/100

For your information: I've entered the first formula in F8, and dragged and dropped until F10.

Upvotes: 1

Related Questions