Excel Tips

IF STATEMENTS

 

IF Statements are a very common formulation used in MS Excel.  The formulas can be very powerful and are relatively simple to construct.

They are constructed as follows:

=IF(Logical_Test,[Value if True],[Value if False])

Logical Test - This is the condition you type or want to test - A1=B1 for example

Value if True - The result you want if the Logical Test above is true. i.e. A1=B1

Value if False - The result you want if the Logical Test above is false or untrue. i.e. A1 does not Equal B1

 

Example

 

=IF(A1=B1,A1,B1)

 

The example above looks at cells A1 and B1 and compares them.

If A1 = B1 then A1 is returned as the result.

If A1 does not equal B1 then B1 is returned as the result.

 

Combining IF Statements

 

More usefully, once the basic IF statement has been mastered, it is easy to create more complex and specific formulas by combining or nesting the formulas.

 

For example, in the above example, you might want A1 to be the result if the Logical Test is True, but you might want to perform a further test if it is not.

 

The easiest way to construct these combined or nested IF statements is to write them out separately and then combine them.

 

Therefore:

 

=IF(A1=B1,A1,B1) - As Before

 

=IF(B1=C1,A1,B1) - Here we are checking the Logical Test does B1 = C1.

 

We can therefore combine the formulas by putting the whole of the second IF statement within the first IF statement.

 

=IF(A1=B1,A1,IF(B1=C1,A1,B1))

 

We can see that we have simply replaced the Value if False with the new IF statement.

NOTE: We have to return the last bracket and ensure that the whole second IF statement, (Excluding the initial Equals sign), is put in the formula where there was once B1.

 

Thus, with the nested formula:

 

A1=B1 Answer is A1

A1 does not equal B1, but B1 equals C1 - Answer is A1

A1 does not equal B1, and B1 does not equal C1 -  Answer is B1

 

You can continue to nest formulas up to a very high level.

 

NOTE:  It is possible to nest other formulas within an IF statement.

 

Again the simplest solution is to write the formulas out all separately and then go about combining them, by copying the individual formula into another formula.

 

With a bit of practice, you can soon end up with very complex formulas:

 

=-IF(AND(Mode="Actual",DEMO="D"),IF(ISERROR((SUM(AP$7:AP$8)/AP$99)*(AP$99-AP$107)),0,IF(AP$99>AP$107,(SUM(AP$7:AP$8)/AP$99)*(AP$99-AP$107),(SUM(AP$7:AP$8))*(AP$99-AP$107))),IF(Mode="PROPOSAL",IF(ISERROR((SUM(AP$7:AP$8)/AP$99)*(AP$99-$AQ$89)),0,(SUM(AP$7:AP$8)/AP$99)*(AP$99-$AQ$89)),-IF(ISERROR((SUM(AP$7:AP$8)/AP$99)*(AP$99-AP$89)),0,IF(AP$68>$AW$89,(SUM(AP$7:AP$8)/AP$68)*(AP$68-$AW$89),(SUM(AP$7:AP$8))*(AP$68-$AW$89)))))

 

This formula is no where near as complex as it looks.  Hence the biggest tip here is to write the components of the formula out separately and then combine them.  Save the worksheet regularly or make a backup before you start.  That way if it gets out of hand and you lose your way, you won’t do any permanent damage.

 

 

 

Got an issue with a Formula or an Excel query?  Email us and we can help!  excel@southcoastaccountants.com

We will make a charge, minimum of £5, but we would let you know how much it would cost before starting any work.  We can usually turn around in less than 1 working day.