How to calculate this ratio for use in pro-rata forecasting

Question

This is really a very simple question, it's more the understanding I need rather than a simple answer.

If I have two arrays, A with 10 elements (A1, A2, ...), and another, B, with 5 elements (B1, B2, ...) and I want to base the missing 5 elements on the ratio between existing 5 elements how would I go about doing this?

All I want to know is which of the following methods I should use to calculate the ratio:

(B1/A1+B2/A2+...+B5/A5)/5 or (B1+B2+...+B5)/(A1+A2+...+A5)

These obviously produce different numbers so which would be to correct one to use and why? Also would th eother version ever be used and in what situation?

Thanks.

Answer 1

This is ofcourse a non-question. Neither is correct, both are valid answers.

Method two however weights it by larger numbers which in most scenarios is probably not what you want. The relationship between A1 and B1 is mostlikely as important as the relationship between A3 and B3, even if A3 is ten times teh size of A1.

For my work, I need to use method one and average all the divisions, rather than summing top and bottom.

Answer 2

So I have possible solved this question with some analysis...

Jumping to excel In row 1 I have the folling code

A1: =RAND()

B1: =A1*2+RAND()

C1: =B1/A1

D1: =A1*$H$1

E1: =A1*$I$1

F1: =B1-D1

G1: =B1-E1

dragged the formulas down 100 rows with the important code in the following cells

H1: =AVERAGE(C:C)

I1: =SUM(B:B)/SUM(A:A)

Now I figure all I have to do it calculate the average distances (columsn F and G) and whichever average distance is smaller indicates a better averaging calculation.

So the final excel solution:

H2: =AVERAGE(F:F)

I2: =AVERAGE(G:G)

Thanks to the nature of excel and the RAND function I can easily refresh the sheet to refresh over and over and from this I can see the cell in H2 ranges from -3 to +3 while the cell in I2 ranges from E-14 to E-17, i.e. incredibly small.

This indicates to me I should be using this method:

(B1+B2+...+B5)/(A1+A2+...+A5).

I hope someone can confirm my methodology and second the answer I've come to.