Plain language definition of array formulas vs other formulas
- 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:
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?
=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?
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.
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.
