Continuing  our data manipulation, these further examples can be applied to a cell to extract all kinds of data, and when used together can be particularly powerful when combined with the FIND command.

Here have a quick look at the power of logic statements, and again they can be combined with everything we have done previously.

There is much to do with Excel data manipulation, and I have only really scratched the surface, the next series may well look at the various different methods of counting Excel offers, combined with Array formula and a little logic for good measure 🙂

 

How do I test a value in Excel to see if it meets certain or multiple criteria?

Logic staments:

=IF(text=condition, condition true, condition false)

 To simplify: “If”, “Then”, “Else”.

 Example:

In this example the formula (or logic statement) in A2 is looking for ‘Pie’ in cell A1.  As it is clearly not a pie (rather a tubular food product) THEN (or condition TRUE) is skipped over and ELSE (or condition FALSE) is invoked telling us the contents of A1 is “Definately not a Pie L”.

 

=OR(condition, condition, condition…)

 Can be used in IF statements as part of a condition, where the answer becomes true if ANY of the conditions met are true.

 Example: 

 In the above example, the IF statement is used along with the OR statement in the initial logic test.  Effectively the combined IF & OR statements check to see if ‘Pies’ OR ‘Peas’ are in cells A1 OR A2 respectively.  IF one or other are then the formula returns ‘There is something you like on the menu’ as is the case here!

   

=AND(condition, condition, condition…)

Can be used in IF statements as part of a condition, where the answer becomes true if ALL of the conditions met are true.

Example:

In the above example, the IF statement is used along with the AND statement in the initial logic test.  Effectively the combined IF & AND statements check to see if ‘Pies’ AND ‘Peas’ are in cells A1 AND A2 respectively.  IF they are then the formula returns ‘Perfect match’ as is the case here!

 

Can I nest IF, OR, AND statements or are they mutually exclusive?

During Christmas cover, I was IM’d asking this very question, the person in question was fairly Excel literate my response was:

=IF(OR(AND(1=x,2=x),AND(3=x,4=x)),THEN,ELSE)

The penny immediately dropped 🙂  Just think logically and carefully about the conditions you are setting out to achieve.  You can nest most if not all Excel formula 🙂

 

Can I turn an output error from a formula into a meaningful value?

=ISERROR(value)

Yes you can :).  Used primarily as part of a logic statement with a nested IF.  Instead of an error producing a #VALUE error, the error can be specified, for example “” to leave the cell blank.

Example 1 – a formula creating in error:

In this example we can see a lookup attempting to return the number of snacks sold, in this case the Value in A8.  Clearly the specified table above has no ‘Crisps’ so the not so useful error code of #N/A is produced.

Example 2 – the use of iserror (in conjunction with a logic statement).

With this example, you can see the previous formula in use along with a logic condition.  In laymen’s terms, IF there IS(an)ERROR doing the LOOKUP then tell me that there is ‘No such Item found’ else do the LOOKUP again, and give me the result.

This way you have more control over your errors, and for example, you can effectively nest IF statements and not be tripped up if part of the formula was to produce an error.  This way, excel is no longer going to produce an uncontrolled error, you are fully in charge.