Normal v Array formulae

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,203
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:

Rich (BB code):
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=SUM(A1:A3*B1:B3)
and enter it as an ARRAY formula to get the result of:

Rich (BB 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:

Rich (BB code):
=SUM(A1:A3*B1:B3)


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

Rich (BB code):
10
What exactly is Excel doing to get the value of 10?

Thanks


[/FONT]
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,476
Office Version
365
Platform
Windows
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:

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
It is a "Implicit Intersection" behavior in Excel, and try to Google for further detailing

Regards
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,744
Office Version
2010
Platform
Windows
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:

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,203
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:

Watch MrExcel Video

Forum statistics

Threads
1,102,015
Messages
5,484,206
Members
407,434
Latest member
huynnguyen

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top