Search code examples
excelexcel-formulaaverageforecastingabsolute-value

Averaging the absolute values of data in excel, ignoring blank (" ") cells

I am building a forecast method comparison tool in excel. I have to use excel for this particular task. I need to compare different types of errors (MAE-mean absolute error, RMSE-root mean squared error, etc) to show which method does the best job.

I have the errors (residuals) organized as follows:

Column 1   Column 2
  -0.5       1.2
  1.5        -1
   ""        ""          #  <==== here is what is causing the issue
   0.2       1.5

The problem is that sometimes the data sets are not fully populated, and I have built in 'if' statements that return "" (as blank a cell as I can get from an 'if' statement) if there is no activity on a certain date. This doesn't cause a problem in calculating the Mean Error or RMSE, or in the forecasting.

I tried the following formulas (using ctrl+shift+enter for array formulas):

=average(abs(DATA-RANGE))
=sum(abs(DATA-RANGE))/count(DATA-RANGE) # I calculated the count in another cell

I believe these would work if it were not for the cells containing "" (according to a solution found in another place)

Any thoughts? The spreadsheet is already large, I would like to do this without creating new columns (ie, making a new column of abs(DATA) to calculate the average of).

I also want to make this without any VBA/macros - it needs to be accessible to folks who don't know anything except simple excel formulas.

Thanks!

EDIT 1: Here I have tried both Scott and Tom's method with my data. Both work! I even tried removing some of the formulas that provide the "" (so that true blank spaces were there), and it did not return #VALUE in either method. The formula in the edit box is what is being used to calculate the errors. Much obliged!

Solution

  • A non-CSE formula that works with empty cells and cells containing ="" is

    =SUMPRODUCT(ABS(N(+A2:B5))*(A2:B5<>""))/COUNT(A2:B5)

    or because the cells which are empty or have quotes in them do not contribute to the sum,

     =SUMPRODUCT(ABS(N(+A2:B5)))/COUNT(A2:B5)

    See this useful answer and also this

    enter image description here