weighing avg by columns vs rows outputs different results

Question

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$

Answer 1

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$

Answer 2

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.