Accuracy Prediction Formula

[robt020]

I am attempting to creating an accuracy formula for predictions of natural gas storage levels by industry analysts.

As you can see on the attached spreadsheet, I have the report dates across the columns and the analysts on the rows. I am trying to compare the analyst estimates to the reported inventory level ("Reported").

My thought was to use the ABS function to compare the estimate to the reported: =ABS(Estimate - Reported)/Reported and then sum those values, divide by the total number of estimates, and then subtract from 1 to get an overall accuracy percentage.

My problem is that each analyst doesn't make an estimate each week. So therefore, the number of estimates is different for each analyst and there are blanks in some cells for reporting dates.

I want to be able to easily create a formula that adds up (i.e., =((ABS(D3-D2))/D2)+((ABS(E3-E2))/E2)+((ABS(F3-F2))/F2)+((ABS(G3-G2))/G2) ), but doesn't require me to remove that function for dates that don't have estimates. Ideally, I would like to be able to plug in the estimates and reported value each week and have the accuracy column update accordingly.

This might not make sense, but hopefully you will see what I mean when looking at the attached spreadsheet. Thank you in advance!

[MrShorty]

If I understand, it seems easy enough, so I wonder if there is more to your question than I am understanding. My approach would look something like:
1) Enter =IF(isblank(d4),"none",ABS(D4-d$3)/D$3) into a suitable cell (I used D38). Note the use of relative and absolute references to make copying easy. Also note the text string output when the corresponding cell in the main table is blank. This is because I know that the build in average function ignores text.
2) Copy the formula in D4 across a range corresponding to the size of the input range. I used D38:AE69.
3) Enter the average formula =AVERAGE(desired row). In mine, I used =AVERAGE(D38:AE38) in B38. copy this formula down.

The main complication that I might be missing is our inescapable desire to compress multi-step calculations into single cell mega-formulas and/or array formulas. Since I am not fond of that level of nesting, I will leave that exercise to you (or some other soul who wants to tackle it). Unless I am missing something, that should work quickly and easily.