Evaluation same, result different?

  • Thread starter Thread starter dindigul
  • Start date Start date
D

dindigul

I selected A22:A27 then input the following:
{=CHAR(97+ROW(A22:A27)-ROW(A22))} CSE
Though I get a,b,c ... e,f in cells A22:A27, but if I evaluate going into
each of the cells in this range, the evaluator returns CHAR(97) always, but
then how come the resultant character changes? Any ideas?
 
Thu said:
I selected A22:A27 then input the following:
{=CHAR(97+ROW(A22:A27)-ROW(A22))} CSE
Though I get a,b,c ... e,f in cells A22:A27, but if I evaluate going into
each of the cells in this range, the evaluator returns CHAR(97) always, but
then how come the resultant character changes? Any ideas?
Click to expand...

The value of row(A22:A27) is 22 if it's not inside an array formula.
 
You need to select a single cell in the array, then just click the evaluate
button until it changes to restart in the evaluation box and it will end
with the letter
 
Would you be more comfortable with something like this *non*-array formula
instead:

=CHAR(97+ROWS($1:1)-1)

Entered *anywhere*, will return the alphabet as it's copied down.
 
My question was that even when the result is char(97) throughout, how come
the characters change from a to b to c ...? I do not want any alternate
solution. I wanted to know about this strange behaviour.
 
It's really *not* strange behavior for certain array formulas.

It might help you to read up on XL's different array performances.

Arrays are rectangular ranges of formulas OR values, that XL treats as a
single group.
Some array formulas return an array of results that can appear in *many
cells*.
Other formulas affect an entire array of cells, yet return the result to a
*single* cell.

As far as your question is concerned:
Say A1 to A5 contain prices.
B1 to B5 contain quantities.
In C1 you enter the formula:

=A1*B1

Copying this formula down to C5 results in 5 *different* formulas, one in
each cell.

Now, select D1 to D5, and enter this formula in D1, the cell in focus:

=A1:A5*B1:B5

And use CSE to enter it.

You see the exact same returns in each column (C & D),
BUT ... as you click in each cell in D1 to D5, you see the *same* formula.

XL is using a *single* formula to compute multiple results, in place of 5
individual formulas.

This is a case where XL is using *less* memory and storage with the array
formula, then is used by the 5 individual formulas.

There are many cases, of course, where array formulas use *more* of XL's
resources then regular formulas.
 
Back
Top