- Thread starter j_sun
- Start date

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?

=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:

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

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

Last edited:

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.

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:

=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?

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.