+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 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
    MS Office Excel 2007
    Posts
    5,835

    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 Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,906

    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



  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    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.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  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
    MS Office Excel 2007
    Posts
    5,835

    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
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    20,015

    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!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  8. #8
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,906

    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 Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,906

    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
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    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
    '97, 2016
    Posts
    2,523

    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 any helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion. (Thread Tools above Post # 1) Thanks!-Lee

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

    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

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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