Latest posts by techwriter (see all)
- English Grammar – How to Use LIE and LAY Correctly - October 26, 2016
- How to Count the Number of Days with an Incident and Chart with Running Averages in MS Excel - October 19, 2016
- FREE Online Video Course – MS Word Power Shortcuts - October 14, 2016
IF is one of the basic functions in Microsoft Excel that can be used to evaluate a logical test, and then result a positive or negative result. If this sounds complicated, it’s not – you can use simple IF statements for tasks such as showing a warning when inventory is low or calculating a bonus if sales are over a certain number.
The function looks like this:
=IF (logical test, value if true, value if false)
While it’s certainly possible to do a static test such as….
=IF (1=1, “One is one”, “One is not one”)
… it’s generally more useful to use other cells as the input for your test:
=IF (A1>10000, “Monthly sales exceed target”, “Target not met”)
=IF (TODAY()=DATE(2010,12,25),”Happy Christmas”,””)
Every time A1 changes, the IF statement is re-evaluated. It’s also possible to nest IF statements, meaning that an additional IF statement is tested depending on the result of the first IF statement. For example:
=IF (A1>10000, IF( A1>50000, “$1000 bonus”,”$100 bonus”), “No bonus)
This means that there’s a $1000 bonus is A1 is greater than 50,000, a $100 bonus if it’s greater than 10,000 and less than 50,000, and none for under 10,000.
While nesting can be useful, and up to 64 IF statements can be nested inside each other, be careful not to make the formula too unwieldy. It’s easier for troubleshooting purposes to keep formulas as short as possible, and split across multiple cells if necessary. If you have many conditions to test, you may find that LOOKUP, VLOOKUP or CHOOSE functions are more appropriate.
IF is one of the fundamental functions in spreadsheet applications and works identically in Google Apps and Zoho Office.
James Beswick owns the online marketing consultancy One Uproar ( http://oneuproar.com ) and is the author of two books “Ranking #1” and “Getting Productive With Google Apps”.