2

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!

henrythedj
  • 78
  • 1
  • 2
  • 12
  • I don't know which answer to pick, both work! I suppose I'll give it to Tom for the use of non-CSE (array) formula. – henrythedj Apr 08 '16 at 13:57

2 Answers2

5

Try the following array formula:

=AVERAGE(IF(A2:B5<>"",ABS(A2:B5)))

Being an array formula it must be confirmed with Ctrl-Shift-Enter. If done properly Excel will automatically put {} around the formula.

![enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • I find that the CSE works if A1 etc. actually contain ="" but the first one gives #value! error – Tom Sharpe Apr 07 '16 at 21:20
  • @TomSharpe I think that in this case the only answer is the CSE Array formula. At least with my limited knowledge I could not make SUPRODUCT() work. It is the ABS(), it does not like blank cells. – Scott Craner Apr 07 '16 at 22:18
4

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

Community
  • 1
  • 1
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Good job, I tried everything but using the `--` or `+` to turn the `""` into `0`. Though with the `+` I believe the `N()` is not needed. – Scott Craner Apr 09 '16 at 15:13
  • 1
    Thanks! It seemed to need the '+' to make it work as an array and the N() to convert "" into 0 so two separate things as far as I can tell. – Tom Sharpe Apr 10 '16 at 07:19