weighing avg by columns vs rows outputs different results

First of all, I just know the basics of math, so please be patient.

I have an overall score for a company with the product1 and product2. However when I do the overall for each criteria like A, B, C, D, E, the sum of these criteria is not equal to the overall score.

Example A

Data

$\begin{array}{c|ccccc} \;&A&B&C&D&E\\ \hline \text{product1}&1&2&3&4&5\\ \text{product2}&2&2&3&3&3 \end{array}$

Weighing

$\begin{array}{c|ccccc} \;&AA&BB&CC&DD&EE\\ \hline \text{product1}&2&3&2&3&5\\ \text{product2}&2&4&3&3&4 \end{array}$

Formula for row $i$: $(A_i \times AA_i + B_i \times BB_i + \dots ) /sum(AA_i:EE_i)$

product1: $\dfrac{2+6+6+12+25}{15}=3.4$

product2: $\dfrac{4+8+9+9+12}{16}=2.625$

Total AVG: $\dfrac{3.4+2.625}{2}=3.0125$

If I try to find what is the weighing avg for each column, the total is different. Both examples should output the same weighing avg? If not, why? What is the most adequate way to represent the avg for this type of need? the first example or the second?

Example B

Formula for column $J$: $(J_1 \times JJ_1 + J_2 \times JJ_2)/sum(JJ_1:JJ_2)$

$\begin{array}{c|ccccc} \;&A&B&C&D&E\\ \hline \text{average}&\dfrac{2+4}{6}=1.5 &\dfrac{6+8}{7}=2 &\dfrac{6+9}{5}=3 &\dfrac{12+9}{6}=3.5 &\dfrac{25+12}{9}=4.111 \end{array}$

Total AVG: $\dfrac{1.5+2+3+3.5+4.111}{5}=2.822$

Your $2.8$ is not correct. If you do the weighted average of $1,5\ \ 2 \ \ 3 \ \ 3,5\ \ 4$ with the weights you are using you get $3.0333333$

• sorry, but i don't understand what you mean. I am using the weights as you can see in the second formula. (A1 * AA1 + A2 * AA2) / sum(AA1:AA2) – Fel Jul 13 '15 at 0:13
• Those give you the third line nicely. Then when you weight the third line by the five weights $2,2,3,3,3$ you get $3.0333333$ – Ross Millikan Jul 13 '15 at 0:27
• sorry, but still not understanding. I would appreciate an example of what you are saying. I don't have five weights. I have five weights per product. – Fel Jul 13 '15 at 0:37
• I just did $\frac {2 \cdot 1.5 + 2\cdot 2 +3 \cdot 3 + e \cdot 3.5 + 3 \cdot 4}{2+2+3+3+3}=3.033333$ That is the same weighting you were doing before. – Ross Millikan Jul 13 '15 at 0:49
• 2,2,3,3,3 are the values for the product2. for product1 we have 1,2,3,4,5, so, i don't get what you are saying. for a bounty i am expecting a more complete information/example than this. thanks anyway. – Fel Jul 13 '15 at 11:25

The two methods are fundamentally different, and so in general will produce different results.

The row average of row $i$ is:

$$\dfrac{\sum_{j=1}^{N_c} {e_{ij}\times w_{ij}}}{\sum_{j=1}^{N_c} w_{ij}}$$

where $N_c$ is the number of columns, $e_{ij}$ is the entry in the $j$-th column of the $i$-th row in the data table, and $w_{ij}$ is the entry in the $j$-th column of the $i$-th row in the weighings table.

The column average of column $j$ is:

$$\dfrac{\sum_{i=1}^{N_r} {e_{ij}\times w_{ij}}}{\sum_{i=1}^{N_r} w_{ij}}$$

where $N_r$ is the number of rows.

To see why the two are not equal, consider simpler tables:

DATA

$$\begin{array} {c|cc} \;&A&B\\ \hline prod1&a&b\\ prod2&c&d \end{array}$$

WEIGHINGS

$$\begin{array} {c|cc} \;&A&B\\ \hline prod1&e&f\\ prod2&g&h \end{array}$$

The row average is $\left(\dfrac{ae+bf}{e+f}+\dfrac{cg+dh}{g+h}\right)/2$.

The column average is $\left(\dfrac{ae+cg}{e+g}+\dfrac{bf+dh}{f+h}\right)/2$.

You need some maths to prove that these are rarely equal, but they are indeed rarely equal.

As to your question as to which to use, they have different meanings. The row average tests a product against a set of criteria. The column average tests a criteria against a set of products. So they both have a purpose.