Calculating accuracy within excel

Question

In MS Excel, I have three cells: a Prediction cell (A1), Results cell (B1), and Difference cell (C1). I have a cell called Accuracy (D1) where I would like to display the accuracy of the prediction made in light of the outcome.

As an illustration, if I forecast 24 and get 24, the accuracy should be 100% with a difference of 0. The accuracy should be 50% with a differential of -12 if I have a prediction of 24 and an outcome of 12. The accuracy should be 50% with a differential of 12 if I have a prediction of 24 and an outcome of 48.

Here is the calculation I'm using in the Accuracy cell (D1):

=(((C1+100)*A1)/B1)/(C1+100)

This calculation is only showing the expected results when the Prediction cell is higher than or equal to the Results cell.

Answer 1

Format the cell in Col C as "%" and then try this formula:

The formula in C1 as per the snapshot:

=IF(B1<A1,VALUE("-"&(B1/A1)),(B1/A1))

The formula in D1 as per the snapshot:

=B1-A1

Various test scenarios are below.