inls 461
Information Tools

Professor: Serena Fenton
School of Information and Library Science at UNC-Chapel Hill

Excel Advanced Formulas

Excel has a built-in set of advanced formulas and functions. To access them, click on the fx symbol to the left of the formula bar, or click on the pull down box next to the AutoSum Icon, or use the Insert Menu. Here are some commonly used functions:

Function Example Result
SUM                = SUM(C3:G9) Total of all numbers in the cells or range of cells
AVERAGE

  = AVERAGE(C3:G9)

Average of all numbers in the range
COUNTIF =COUNTIF(A3:F7, “>100”) # of cells in the range containing numbers greater than 100
TODAY() 

=TODAY()                          

 Current date.  Useful for forms that require a date.
ROUND = ROUND(G8,2) Contents of cell G8 are rounded to 2 digits
IF

=IF(logical_test,value_if_true,value_if_false)

 Calculates any value or expression that can be evaluated to TRUE or FALSE
VLOOKUP    
  Text & Logical Operations

You can also perform logical and text operations. The & operator connects two values to produce one text value. Comparison operators, which include =, <, >, >=, <=, and <>, compare two values and return the logical value TRUE or FALSE .

Formula

Result

=123&456

123456

="Highway"&64

Highway 64

="Carol"&" "&"Bernstein"

Carol Bernstein

=1=2

FALSE

=2>=1

TRUE
  Translating Common Error Messages

######

The number, or returned result is too long to fit. Make the column wider.

#DIV/0!

You're trying to divide by zero.

  • Change the divisor to a real number.>
  • If the divisor is a cell reference, check that the cell is not empty.

#NAME?

Formula contains a name that Excel doesn't recognize.

  • CHECK YOUR SPELLING!!
  • If you typed in a function, check its spelling or verify that such a function exists.
  • If you're performing operations on text, enclose it in double quotation marks.

#REF!

Cell reference is not valid.
If you deleted, pasted or moved cells used in the formula, you will get this error. You need to reconstruct your formula

#VALUE! The formula uses a wrong type of operand or argument.
Check to see that you have a numeric value in your cells (not text)

HINT: shortcut for solving #REF and #NAME errors is to click the cell that displays the error, click the button that appears Button image , and then click Trace Error if it appears.
 
Converting a Formula to a Value

If a cell contains a formula whose value will never change, you can convert the formula to that value. This speeds up large worksheet recalculations, and it frees up memory for your worksheet because values use much less memory than formulas do. For example, you might have formulas in part of your worksheet that use values from a previous fiscal year. Because these numbers aren't likely to change, you can safely convert the formulas to their values. To do this, follow these steps:

  • Select the cell containing the formula you want to convert.
  • Double-click the cell or press F2 to activate in-cell editing.
  • Press F9. The formula changes to its value.
  • Press Enter or click the Enter button. Excel changes the cell to the value.

 

revised May 26, 2006