Using Array Constants in your formulas

If you like this post please share it!
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

print
I find I use this kind of formula a lot…

=IF(AND(B5<>{“A”,”B”,”C”,”D”}),”Yes”,”No”)

Instead of

=IF(AND(B5<>”A”,B5<>”B”,B5<>”C”,B5<>”D”),”Yes”,”No”)

And imagine if you want to test for 20 items B5 is not equal to?  It’s shorter and easier to read and edit.  I discovered this by just playing around way back but it turns out these are called Array Constants and it can make many formulas simpler. 

Consider…

=SUM(COUNTIFS(A1:A10,{“TypeA”,”TypeB”}))

will count instances of TypeA OR TypeB in the A1:A10 range.  If say you enter TypeA and TypeB in that range with some other values the COUNTIFS part of this formula returns an array of {1,1} for the two instances found and then the SUM() formula wraps COUNTIFS to convert the array values to a value it can put into a cell so adds the 1 + 1 to return what you want which is 2.

You can also use Array Constants for simplifying nested IF statements which is an adjunct to an earlier post I did of Nested IFs (and avoiding them).

Here is a great example from Ozgrid.com where A1:A20 has values of 1,2,3…20 and this formula is entered to B1:B20

 =LOOKUP(A1,{1,6,11,16},{“1-5″,”6-10″,”11-15″,”16-20”})

SYNTAX:=LOOKUP(lookup_value,lookup_vector,result_vector)

It categorizes values of 1-20 into groupings.  That’s a really practical use and example.

You might have not noticed the LOOKUP function before apart from it’s regular use (Office Support defines this as: Use LOOKUP, one of the lookup and reference functions, when you need to look in a single row or column and find a value from the same position in a second row or column), it’s ideal for looking up lookup vectors and result vectors within the actual formulas.  If you can’t recall from high school maths, vectors are 1-dimensional arrays so {1,6,11,16} is one vector and {“1-5″,”6-10″,”11-15″,”16-20”} is another.

So when you have a complex formula because it’s got a lot of testing to do be aware of array constants as a simpler way.  OK as with any constants there is a danger the values in the array constants may need to change so the poor user coming along after you has to find these buried in the formula to make a change.  But some values in practical situations rarely change, like this old chestnut for bean counters of grouping months into quarters…

 =LOOKUP(A1,{1,4,7,10},{“Qtr 1″,”Qtr 2″,”Qtr 3″,”Qtr4”})

Get’s you thinking… there are lots of ways to use Array Constants if you have an awareness for it.

 

Tagged with: , ,