Functions, named ranges and IF

A function is a predefined formula already built into Excel, so you need not type long formulas by hand. Instead of =A1+A2+A3 you write =SUM(A1:A3). Every function follows the same syntax: =, the function name, an opening bracket, the arguments separated by commas, a closing bracket: =FunctionName(arguments). Know each function's name, purpose, arguments and result.

Ways to put a function in a cell:

• Type it in the cell or the formula bar, starting with =.
• Insert Function (the fx button) lists every function and helps fill the arguments.
• On the Formulas tab, pick a group (Financial, Logical, Text...) and choose from its list.
• AutoSum adds up a range in one click.
• Recently Used brings back a function you used before.

The common ones: SUM adds, AVERAGE averages, COUNT counts numbers, MIN finds the smallest, MAX the largest. For four marks of 60, 74, 82 and 70 in E2:E5, =MIN(E2:E5) gives 60 and =MAX(E2:E5) gives 82.

Three more tools:

• A named range (Formulas > Define Name) puts a word in place of a range address: =SUM(Numbers) instead of =SUM(B1:F1). SUMIF adds only the cells that meet a condition.
• Paste Special > Values freezes a formula's current result and removes the formula.
• IF(logical_test, value_if_true, value_if_false) asks a yes/no question using the logical operators =, <, >, <=, >= and <> (not equal). =IF(A1<20,"Yes","No") shows Yes when A1 is below 20, otherwise No.

For more than two answers, nest IFs, testing one condition after another: =IF(E2<96, IF(E2<91, IF(E2<55,"Fail","C Grade"),"B Grade"),"A Grade"). A nested function must return the kind of value its argument expects, or Excel shows #VALUE!.