# array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTER

1. ## array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTER

Hi,

I think that the following array formula should give a sequence of numbers in the selected range, entered with ctrl + shift + enter:

{=ROW(C4) - ROW(C\$4)} (without the {}.)

However in my case: excel 2016, all values are the same: zero.

If I enter the formula with CTRL+ENTER it works, but it is not an array formula anymore.

Is this expected behavior of excel? Does anyone understand what's going on or what I am doing wrong?

Thanks,
Hombibi

2. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

Why does it need to be an array formula? Who told you this?

Entering the formula as a normal formula produces the sequence 0,1,2,3,4,5,6 which is what you want.
Just enter it as a normal formula.

3. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

It's not an array formula. It's just a regular formula... No need for CSE. It also works when array entered. What you were probably doing wrong was selecting a range of cells and CSE. You should CSE an array formula in one cell and then drag it.

However, in this case, enter will do fine.

4. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

=ROW(C4) - ROW(C\$4) and drag down will give you:0,1,2,3,4,5...
=ROW(C4) - ROW(C\$4) with Ctrl+Shift+Enter and drag down will give you as above
if you paste formula into the C4, then select range C4:C8 then click formula in formula bar and press and hold Ctrl then hit Enter it will give you as above

maybe try: =ROWS(\$1:1)-1 (normal entered) and drag down

5. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

The formula is part of a larger formula that needs to be entered as an array formula, based on "Excel Magic Trick 185 Dynamic Formula Extract Data 1criteria"
I could not get it to work and have pinpointed it to the issue described.

Entering an array formula and dragging down works in this example. However is it reliable? I thought the target range has to be pre-selected.

In the attached spreadsheet (from Excel magic trick 185) I added three columns with: One pulling down the formula, one arryed in the first row and subsequently pulled down and the third arrayed at once over all rows. The results are different and only the third one is correct. I can't replicate this in an excel 2016 version myself though.

Seems I can't attach an excel file. I hope it is clear what I did, the file can be downloaded from https://people.highline.edu/mgirvin/...02Finished.xls

6. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

To attach files:

Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

7. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

8. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

Where are we to find the formula? too much "stuff" to wade through!!

9. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

Found it. Still confused though. Does ths not do what you want, in I6, copied across and down:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(Date)/(Dep=\$J\$13),ROWS(\$1:1))),"")

Since you have a recent version of Excel, do you really need compatibility back to pre-2003??

10. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

- I don't need an excel 2003 solution, I intended to reuse and adapt the formula in my Excel 2016.

- That's indeed confusing, that is another solution, the quoted formula in row 10 is completely different from the used formula in that sheet. I'll have to look into that. Leaves open the original question though:

I have added the spreadsheet I was referring to, in which the formula quoted is the same, but also actually used in the tables. I think your sheet is a later adaption of the one I have here attached, maybe someone ran into the same problem with 2016?

The strange thing is that the formula's work in the sheet, but I can't replicate them, not even on the same tab.

In the sheet attached I added column at P11 to Y11 to illustrate the issue.
• The first column with the red header and the first with the black header show that using a pull-down/copy down formula may lead to different results, which would therefore require a different formula.
• The second in red and black represent the traditional array formula approach: "select total area, enter formula in first cell, CTRL+SHIFT+ENTER", which for some reason does not seem to work in Excel 2016: in red the non-FALSE cell values should start at 1, and in black the values should be a sequence starting at 0.
• In the third column with the red and black header I entered an array formula in the first cell and subsequently pulled it down, the results are wrong in red, and correct in the third black column. As I don't understand why, I can't rely on the results.

I also added a "table" object in blue. the table object refuses the traditional array formula approach. Pull down an array formula has the same result as with the ranges.

I don't understand what's going on, formula works in the sheet, but can't even be copied to another section on the same sheet, and entering a simple row function with an array formula in a selected range fails, deferring the main purpose of array formula's

Hombibi

11. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

I don't understand your issue: all formula work as expected. Although I haven't 2016 I am certain the formulae work the same if entered correctly.

For example, the array formula in Q returns the index where a match is found: so 30 in Q45 is the 30th element of range DEP, Q49 is the 34th element of DEP etc.

This formula ...

=IF(ROWS(J\$16:J16)<=\$K\$13,INDEX(INDIRECT(J\$15),SMALL(IF(Dep=\$J\$13,ROW(Dep)-ROW(\$C\$13)+1),ROWS(J\$16:J16))),"")

uses that logic to list all matches with J13, ignoring blanks,

=IFERROR(INDEX(INDIRECT(J\$15),SMALL(IF(Dep=\$J\$13,ROW(Dep)-ROW(\$C\$13)+1),ROWS(J\$16:J16))),"")

will do the same

as does Glenn's formula

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(Date)/(Dep=\$J\$13),ROWS(\$1:1))),"")

If you select a range, enter formula into formula box then CSE, the formula is simply repeated (identical) in all cells of the range.

Hence array formula should be entered in the first cell of a range, then copied down.

12. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

Hi John,

Maybe I don't understand Array formulas, please let me try one more time, I'll use the example from Microsoft from

https://support.office.com/en-us/art...2-ecfd5caa57c7

Microsoft makes a distinction between multi-cell and single cell array formula's. I intended to use the first, as per the instruction for the spreadsheet I copied here.

Anyway.

If I take your approach then I add the formula C2:C11*D2:D11 to the first cell E2, and then "CTRL+SHIFT+ENTER". If I then pull down the array formula all cells but the first display a value error because the range ran out of the table while pulling (copying) down the array formula. The first cell displays the grand total of unites times prices.

To my expectation all results are wrong, also the first one. I could have added dollar signs to the ranges, but he example does not do that either.

If I then enter the formula as described in the example (enter formula in first, select range, ctrl+shift+enter) it also does not work: I get, in every row, the grand total of 1590000, while I should get per row the nr of units x price value.

First: I don't think the argued "enter an array formula and copy it down" approach is the same thing as selecting the whole range and than ctrl+shift+enter.

However, my real is that I need a multi cell array formula and can't get it to work.

My problem is therefore that my Excel2016 does not behave as described in the Microsoft documentation, and my question is what am I missing/not seeing.

Hombibi

13. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

Hi all. @hombibi, the only time you enter a formula as a multi-cell array formula is if that formula returns an array of values, so that you need more than one cell to contain them. If the formula uses array(s) internally to yield a single result, then it is array-entered in a single cell. That cell can then be copied down, yielding multiple individual results.
Your formula =ROW(C\$4)-ROW(C4) neither uses nor returns an array. Therefore, it may be entered normally.

PS The formula =ROW(C4:C203)-4 does yield an array of results {0,1,2...,199}, and would require entry as a multi-cell array formula...unless it's used by a function that returns a single result. So =SUM(ROW(C4:C203)-4) is a single-cell array formula.

14. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

The formulas in column P and Q are very similar

The named range Dep refers to ='Y(185)'!\$C\$16:\$C\$237

If you just put this in a cell by itself (without CSE)
='Y(185)'!\$C\$16:\$C\$237
That's not really a valid cell reference.
You can only make a direct reference to a single cell
You would have to add some sort of function to actually 'USE' that multicell range. Like SUM for example
But rather than error, Excel assumes you meant to use the cell in the range 'relative to' the cell you entered the formula in.
Each formula in P refers to a cell within C16:C237 'relative to' the cell the formula was entered in (the same row).
So in P16, formula refers to C16
in P17, formula refers to C17
etc..
This is why they it will return the #Value error if extended beyond the range 'relative to' C16:C237. Because it no longer has a logical reason to reference a cell with.
I think this is called an 'Implied Reference'

The one in column R simply sees the first cell of the named range, C16. No matter which cell it is entered in.

See this small scale example.
MyRange refers to \$A\$5:\$A\$10
B3 was entered as a single formula without CSE and dragged down
C3:C13 was selected and entered with CSE
D3 was entered with CSE then dragged down.

15. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

Ok, I think I got it now: so I am dealing with a single cell array formula, while I was thinking all the time that I had a multi cell array value.

Thanks everyone trying to point that out to me, and thanks Leelnich for focusing my attention directly on the issue (which was me looking in the wrong direction..).

Hombibi

16. ## Re: array formula with row fails, if entered with CTRL+SHIFT+ENTER, but not with CTRL+ENTE

Happy to help, thank you for the rep! If complete, please mark your thread as SOLVED (Thread Tools up top). Regards - Lee

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1