I have a large spreadsheet, generated by a colleague, that contains the results of $E$ experiments. For each experiment, he calculated the average of $M$ measurements.
I need the calculate the average and variance of all measurements, but due to the complex structure of the spreadsheet, I cannot make the calculation directly in the spreadsheet, nor copy the data to another spreadsheet. How can use the current data, i.e., the averages per experiment and the number of measurements per experiment, to calculate the average and variance of all measurements?
Here is what I did so far. Let $x_{ij}$ be measurement $j$ in experiment $i$.
Let $a_i$ be the average of experiment $i$:
$$a_i = \frac{1}{M} \sum_{j=1}^{M}{ x_{ij} } $$
Let $A$ be the total average:
$$A = \frac{1}{EM} \sum_{i=1}^{E}{ \sum_{j=1}^{M}{ x_{ij} } } = \frac{1}{E} \sum_{i=1}^E {a_i}$$
So, I CAN calculate the total average by averaging the averages per experiment $a_i$.
However, this trick doesn't seem to work for the variance. I need to calculate $V$:
$$V = \frac{1}{EM} \sum_{i=1}^{E}{ \sum_{j=1}^{M}{ (x_{ij} - A)^2 } }$$
But if I calculate the variance of the $a_i$'s, I get:
$$V' = \frac{1}{E} \sum_{i=1}^{E}{ (a_{i} - A)^2 }$$
These don't seem similar:
$$V - V' = \frac{1}{EM} \sum_{i=1}^{E}{ \sum_{j=1}^{M}{ [(x_{ij} - A)^2 - (a_{i} - A)^2] } }$$ $$ = \frac{1}{EM} \sum_{i=1}^{E}{ \sum_{j=1}^{M}{ [(x_{ij} + a_{i} - 2A)\cdot(x_{ij}-a_{i})] } } $$
Can I use the existing data to get, at least, an approximation to $V$?