Please can you help. I can not remember what the special formula step is.

[Rick]

some time ago a friend made a graph on a sheet for me.

Now he did something that to this day I have been unable to recall or find
any documentation on, I can not even remember what is did in the sheet or
how it worked.

All I know is all the formula did not work until he did this step.

I would imagine this will be easy if you know what it is this if just an
example not the actual code.

The actual code used a number of if statements in it if I remember
correctly.

Now this was the trick

The STD Code would look like this below.
=VLOOKUP(Sheet2!A1,Sheet3!A:E,2,0)


He then pressed two keys wile in the formula and the formula then looked
like this.
Note " at the beginning and the end.
The formula did not work until he pressed the key combination and the
formula was placed inside " ********* ".

"=VLOOKUP(Sheet2!A1,Sheet3!A:E,2,0)"

Can you explain this step and what is was doing.
Where is the documentation to this process in excel I am sure it must have a
name.

Note
Just placing the formula inside " " does not work you have to press this key
combination like ( Eg ctrl,shift,f2 ) this is not the actual key comb,

Thanks for your help.

Rick

 

[Lars-Åke Aspelin]

some time ago a friend made a graph on a sheet for me.

Now he did something that to this day I have been unable to recall or find
any documentation on, I can not even remember what is did in the sheet or
how it worked.

All I know is all the formula did not work until he did this step.

I would imagine this will be easy if you know what it is this if just an
example not the actual code.

The actual code used a number of if statements in it if I remember
correctly.

Now this was the trick

The STD Code would look like this below.
=VLOOKUP(Sheet2!A1,Sheet3!A:E,2,0)


He then pressed two keys wile in the formula and the formula then looked
like this.
Note " at the beginning and the end.
The formula did not work until he pressed the key combination and the
formula was placed inside " ********* ".

"=VLOOKUP(Sheet2!A1,Sheet3!A:E,2,0)"

Can you explain this step and what is was doing.
Where is the documentation to this process in excel I am sure it must have a
name.

Note
Just placing the formula inside " " does not work you have to press this key
combination like ( Eg ctrl,shift,f2 ) this is not the actual key comb,

Thanks for your help.

Rick

Are you sure that there were " " around the formula and not { } ?
If you use CTRL+SHIFT+ENTER rather than just ENTER to store a formula
you will get an array formula, indicated by the { in the beginning
and } at the end.

Lars-Åke

 

[Per Jessen]

Hi Rick

If you are working with matrix formulas, you need to press:

CTRL + SHIFT + ENTER

every time the formula is changed.

Hopes it helps

//Per

 

[Mike H]

Rick,

I can't imagine how enclosing a formula in quotes would make it do anything
other than appear as text but your friend may have done this. Put this
formula in a cell and press enter.

=AVERAGE(IF(A1:A5>0,A1:A5,FALSE))

Now this averages all the number larger than zero in a1-a5. Put these
numbers in those cells and you'll see you get the average of all the numbers
which is 1.8 which is incorrect.

Select the formula cell again and Tap F2 and this time hold down CTRL+Shift
and tap enter and you will note 2 things. The formula now has curly brackets
around it {} and the formula gives the correct answer of 3.

This is an array formula and you can get more information here. Is that what
your friend did?

http://www.cpearson.com/excel/ArrayFormulas.aspx

1
2
-3
4
5

Mike