# Calculating Projection Accuracy

#### 2blues

##### New Member
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

##### New Member
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

##### Well-known Member
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

##### New Member
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?

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

##### Well-known Member
Perhaps something like this:

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">1</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">2</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">3</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Actual</td><td style="text-align: center;;">44</td><td style="text-align: center;;">47</td><td style="text-align: center;;">49</td><td style="text-align: center;;">40</td><td style="font-weight: bold;text-align: center;;">Differences</td><td style="font-weight: bold;text-align: center;;">Rank</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;;">Person 1</td><td style="text-align: center;;">56</td><td style="text-align: center;;">44</td><td style="text-align: center;;">25</td><td style="text-align: center;;">24</td><td style="text-align: center;;">55</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;;">Person 2</td><td style="text-align: center;;">20</td><td style="text-align: center;;">44</td><td style="text-align: center;;">28</td><td style="text-align: center;;">40</td><td style="text-align: center;;">48</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;;">Person 3</td><td style="text-align: center;;">57</td><td style="text-align: center;;">46</td><td style="text-align: center;;">50</td><td style="text-align: center;;">35</td><td style="text-align: center;;">20</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;;">Person 4</td><td style="text-align: center;;">42</td><td style="text-align: center;;">40</td><td style="text-align: center;;">21</td><td style="text-align: center;;">21</td><td style="text-align: center;;">56</td><td style="text-align: center;;">4</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120"></p><br /><br />

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">1</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Actual</td><td style="font-weight: bold;text-align: center;;">50</td><td style="font-weight: bold;text-align: center;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;;">Person 1</td><td style="text-align: center;;">50</td><td style="text-align: center;;">490</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;;">Person 2</td><td style="text-align: center;;">45</td><td style="text-align: center;;">500</td></tr></tbody></table><p style="width:0.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120"></p><br /><br />

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.

Last edited:

#### offthelip

##### Well-known Member
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.