# Fix how Excel handles arrays it produces internally while evaluating a formula so error results are not yielded.

Many times I write a formula for a cell and though it seems perfect in its formation, it yields an error value, no matter what I try. I have to find an alternative way rather than my preferred way, regardless of which is better.

Using the formula evaluator sometimes shows where the error stems from, but often it does not. Even when it shows where, the why is often not really apparent.

Using the technique of highlighting portions of the formula and pressing F9 to show its result is often useful. A perhaps biased estimate is that 90% of the time the problem seems to stem from the output of a buried function (call those “interior” functions) is not what one might expect, but actually an array.

For instance, the following formula, =SUM(ABS(A1:A4)), gives this if one highlights “A1:A4” and presses F9: =SUM(ABS({7;15;6;3}). From this point, if one keeps using this approach, working outward until finalized, one gets “31” as a result. However, just typing it in and pressing Enter yields “#VALUE!” instead.

One can type it as =SUM(ABS({7;15;6;3})) (hand typing the array rather than having it be Excel’s output to the ABS() or SUM() functions when evaluating “A1:A4”) and press Enter and it works. One can make it an array formula in its entirety with Ctrl-Shift-Enter and it works. But typed as first shown above and pressing Enter always gives the Value error.

The suggestion is that the array produced as Excel evaluates the formula is not literally what is seen visually with F9, but rather has something extra, or less, or perhaps it is identical to the typed version but the next function outward “consumes” it differently if it is output of Excel’s evaluation than it does if it is simple text presented to it. Or… well, you see… and unless MS hires us to program Excel, the “why” isn’t really important.

The “what” is. How does one solve this? Two basic ways suggest themselves:

- MS could fix this (if something slightly different than the visual is being presented outward, or the function it’s being passed to could accept it identically to hand typed text, or… well, or whatever is necessary) and open our worlds.
- Excel could present a new function that we can wrap such occurrences in like one might use TRIM() or ROUND() and such arrays produced by its evaluations would be passed out of it in every way identical to a hand typed array.

I would prefer the first, but the second would be as workable as many other things we must do in Excel. But I’d like the situation fixed regardless. I might point out that it seems to me that if this is a result of some “unknown and unapparent to me but greatly or mildly advantageous to the world of Excel” I really don’t care unless someone can explain how and why that is and why it is more worthwhile than fixing it. I just want it fixed…

And to reiterate, this is meant to solve the internal handling of the arrays Excel produces in evaluating formulas, not to try to use functions designed as, or not as, array formulas in the opposite way. Simply to make interior function output that comes in array form available identically to hand typed versions of the same output.

**2**votes