+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    Registered User
    Join Date
    03-07-2017
    Location
    Online
    MS-Off Ver
    2013
    Posts
    5

    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
    Last edited by hombibi; 01-02-2018 at 12:34 PM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    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.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    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.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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
    Last edited by sandy666; 01-02-2018 at 12:56 PM.

  5. #5
    Registered User
    Join Date
    03-07-2017
    Location
    Online
    MS-Off Ver
    2013
    Posts
    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. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    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. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

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

    Attach the file here, please.

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    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. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    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??
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-07-2017
    Location
    Online
    MS-Off Ver
    2013
    Posts
    5

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

    Answering some earlier questions:

    - 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
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,997

    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.
    Last edited by JohnTopley; 01-03-2018 at 08:12 AM.

  12. #12
    Registered User
    Join Date
    03-07-2017
    Location
    Online
    MS-Off Ver
    2013
    Posts
    5

    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. #13
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    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.
    Last edited by leelnich; 01-03-2018 at 10:35 AM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

  14. #14
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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.
    Attached Files Attached Files
    Last edited by Jonmo1; 01-03-2018 at 10:34 AM.

  15. #15
    Registered User
    Join Date
    03-07-2017
    Location
    Online
    MS-Off Ver
    2013
    Posts
    5

    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. #16
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Array formula doesn't work (CTRL+SHIFT+ENTER)
    By Ztv in forum Excel Formulas & Functions
    Replies: 65
    Last Post: 04-07-2021, 12:29 AM
  2. [SOLVED] Multiple Array Formula & CTRL+SHIFT+ENTER
    By makingtrails in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-30-2014, 09:54 PM
  3. Replies: 8
    Last Post: 02-10-2014, 08:07 PM
  4. [SOLVED] (urgent help pls) how to apply array formula with ctrl+shift+enter
    By otabokauyelikicin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2013, 09:56 AM
  5. With VBA Enter Ctrl Shift Enter For Array Formula
    By realniceguy5000 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-17-2013, 09:02 AM
  6. Array Formula: Ctrl+Shift+Enter does not work
    By georgroth in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2013, 12:15 PM
  7. [SOLVED] CTRL+SHIFT+ENTER array formula not working
    By frusterated in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-05-2012, 12:02 AM
  8. Array Formula: CTRL+SHIFT+ENTER?
    By WebKill in forum Excel General
    Replies: 2
    Last Post: 05-27-2011, 01:56 PM

Bookmarks

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