How Do I Know I Have an Array Formula?

bencar

Banned user
Joined
Jun 8, 2016
Messages
149
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))}
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
ABC
1JimWithout CSE (Bad)----->1
2AdamWith CSE (Good)----->3
3BenWithout CSE (Good)----->3
4Adam
5Jim
6Adam
7Ben
8Adam
9Jim
10Adam
Sheet3
Cell Formulas
RangeFormula
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!
 
Last edited:
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
Very confused by your answer. Would you further explain??

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


Excel 2010
ABCDEFG
1HXNGEHTZD11
2jimbenalice3030
3WOAbenBAK2345
4FXNBUWEIG71
5jimJXPalice4
6KPUbenRTH83
7JCHLOLZB50
8jimbenalice15
9jimUBJICO85
10jimJCRHIF54
Sheet2
Cell Formulas
RangeFormula
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})
 
Last edited:
Upvote 0
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. :)
 
Upvote 0
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?
 
Upvote 0
Aladin and Dave explained it more thoroughly, but here's another example:

Excel 2010
ABCDEFG
1HXNGEHTZD11
2jimbenalice3030
3WOAbenBAK2345
4FXNBUWEIG71
5jimJXPalice4
6KPUbenRTH83
7JCHLOLZB50
8jimbenalice15
9jimUBJICO85
10jimJCRHIF54

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
G2=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
G3{=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))}

<tbody>
</tbody>

<tbody>
</tbody>


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:

HXNGEHTZD11045
jimbenalice303045
WOAbenBAK23015
FXNBUWEIG71015
jimJXPalice4015
KPUbenRTH83015
JCHLOLZB50015
jimbenalice151515
jimUBJICO8500
jimJCRHIF5400

<tbody>
</tbody>




Btwm, how were you able to paste cells from excel in your post??
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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