Wednesday, January 04, 2006

Reading complex formulas in Excel

Sometimes we end up having to write complex formulas in order to get the required logical results from a spreadsheet.
Here is one.
=OR(LEFT(OFFSET(data!$A2,0,$C$22-1),2)="SM",
LEFT(OFFSET(data!$A2,0,$C$22-1),5)="ANDER",
OFFSET(data!$A2,0,$C$22-1)="MILLER",
AND(OFFSET(data!$A2,0,$C$22-1)="BROWN",
LEFT(OFFSET(data!$A2,0,$C$17-1),2)="TO"),
OFFSET(data!$A2,0,$C$22-1)="JONES")

On its own this can look very confusing.
However once you take out the added complexity of the OFFSET functions (which just delineate the array to query) the formula can be restated like this.

=OR(LEFT(cell_reference1,2)="SM",
LEFT(cell_reference1,5)="ANDER",
cell_reference1="MILLER",
AND(cell_reference1="BROWN",
LEFT(cell_reference2,2)="TO"),
cell_reference1="JONES")

Ok this is a little less complicated but still quite confusing.
Lets now give the cell references names that make sense in the context of the spreadsheet. I will assume that the spreadsheet is a list of first and last names where cell_reference1 is last name and cell_reference2 is first name.

Now we can work out the logic of this formula.
This formula will return TRUE if any of the following conditions are true:
Last name begins with SM
Last name begins with ANDER
Last name is MILLER
Last name is BROWN, but only where first name begins with TO
Last name is JONES

Deconstructing this formula is reasonably simple. Building it can also be quite simple as long as the basic rules of formula syntax are followed. The most important thing to remember is actually the mathematical operators sequence.
The actual sequence these operators are performed in Excel is:
: (colon)
  (single space)
, (comma)
–
%
^
* and /
+ and –
&
= < > <= >= <>

Equally important is to use the correct function syntax. So in this case AND(logical1,logical2, ...) and OR(logical1,logical2,...) along with LEFT(text,num_chars) and OFFSET(reference,rows,cols,height,width) have been used.


Posted by: Jethro | 8:50 AM |