Difference between "Fill down" and "select+F2"

[eterenal-studen]

Hi,

I've been trying to work out the difference between two different ways of entering array formulas. I've been looking at the example on this web page

http://www.cpearson.com/excel/noblanks.htm

that tells you how to make a formula to relist a set of items without the blank cells in between.

It's an array formula and you have to enter the formula in the first cell by pressing Ctrl+Shift+Enter. Then you have to select the cell with the formula and the cells directly below it, go to Edit->Fill->Down to get the array formula in to all the cells. Fair enough.

But I also thought that if you entered the first cell with the formula, highlighted the cell and the cells directly below it (as above), then press F2, and then Ctrl+Shift+Enter it would do exactly the same thing. For some reason the formula gives a completely different result.

I thought they were both the same method to insert array formulas that return multiple results. The cells look as though they have the same formula in them. So my question,

What is the difference between these two techniques for entering array formulas?

Many thanks,

Stephen B

[Bryan Hessey]

Hi,

I've been trying to work out the difference between two different ways of entering array formulas. I've been looking at the example on this web page

http://www.cpearson.com/excel/noblanks.htm

that tells you how to make a formula to relist a set of items without the blank cells in between.

It's an array formula and you have to enter the formula in the first cell by pressing Ctrl+Shift+Enter. Then you have to select the cell with the formula and the cells directly below it, go to Edit->Fill->Down to get the array formula in to all the cells. Fair enough.

But I also thought that if you entered the first cell with the formula, highlighted the cell and the cells directly below it (as above), then press F2, and then Ctrl+Shift+Enter it would do exactly the same thing. For some reason the formula gives a completely different result.

I thought they were both the same method to insert array formulas that return multiple results. The cells look as though they have the same formula in them. So my question,

What is the difference between these two techniques for entering array formulas?

Many thanks,

Stephen B

Never used the F2, but either the Formula drag (fill) or the Paste requires CSE on only the original entry. My preferred method is to enter a formula ('enter' or 'CSE' as required), copy that, select the required copy area (either range-drag, columnn select, or type the range into the Name box) and Paste.

If you had a different result was that due to the 'array' failing? (as denoted by no braces {} ) ? - or are there any other clues?


note, one possible difference is that you omitted $ on the range.
---

[eterenal-studen]

Hi,

Thanks very much for the reply. Yes, you've made me see sense a bit. You're right, I think I normally use that method you gave in your reply; entering the formule (with Enter or Ctrl+Shift+Enter) and then copying the cell and the selecting the cells below and pasting in. I hadn't realised but that's exactly the same as the edit->fill->down method I stated.

I think the F2+CSE method is for an array formula that returns an array. It was giving me a strange result which is where I was getting confused. But your reply has made me see sense now! Thanks very much,

Stephen B