The IF function (also more commonly called IF statements) work like this: IF, then, else. Basically, that means if a condition is true, then do one thing, else/otherwise do something else. For example, if the puppy is a Labrador, then buy a blue collar, otherwise/else, buy a red collar.

The syntax (the way the commands are organized in the formula) of the IF statement is: =IF(logic_test, value_if true, value_if_false). IF statements are used in all programming languages and, although the syntax may vary slightly, this function provides the same results.

1. Enter the following column headers: Cookie Boxes Sold; 3rd Prize =More than 500 Sold, Less than 1000; 2nd Prize =More than 1000 Sold, Less than 1500; 1st Prize =More than 1500 Sold, Less than 2000; Grand Prize =More than 2000 Sold

2. Enter some numbers into column A4:A13. Mix it up so you get data in all of the Sold columns.

3. Enter this formula in B4: =IF($A4>500, $A4, 0).

NOTE the $ sign before the column letter ‘A’ in the above formula. Place your cursor on the first ‘A’ in the formula, then use the function key F4 to cycle through the Absolute and Relative References. Stop when the $ sign precedes the ‘A’ (for each A in the formula). This tells Excel NOT to change the column letter, but only change the row numbers when this formula is copied. If you put a dollar sign before both the column letter and the row number, neither would change.

4. Copy the formula in B4 to C4, D4, and E4, then edit as follows: C4 =IF($A4>1000, $A4, 0); D4 =IF($A4>1500, $A4, 0); and E4 =IF($A4>2000, $A4, 0). Then copy down.

5. The formula works, but you have to review each column to see who won the prizes, because each column shows ALL the values greater than the amount in the formula. That’s ok for a small spreadsheet, but not for anything larger than a single screen.

6. We need a Nested IF statement for this one. Repeat numbers 1, 2, and 3 above beginning on row 20; but instead of the formula in 3 above, enter this formula in B20: =IF(AND($A20>500,$A20<1000),$A20,0).

7. Repeat number 4 above, but edit the formulas like this: C20 = =IF(AND($A20>1000,$A20<1500),$A20,0); D20 = =IF(AND($A20>1500,$A20<2000),$A20,0); and E20 = =IF($A20>2000, $A20, 0). Yes, this last one is different because there is no “less than” amount. Then copy down. Now you can look at each column and determine immediately who the winner is for that category.