Excel’s IF operator allows you to perform different operations on spreadsheet cells depending on whether conditions in other cells are logically true or false. The operator can only compare two values, but the user can nest more than one IF in a calculation to carry out more complex steps. The usual mathematical comparison symbols are used: =, <, <=, >, >=, <>. The function can be used to write specific values or text to cells or even carry out calculations. A hypothetical example in the steps illustrates the utility of this capability.
Create a simple spreadsheet to track daily cell phone use for two individuals. Head the first column “A” for employee A and the second column “B” for employee B. Make a heading for column C reading “Warning,” a heading for column D labeled “A Reimbursement.” Format this column as currency. Label column E as “Problem.” A similar column for B’s reimbursement could be made.
Video of the Day
Enter random numbers of minutes in each column starting with cell A2 and B2 for 10 days. Make one of the A column cells equal to zero.
Create a logical test in cell C2 reading “=IF(A3>(B3+10), “To big a spread”, “Acceptable”). Copy this formula down to the next nine cells below. If Employee A used a cell phone 10 minutes longer than Employee B, the trusted one, this day will be flagged for checking. If not, the day is labeled "Acceptable." In real practice, more daily values can be entered and the formula repeated, saving manual arithmetic comparisons from one column to another.
Make a new logical test in cell D2. Enter the formula “=IF(A2<(B2+10),A2_.03,A2_(.03-.0015)).” Copy this formula down to the next nine cells in this column. This creates a distinction between whether Employee A stays within the acceptable difference from Employee B, in which case the first employee is reimbursed at $.03 per minute, otherwise an adjustment of 1.5 cents is made.
Put a last test in cell E2 reading “=IF(A2<1),IF(B2>=1,”Where was A today?”)).Copy this formula down to the next nine cells below. The result is if A did not use a cell phone on a day, but B did, flag this with a question. If neither used a cell phone, perhaps it was a holiday. Otherwise don’t do anything, in which case the formula will enter the word “FALSE.” This is by default, which is why the formula doesn’t need to contain a second result.
Do not use commas within values or the formula will not operate as expected. Enter four or more digit numbers such as 1,280 as 1280. Pay strict attention to the required number of parentheses and group tests that involve more than one cell or value within parentheses, as in Step 3.