Excel Array Evaluation and some tricks

Question

I came across some array evaluation tricks on the net. But i couldn't understand the theory. I think there are many like me who want to know about this subject.

• what is array evaluation and why do we need that?
• how does N( INDEX (.....)) trick work behind the scenes?
• how does N( IF (1,.....)) trick work behind the scenes?
• why do we need to use N function here?

Answer 1

1. Write the formula.
2. Select it.
3. Go to the ribbon and select Formula>Evaluate
4. Click Evaluate and see for yourself