IF (+ video tutorial)

The IF function returns one result if a condition you specify evaluates to TRUE, and a different result if that condition evaluates to FALSE.

For example, the formula evaluating the cell A1with the new computer price can run like this: IF(A1>999,"Bloody rip-off","Fair price")

If the price is $999 or less, the result of the formula is Fair price, if the price tag is higher, the result of the formula will display Bloody rip-off

IF Functions can be embedded in another IF function, creating chained logical tests - searches, assiging multiple results based on different conditions: IF(IFS="fruits",IF(BUTS="nuts","CHRISTMAS"," ")," ")

In this case the formula evaluates one name - IFS and if its definition returns the word fruits, it keeps searching in the name BUTS for a word nuts. Only if both of these searches are positive, it returns the word CHRISTMAS, otherwise just an empty space.

Formulas used in this video:
"IF(B2<1000," A fair price.","A bloody rip-off!")"
"IF(IFS="fruits",IF(BUTS="nuts","CHRISTMAS"," ")," ")" 

Another example of a chained IF logical test is an European VAT table assiging based on the customers country (defined as the name CC):

IF(CC="Austria",20%,
    IF(CC="Belgium",21%,
     IF(CC="Bulgaria",20%,
       IF(CC="Cyprus",20%,
        IF(CC="Czech Republic",20%,
         IF(CC="Denmark",25%,
          IF(CC="Estonia",20%,
           IF(CC="Finland",23%,
            IF(CC="France",19.6%,
             IF(CC="Germany",19%,
              IF(CC="Greece",23%,
               IF(CC="Hungary",25%,
                IF(CC="Ireland",21%,
                 IF(CC="Italy",20%,
                  IF(CC="Latvia",22%,
                   IF(CC="Lithuania",21%,
                    IF(CC="Luxembourg",15%,
                     IF(CC="Malta",18%,
                      IF(CC="Netherlands",19%,
                       IF(CC="Poland",23%,
                        IF(CC="Portugal",23%,
                         IF(CC="Romania",24%,
                          IF(CC="Slovakia",20%,
                           IF(CC="Slovenia",20%,
                            IF(CC="Spain",18%,
                             IF(CC="Sweden",25%,
                               IF(CC="United Kingdom",20%,0%)
))))))))))))))))))))))))))

This function is a part of our Smart Invoice video tutorial: