• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Plain language definition of array formulas vs other formulas

j_sun

Member
Hi All,

For whatever reason my simple mind can't understand what an array formula is compared to other formulas.

Can someone give me some simple examples and also tell me the power and benefit that using array formulas have?


Many thanks,

Jason
 

Luke M

Excel Ninja
An array is a group of values, rather than a single item. While a single cell reference like A1 would return 1 value, the array A1:A5 would return 5 values. Technically, when you commonly use a SUM function, you are feeding it an array, like this:

=SUM(A1:A5)
=SUM(1,4,3,2,1) <- I just made up some values for what was in A1:A5
=11
The more powerful way of using arrays is when we need to do a comparison across mutliple lines. Check out the latest Formula Forensics article for an explanation:

http://chandoo.org/wp/2012/03/01/formula-forensic-014/


When working with certain functions like IF, we have to manually tell XL that we want an array sometimes, otherwise XL will treat it like a single value. For examples, lets look at this data

Col A Col B
10 1
41 2
32 3
41 4
50 5
With this formula:

=MAX(IF(A1:A5>40,B1:B5))

If XL treats this as single values, it will only look at the first cell reference from the array, and do this:

=MAX(IF(A1>40,1))

=MAX(IF(10<40,1))

=MAX(IF(False,N/A)

=MAX(False)

=0


But if we tell it to treat is as an array formula, it becomes:

=MAX(IF({10,41,32,41,50}>40,{1,2,3,4,5}))

=MAX(IF({False,True,False,True,False},{1,2,3,4,5}))

=MAX(False,2,False,4,5}

=5


Does that help?
 
Last edited:

j_sun

Member
Hi Luke,

This is helpful. I still am not sure of one thing:


"But if we tell it to treat is as an array formula"


What did you do to make it an array formula in the bottom example vs the top one?


Thanks
 

Luke M

Excel Ninja
Whoops, sorry about that. When you input a regular formula, you confirm it using just the Enter key. However, if you want to force XL to treat it as an array formula, you confirm by holding down Ctrl+Shift and then hitting the Enter key. When done properly, a pair of curly brackets will appear around the formula, so it would look like this in the formula bar:

{=MAX(IF(A1:A5>40,B1:B5))}
 
Last edited:

j_sun

Member
Ahhhhh. I think that is one piece of info I've been missing that helps tie all this together!


This has been most informative but I have one more small favor to ask...


As I am still not that fluent in formulas can you, in plain language, describe what your example formula is doing?


{=MAX(IF(A1:A5>40,B1:B5))}


Thanks for all the help.
 
Last edited by a moderator:

Luke M

Excel Ninja
Sure. The intent of the formula is to find the largest value (MAX) from the set of numbers in column B that have a value in col A that is greater than 40. So, the formula first looks at all the numbers in col A, determines which ones are greater than 40, and if so, it passes that number into the MAX function, as seen here:

=MAX(IF({False,True,False,True,False},{1,2,3,4,5}))

=MAX(False,2,False,4,5}


Since the 2nd, 4th, and 5th numbers in col A met the criteria, the 2nd, 4th, and 5th numbers from Col B were fed into the MAX function. Questions?
 

DonMinter

Member
Usually, looking at Excel formulas "inside out" makes more sense when you try to put it into English:


Look through the cells from A1 to A5. If that value is bigger than 40, then the corresponding value in B1 to B5 is "on the list" to be the MAX value. Give me the MAX value as the value of this cell.
 

SirJB7

Excel Rōnin
@DonMinter

Hi!

Now... gotcha! I discovered why sometimes they don't make any sense for me... I put them into Spanish... :)

And just God knows how I translate them.

Regards!
 
Top