How Do I Know I Have an Array Formula?

[bencar]

How do i know I have an array formula? For example, how do I know when to add brackets around this array formula?
{B2:I2*B3:BI}

And also with this?
{=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))}

 

[sheetspread]

Notice that when you enter the formulas without holding down ctrl-shift usually only the first cell of the array is calculated.

 

[bencar]

Notice that when you enter the formulas without holding down ctrl-shift usually only the first cell of the array is calculated.

Very confused by your answer. Would you further explain??

 

[dave2018]

An Array formula is a formula that does calculations on many cells at once. For example, =A1="jim" Takes cell A1 and sees if it is equal to "jim". The array formula of =A1:A10="jim" Means that excel looks at A1, is it equal? then A2, is it equal etc. until the end.

In such a case, you would need to add curly brackets with CTRL+SHIFT+ENTER to tell excel that u want it to calculate all the cells in the range (and not just 1 cell..)

Disclaimer: sometimes, you can get around pressing CTRL+SHIFT+ENTER!!! See the following formulas:

Excel 2016 (Windows) 32 bit
	A	B	C
1	Jim	Without CSE (Bad)----->	1
2	Adam	With CSE (Good)----->	3
3	Ben	Without CSE (Good)----->	3
4	Adam
5	Jim
6	Adam
7	Ben
8	Adam
9	Jim
10	Adam
Sheet3

Cell Formulas
Range	Formula
C1	=SUM(--(A1:A10="Jim"))
C3	=SUMPRODUCT(--(A1:A10="Jim"))
C2	{=SUM(--(A1:A10="Jim"))}
Press CTRL+SHIFT+ENTER to enter array formulas.

PS: beginning to learn array formulas is like learning a new excel! But you get used to it pretty quickly with focus and consistency.

Also, U need to check out Mike ExcelIsFun Girvin on youtube! He has playlists of videos just on array formulas and functions. He also wrote a book called CTRL+SHIFT+ENTER (which explains all about array formulas - the only book of its kind, the way he lists it out methodically etc.)

And of course, if u have any more q's, u can probably count on MrExcel Forum!

Good Luck!

 

[Aladin Akyurek]

How do i know I have an array formula? For example, how do I know when to add brackets around this array formula?
{B2:I2*B3:BI}

And also with this?
{=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))}

If you have bits with

[1] pairwise multiplications like

(A2:A4)*(B2:B4)

(A2:A4="jim")*(B2:B4)

[2] with IF(s) which act(s) as filter like

IF(A2:A4>=2,1)

[3] with multi-valued references which serve as look up value or comparison value like...

MATCH(A2:A4,B2:B4,0)

A2:A4=B2:B4

you have to do with an array-processing formula.

The foregoing list is probably not exhaustive. The gist is: bits leading to multi-value results indicate array-processing, therefore control+shift+enter if the receiving function is not designed exlusively for array-objects like SUMPRODUCT.

Hope this helps.

 

[dave2018]

If you have bits with

[1] pairwise multiplications like

(A2:A4)*(B2:B4)

(A2:A4="jim")*(B2:B4)

[2] with IF(s) which act(s) as filter like

IF(A2:A4>=2,1)

[3] with multi-valued references which serve as look up value or comparison value like...

MATCH(A2:A4,B2:B4,0)

A2:A4=B2:B4

you have to do with an array-processing formula.

The foregoing list is probably not exhaustive. The gist is: bits leading to multi-value results indicate array-processing, therefore control+shift+enter if the receiving function is not designed exlusively for array-objects like SUMPRODUCT.

Hope this helps.

Another Disclaimer, that even SUMPRODUCT can need CSE (CTRL+SHIFT+ENTER) because a function inside of the sumproduct may need CSE. so it's not 100% clear cut, which may be 1 of the reasons microsoft doen't really provide documentation for it so much...

 

[sheetspread]

Very confused by your answer. Would you further explain??

Aladin and Dave explained it more thoroughly, but here's another example:

Excel 2010
	A	B	C	D	E	F	G
1	HXN	GEH	TZD	11
2	jim	ben	alice	30			30
3	WOA	ben	BAK	23			45
4	FXN	BUW	EIG	71
5	jim	JXP	alice	4
6	KPU	ben	RTH	83
7	JCH	LO	LZB	50
8	jim	ben	alice	15
9	jim	UBJ	ICO	85
10	jim	JCR	HIF	54
Sheet2

Cell Formulas
Range	Formula
G2	=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))
G3	{=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))}
Press CTRL+SHIFT+ENTER to enter array formulas.

without CSE only the first number is returned, with CSE you get sum({30,15})

 

[Domenic]

If you have bits with

[1] pairwise multiplications like

(A2:A4)*(B2:B4)

(A2:A4="jim")*(B2:B4)

[2] with IF(s) which act(s) as filter like

IF(A2:A4>=2,1)

[3] with multi-valued references which serve as look up value or comparison value like...

MATCH(A2:A4,B2:B4,0)

A2:A4=B2:B4

you have to do with an array-processing formula.

The foregoing list is probably not exhaustive. The gist is: bits leading to multi-value results indicate array-processing, therefore control+shift+enter if the receiving function is not designed exlusively for array-objects like SUMPRODUCT.

Hope this helps.

Nice explanation Aladin. I'm book marking it.

 

[bencar]

If you have bits with

[1] pairwise multiplications like

(A2:A4)*(B2:B4)

(A2:A4="jim")*(B2:B4)

[2] with IF(s) which act(s) as filter like

IF(A2:A4>=2,1)

[3] with multi-valued references which serve as look up value or comparison value like...

MATCH(A2:A4,B2:B4,0)

A2:A4=B2:B4

you have to do with an array-processing formula.

The foregoing list is probably not exhaustive. The gist is: bits leading to multi-value results indicate array-processing, therefore control+shift+enter if the receiving function is not designed exlusively for array-objects like SUMPRODUCT.

Hope this helps.

Dave,

I really dont get what youre saying. Could you explain with easier examples?

 

[bencar]

Aladin and Dave explained it more thoroughly, but here's another example:

Excel 2010

	A	B	C	D	E	F	G
HXN	GEH	TZD
jim	ben	alice
WOA	ben	BAK
FXN	BUW	EIG
jim	JXP	alice
KPU	ben	RTH
JCH	LO	LZB
jim	ben	alice
jim	UBJ	ICO
jim	JCR	HIF

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]71[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]83[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]85[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G3[/TH]
[TD="align: left"]{=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


without CSE only the first number is returned, with CSE you get sum({30,15})

What first number is returned? Using the formula without CSE I get the results in column F. With CSE I get results in column G. Heres what I mean:

[TABLE="width: 616"]
<tbody>[TR]
[TD="class: xl64, width: 88"]HXN[/TD]
[TD="class: xl64, width: 88"]GEH[/TD]
[TD="class: xl64, width: 88"]TZD[/TD]
[TD="class: xl64, width: 88, align: right"]11[/TD]
[TD="width: 88"][/TD]
[TD="width: 88, align: right"]0[/TD]
[TD="class: xl63, width: 88, align: right"]45[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]ben[/TD]
[TD]alice[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="class: xl63, width: 88, align: right"]45[/TD]
[/TR]
[TR]
[TD]WOA[/TD]
[TD]ben[/TD]
[TD]BAK[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, width: 88, align: right"]15[/TD]
[/TR]
[TR]
[TD]FXN[/TD]
[TD]BUW[/TD]
[TD]EIG[/TD]
[TD="align: right"]71[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, width: 88, align: right"]15[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]JXP[/TD]
[TD]alice[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, width: 88, align: right"]15[/TD]
[/TR]
[TR]
[TD]KPU[/TD]
[TD]ben[/TD]
[TD]RTH[/TD]
[TD="align: right"]83[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, width: 88, align: right"]15[/TD]
[/TR]
[TR]
[TD]JCH[/TD]
[TD]LO[/TD]
[TD]LZB[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, width: 88, align: right"]15[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]ben[/TD]
[TD]alice[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="class: xl63, width: 88, align: right"]15[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]UBJ[/TD]
[TD]ICO[/TD]
[TD="align: right"]85[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, width: 88, align: right"]0[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]JCR[/TD]
[TD]HIF[/TD]
[TD="align: right"]54[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, width: 88, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]




Btwm, how were you able to paste cells from excel in your post??