Normal v Array formulae
Results 1 to 5 of 5

Thread: Normal v Array formulae
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,053
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Normal v Array formulae

    On my worksheet, cells A1 through to B6, I have the values 1,2,3,4,5,6 from A1, A2, A3, etc.

    In cell D1, I type:

    Code:
    =SUM(A1:A3*B1:B3)
    
    


    and enter it as an ARRAY formula to get the result of:

    Code:
    32
    I understand how this is arrived. It is multiplying cell A1 with B1, then A2 with B2 and then A3 with B3, then summing it, ie 4+10+18=32.

    However in cell D2, if I typed:

    Code:
    =SUM(A1:A3*B1:B3)


    but this time I enter it as a NORMAL formula, I get the result of:

    Code:
    10
    What exactly is Excel doing to get the value of 10?

    Thanks



  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,051
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Normal v Array formulae

    It's multiplying 2*5, ie the values in A1:B6 that are on the same row as the formula

    If you select the cell & then click "Evaluate formula" on the formula tab, you can see what it's doing.
    Last edited by Fluff; Aug 25th, 2019 at 08:42 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,930
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Normal v Array formulae

    It is a "Implicit Intersection" behavior in Excel, and try to Google for further detailing

    Regards

  4. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,404
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Normal v Array formulae

    First, you should write =SUMPRODUCT(A1:A3,B1:B3) instead, which is normally-entered.

    Second, =SUM(A1:A3*B1:B3) seems to work only if it is entered in row 1, 2 or 3. It is interpreted as =SUM(A1*B1), =SUM(A2*B2) or =SUM(A3*B3) respectively. And if that is the intent, there is no reason to uses the SUM function in that context. Simply write =A1:A3*B1:B3.

    That interpretation is most useful for named ranges, e.g. "cost" defined as A1:A3 and "qty" defined as B1:B3, and your intent is to enter the formula =cost*qty for each of 3 transactions in rows 1, 2 and 3.

    The point is: if you normally-enter =SUM(A1:A3*B1:B3) into any other row, you will get a #VALUE result, correctly informing you that the form is incorrect.
    Last edited by joeu2004; Aug 25th, 2019 at 03:36 PM.

  5. #5
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,053
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Normal v Array formulae

    Thanks for all the explanation.

    I don't normally write formulae like the second one, it was actually more out of curiosity than anything.

    However, it is handy to find out exactly what it's doing, just in case I ever inherit someone else's spreadsheets.
    Last edited by tiredofit; Aug 26th, 2019 at 10:31 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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