Calculating Projection Accuracy

[2blues]

I'm trying to figure out 2 different sets of projections and whose projections where the most accurate. The data looks like this:

Proj 1 Proj 2 Actual23 25 27
24 29 16
33 37 26
8 16 33

The difference can be both positive (over projected) or negative (under projected). I know how to calculate the difference by simply using A1-C1 (for example), but how can I show who was more accurate overall with all projections made? The goal would be to nail the actual score with a difference of "0".

Thanks,
Matt

 

[2blues]

Sorry data got jumbled in post...

Proj 1 - 23
Proj 2 - 25
Actual - 29

Proj 1 - 33
Proj 2 - 37
Actual - 26

More cleaner looking example

 

[StephenCrump]

The difference can be both positive (over projected) or negative (under projected) .... The goal would be to nail the actual score with a difference of "0".

Depends how do you want to define most accurate?

Suppose the actual result is 10 both times:
Person 1 guesses 11 and 11
Person 2 guesses 5 and 15.

It sounds like you think Person 2 is the most accurate, because they have guessed -5 and +5 --> exactly right on average?

One alternative would be to consider absolute values: Person 1 is wrong by 1+1=2. Person 2 is wrong by 5+5=10?

 

[2blues]

Depends how do you want to define most accurate?

Suppose the actual result is 10 both times:
Person 1 guesses 11 and 11
Person 2 guesses 5 and 15.

It sounds like you think Person 2 is the most accurate, because they have guessed -5 and +5 --> exactly right on average?

One alternative would be to consider absolute values: Person 1 is wrong by 1+1=2. Person 2 is wrong by 5+5=10?

Thanks for the reply Stephen.

Yes alternative one is exactly what I'm struggling with, how to get away from that.

So I would like to figure out how to calculate absolute values like you mentioned in alternative #2 . Who is most accurate by how far off they are from the actual number?

 

[StephenCrump]

Perhaps something like this:

F4: =SUMPRODUCT(ABS(B4:E4-B$2:E$2))
G4: =RANK(F4,F$4:F$7,1)

Book1
	A	B	C	D	E	F	G
1		1	2	3	4
2	Actual	44	47	49	40	Differences	Rank
3
4	Person 1	56	44	25	24	55	3
5	Person 2	20	44	28	40	48	2
6	Person 3	57	46	50	35	20	1
7	Person 4	42	40	21	21	56	4
Sheet1

You may not want to rank this way, i.e. based on simple differences, if the results can vary in size. Consider, for example:

Book1
	A	B	C
1		1	2
2	Actual	50	500
3
4	Person 1	50	490
5	Person 2	45	500
Sheet1

Based on the difference approach above, Person 2 will be ranked the most accurate, even though Person 2 was 10% out on Trial 1, and Person 1 was only 2% out on Trial 2.

 

[offthelip]

I suggest calculating the errors and then calculating the standard deviation of the errors using the excel function : STDEV(), this automatically takes accounts of positive and negative errors for you and is fairly common way of comparing errors in statistics.