+ Reply to Thread
Results 1 to 7 of 7

Sumproduct produces #VALUE! if Row() is included in Offset funtion

  1. #1
    Registered User
    Join Date
    02-05-2022
    Location
    Florida, USA
    MS-Off Ver
    Office 365 -Excel 2016
    Posts
    2

    Sumproduct produces #VALUE! if Row() is included in Offset funtion

    I need to execute a Sumproduct using an Offset function with an included Row() function, but it results in a #VALUE! error. Hovering over the error flag says "A value used in the formula is of a wrong data type". The formula works until I add the Row() function. I've tried enclosing the Row() function in an N function, and in an INT function to no avail. Is there any way to work around this? A small sample spreadsheet is attached and here is a brief description.

    A1:1 B1: 5
    A2:2 B2: 10

    These formulas work:
    A5: =SUMPRODUCT(A1:A2,B1:B2) ==> 25
    A6: =SUMPRODUCT(OFFSET(A1,0,0,2,1),B1:B2) ==> 25
    A7: =SUMPRODUCT(OFFSET(A1,7-7,0,2,1),B1:B2) ==> 25

    This formula causes the #VALUE! error:
    A8: =SUMPRODUCT(OFFSET(A1,ROW()-8,0,2,1),B1:B2) ==> #VALUE!

    Note that Evaluate Formula for the formulas in A6, A7 and A8 all produce identical results until the very last step, when A6 and A7 produce the correct answer and A8 generates the error.

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Sumproduct produces #VALUE! if Row() is included in Offset funtion

    you need to use double hyphen (coerce), like this:
    Please Login or Register  to view this content.
    do the same with all 3 rows, and the results will work

  3. #3
    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
    28,001

    Re: Sumproduct produces #VALUE! if Row() is included in Offset funtion

    This works ..

    =SUMPRODUCT(OFFSET($A1,ROWS($1:8)-8,0,2,1),$B$1:$B$2)

    but (like the Op's result) this does not

    =SUMPRODUCT(OFFSET($A1,ROW()-8,0,2,1),$B$1:$B$2)

    not this

    =SUMPRODUCT(--(OFFSET($A$1,ROW()-8,0,2,1))*($B$1:$B$2))

    It appears SUMPRODUCT does not handle the ROW()-8 type of construct (at least not in Excel 2010)

    As the Op has 365 & 2016, it would suggest this is "generic".
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  4. #4
    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,893

    Re: Sumproduct produces #VALUE! if Row() is included in Offset funtion

    And both:

    =SUMPRODUCT(OFFSET(A1,ROW(8:8)-8,0,2,1),B1:B2)

    and

    =SUMPRODUCT(OFFSET(A1,ROW(A8:A8)-8,0,2,1),B1:B2)

    work for me (O365). It must be a "feature" of Excel's (i.e. a bug...).
    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

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sumproduct produces #VALUE! if Row() is included in Offset funtion

    No bug!

    You'd suffer the same fate if you used any other function whose output is an array, for example MODE.MULT:

    =SUMPRODUCT(OFFSET(A1,MODE.MULT(0,0),0,2,1),B1:B2)

    also returns #VALUE!, even though

    =MODE.MULT(0,0)

    placed in an actual worksheet cell, returns 0, as of course does

    =ROW()-8

    But this conversion from array-type to value-type only occurs when those arrays are passed to a worksheet range. In-formula, they are still arrays, albeit arrays comprising a single value only.

    So, for row 8

    ROW()

    is actually generating the array {8}, not the value 8, which you can verify by going into the formula bar, highlighting the formula and pressing F9.

    In an actual worksheet cell, this array is returned to a single cell as the value 8, just as, with the new spilled array feature in 365, a formula such as

    ={1;2;3}

    would be returned to a vertical array of 3 cells as the values 1, 2 and 3.

    But in-formula, the output from ROW() is an array, until resolved otherwise. So

    =SUMPRODUCT(OFFSET(A1,ROW()-8,0,2,1),B1:B2)

    resolves to

    =SUMPRODUCT(OFFSET(A1,{0},0,2,1),B1:B2)

    and this is not the same as

    =SUMPRODUCT(OFFSET(A1,0,0,2,1),B1:B2)

    since here, in-formula, there is no coercion from array to value.

    The only "bug" here, in my opinion, is that the Evaluate Formula window does not show this resolution of ROW() within the OFFSET construction (though it does with MODE.MULT).

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    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,893

    Re: Sumproduct produces #VALUE! if Row() is included in Offset funtion

    Ahhh. I recall having seen this, in reverse, or something like it... trying to pass a single value into a MATCH array, where a Named Range, containing a FILTERXML array consisting of text values (Array2) needed the &{""} phrase to convert it to an array to cover occasions when Array2 contained only a single value.

    The use of XMATCH removes that need.

    I've never gone on to investigate what's needed to get MATCH to work if the FILTERXML array contains numbers.

    =(MATCH(Array1,Array2&{""},0)

    Edit:

    =(MATCH(Array1,Array2+{0},0) works when the array is numerical. XMATCH works fine without it.
    Last edited by Glenn Kennedy; 02-06-2022 at 06:05 AM.

  7. #7
    Registered User
    Join Date
    02-05-2022
    Location
    Florida, USA
    MS-Off Ver
    Office 365 -Excel 2016
    Posts
    2

    Re: Sumproduct produces #VALUE! if Row() is included in Offset funtion

    Thanks to everyone for the great help, especially to XOR LX, who not only explained the reasoning underlying the problem, but also taught me a more friendly definition of the word "coercion"!

+ 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. OFFSET - funtion
    By rogrand in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-31-2019, 08:18 PM
  2. Sumproduct produces #NUM! error, double unary operator seems to be failing
    By #vlookup in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-21-2017, 03:54 PM
  3. [SOLVED] Offset - same formula produces different results
    By BRISBANEBOB in forum Excel General
    Replies: 4
    Last Post: 09-23-2016, 07:40 PM
  4. [SOLVED] How to eliminate the OFFSET funtion from excel equations
    By MattRNR in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-02-2016, 01:03 AM
  5. [SOLVED] Sumproduct produces #N/A
    By antexity in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-30-2015, 10:50 AM
  6. SUMIF / SUMPRODUCT Funtion
    By akkerstad in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-26-2015, 01:34 PM
  7. Sumproduct and frequency funtion sorting
    By nparsons75 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2014, 08:16 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