Calculating accuracy as a Percentage

[Kip Simpson]

I am trying to represent as a percentage the similarity between one column of data to another.

One column represents the forecasted volume of water collected, the second column represents the actualized volume of water collected.

I would like to be able to write an expression that calculates the accuracy of the forecasting. At the moment i am achieving this with simple devision;Sum([forecasted amount]) / Sum([actualized amount]) The trouble here is that if the forecasted amount exceeds that of the actualized amount, the "accuracy" exceeds 100%.

I want to be able to limit the percentage so that it does not exceed 100%, and in fact will begin to go down again as the forecasted amount exceeds that of the actualized amount.

Hopefully this makes sense. Thank you to anyone who takes the time to help.

[Fredrik Rosell]

Hello,

 

Without claiming that this is the RIGHT way for you to look at your data, based on your description it sounds like you might want to explore usingexpressions that look something like this:

 

Accuracy per forecast

1 - (Abs([forecasted amount] - [actualized amount]) / ([forecasted amount] + [actualized amount]))

 

Accuracy for the entire column

Sum(1 - (Abs([forecasted amount] - [actualized amount]) / ([forecasted amount] + [actualized amount]))) / Count()

 

Example:

[Kip Simpson]

Helpful and very comprehensive. Thank you!