Using IF Statements in Excel

excel-if-statementThe IF statement is a useful function of Microsoft Excel that can save a great deal of time spent on analysing and annotating data manually.

It’s a versatile, advanced, formula that can be combined with other formulae where needed. This is exactly the sort of thing we’ll teach you in our Advanced Excel course.

If this is the kind of advanced Excel tip you find useful, you might also want to have a look at our tutorials on the VLOOKUP and HLOOKUP functions of Excel and Conditional Formatting in Excel.

How to Use the IF Formula in Excel

The IF formula is built on a premise of auto-populating a field with the result of a true/false test.

For example, an employer may have a bonus structure where employees get a staggered bonus payment based on how much they sell in a month. If they make more than 60 sales, they get 5 times their sales in pounds in their pay packet! If they make less than 60 sales, they get 2 times their sales.

The formula looks like this: IF (logical_test, [value_if_true], [value_if_false]).

So, using the example above, we’d say =IF(b3<60, 5, 2)

Excel-IF-Formula1

You can then add another column to calculate their final bonus payment.

Excel-IF-Formula2

Alternatively, you could just put it all together in one formula – =IF(B3<60, B3*5, B3*2):

Excel-IF-Formula3

You can also put two conditions in your IF formula. For example, if you want to restrict the higher bonuses to those who had not been late to work more than twice in the month:

=IF(AND(B3>60,C3<3), 5*B3, 2*B3)

 

Excel-IF-Formula4

So there you have it, how to enter an IF formula/IF statement, and base it on more than one condition.

Tags

, , , , , , ,
Andy Trainer


Connect with Andy on Google.

View all posts by Andy Trainer

Leave a Comment