+ Reply to Thread
Results 1 to 4 of 4

Use of Row() in array formula not working

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    7

    Use of Row() in array formula not working

    Hello, I have data laid out like the simplified workbook attached - a group of channel values split into bins (0, 0.5, 1...) for each device for each year. On sheet 'Calc', I am calculating summary data, in this case adding up all the monthly Channel1 data points in a year for each bin for each device, for each year.
    The following array formula works (used in column H):

    =SUM((MOD(COLUMN(INDIRECT("'Data"&I$1&"'!C"&ROW($C$3)&":CT"&ROW($C$3)))-COLUMN(INDIRECT("'Data"&I$1&"'!C"&ROW($C$3))),3)=0)*OFFSET(INDIRECT("'Data"&I$1&"'!C3:CT3"),MATCH(OFFSET($B$2,ROUNDDOWN(($L3-3)/7,0)*7,0)&", "&I$1,INDIRECT("'Data"&I$1&"'!$B$2:$B$10000"),0)-1+2*$B3,0))

    First part of the formula identifies every third column from the reference column on the appropriate data sheet, then multiply that array by the second part of the formula, which looks up the appropriate device row on the appropriate data sheet.

    This formula refers to cell $L3 in the match function, which contains the formula =row(). If I put the Row() function into the formula in place of $L3 (as I've done in attached cell I3), I get #VALUE result.
    I cant figure out why. Can anyone tell me why ROW() wont work in place of $L3, and how might I make the formula work such that I dont have to have a special column such as $L which contains the row numbers?

    Thank you,
    jsw
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Use of Row() in array formula not working

    Maybe like this.

    First re-arange the data (with an macro).

    After that I made an pivot table of it.

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Use of Row() in array formula not working

    Hello jsw,

    ROW returns an "array" like {3} even when it'a a single value - this works perfectly well in some functions but others don't like it - you've found one of the latter. You can wrap it in another function like

    SUM(ROW())

    ....but it's better (more robust) to use ROWS function instead, so in I3 instead of ROW()-3 use

    ROWS(I$3:I3)-1
    Audere est facere

  4. #4
    Registered User
    Join Date
    07-24-2013
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Use of Row() in array formula not working

    Thanks DaddyLongLegs for the simple explanation and the solution. Your recommended solution works perfectly.
    Thanks also to Oeldere - the actual data has a lot of dependencies so not easy to rearrange. I will have a good look through your spreadsheet though, as the pivot table layout might be worth the effort.
    jsw

+ 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. [SOLVED] Array Formula Not Working....
    By sagar007 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-09-2012, 01:16 AM
  2. Array formula not working
    By MIAO in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-31-2012, 10:01 AM
  3. Array formula not working
    By bj in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 07:05 AM

Tags for this Thread

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