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))}

 

[MARK858]

What first number is returned?

Jim = 30 from Row 2

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

Dominic uses the MrExcel HTML maker that you have already had links to given to you in your other thread when you asked how to post.

 

[Aladin Akyurek]

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.

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

You can check the behavior of the examples I gave for yourself.

Create a sheet, Name it 1.

Enter number in A2:A4 and in B2:B4.

In A6 enter:

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

Observe the result.

Select A6. Go to the Formula Bar. Select what you see. Hit F9. Observe the result which is an array given between { and }. This is a multi-value result. Hit the Escape key.

In A7 enter:

=SUM((A2:A4)*(B2:B4))

As you see, SUM does not aggregate (total) the array F9 shows.

In A8 control+shift+enter:

=SUM((A2:A4)*(B2:B4))

Now you see a total, a value which is aggregated by SUM upon the control+shift+enter instruction.

Create a new sheet. Name it 2. Try to evaluate the example given under [2].

And, create a new sheet. Name it 3. Try to evaluate the example given under [3].

 

[sheetspread]

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

In your example put $ signs around the range before you copy down to get the correct results. (highlight the range and press F4). But you see {30,15} --not counting the zeroes in between.

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

To post your spreadsheet as above:

https://www.mrexcel.com/forum/about-board/947826-why-cant-i-add-attachments-my-threads.html