Array constants can help simplify your formulas.
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 20 items for 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.