Calculating Projection Accuracy

2blues

New Member
Joined
Apr 25, 2018
Messages
7
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
Joined
Apr 25, 2018
Messages
7
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
Joined
Sep 18, 2013
Messages
3,568
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
Joined
Apr 25, 2018
Messages
7
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

Well-known Member
Joined
Sep 18, 2013
Messages
3,568
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
Joined
Dec 23, 2017
Messages
1,044
Office Version
2010
Platform
Windows
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.
 

Forum statistics

Threads
1,086,257
Messages
5,388,720
Members
402,137
Latest member
pkulkarni

Some videos you may like

This Week's Hot Topics

Top