Normal v Array formulae

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,924
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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:
Upvote 0
It is a "Implicit Intersection" behavior in Excel, and try to Google for further detailing

Regards
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top