Scoring error's with multiple data ranges problem. need help!

[wxmeddler]

I trying to replicate a national weather forecasting competition verification for precipitation. The point is that a forecaster will enter a guess at the next days precipitation for a given location and then points are given to how close they get (lower score is better, like golf). I have read up on IF and AND statements and it have helped when doing an individual verification range but the problem is getting multiple ranges. On top of that I need to get all of this in one cell. Can someone point me in the right direction?


Here are the rules:


o .4 points for each .01 of error in the verification range from 0.00 - .10 inclusive

o .3 points for each .01 of error in the verification range from 0.11 to 0.25 inclusive

o .2 points for each .01 of error in the verification range from 0.26 to 0.50 inclusive

o .1 points for each .01 of error in the verification range over 0.50

o This is NOT an absolute error range. The score depends where you are on the scale.


Some Examples:


* If a forecaster gave a forecast of .17 and the verification was .30, the score would be (25 -17)*.3 + (30 - 25)*.2 = 3.4

* If a forecaster gave a forecast of .17 and the verification was .65, the score would be (25 -17)*.3 + (50 - 25)*.2 + (65-50)*.1 = 8.9

* If a forecaster gave a forecast of .17 and the verification was .02, the score would be (17 - 10)*.3 + (10 - 2)*.4 =5.3

* If a forecaster gave a forecast of .90 and the verification was .70, the score would be (90 - 70)*.1 = 2.0

* If a forecaster gave a forecast of .90 and the verification was .45, the score would be (90 - 50)*.1 + (50 - 45)*.2 = 5.0

* If a forecaster gave a forecast of .90 and the verification was 0, the score would be (90 - 50)*.1 + (50 - 25)*.2 + (25 -10)*.3 + (10 - 0) *.4 = 17.5

* If a forecaster gave a forecast of 1.07 and the verification was .80, the score would be (107 - 80)*.1 = 2.7

 

[dan_l]

I'm not clear on what you mean by 'multiple verification ranges'? Ranges ---at least in excel terms --- typically refers to a series of cells.

 

[asa]

Hi wxmeddler (are you meddling with the weather?? :D ),


I'm going to make some guesses as to your terminology...

verification: the actual amount of precipitation measured

verification range: bracketed margin of error of the forecast


One basic solution, given that you understand the calculation, would be to write your formula to account for one range correctly, then add the next range to the end of your formula with the appropriate modifications until you have all the ranges.


Another would be to question the statement "I need to get all of this in one cell". Why?


If it simplifies your formula or understanding it, you could have a separate column for each verification range, and another column that takes their sum, but hide the individual ranges' columns if so desired.


Yet a more elegant solution might be to have a separate table with the rules for your calculation, and generalize your formula to work for any range but get the specific points awarded and min/max for the ranges by referring to this separate table of data.


Try to break your calculation in to smaller steps and get one element working at a time. Rather than write the formula or formulas for you, I'd rather help with your specific stumbling blocks.


Asa