Excel

Formulas and Functions

FormulasFormulas are entered in the worksheet cell and must begin with an equal sign "=". The formula then includes the addresses of the cells whose values will be manipulated with appropriate operands placed in between. After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible in the formula bar. See the example below to view the formula for calculating the sub total for a number of textbooks. The formula multiplies the quantity and price of each textbook and adds the subtotal for each book.

                                                                                                                      

There are four basic Mathematical Operators when writing a formula.  These operators are used to tell the formula what action to perform.  The following table lists the operators, its symbol.

 

The next table lists the order of operation for each mathematical operator.  As you begin to write your formulas, keep in mind that information in parenthesis ( ) is always performed first while everything outside the parenthesis is performed left to right.

Operator Operation Order of CalculationAND, OR, NOT Logic Test: AND, OR, NOT 1+ or - Positive or Negative Value 2^ Exponentiation 3* or / Multiplication or Division 4+ - Addition or Subtraction 5& Text Concatenation 6

Logic Test      7= Equal to  7<> Not Equal To 7<= Less than or Equal to 7>= Greater than or Equal to 7

Operation Symbol

Symbol Name

Addition + Plus Sign

Subtraction - Dash or hyphen

Multiplication * Asterisk

Division / Forward slash

Formula Operators

Functions

• Built-in Excel Functions can be a faster way of doing mathematical operations than formulas.

• Example- if you wanted to add the values of cells D1 through D10, you could type the formula "=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10".

 • A shorter way would be to use the SUM function and simply type

"=SUM(D1:D10)".

Function Example Description

SUM =SUM(A1:A100) finds the sum of cells A1 through A100

AVERAGE =AVERAGE(B1:B10) finds the average of cells B1 through B10

MAX =MAX(C1:C100) returns the highest number from cells C1 through C100

MIN =MIN(D1:D100) returns the lowest number from cells D1 through D100

SQRT =SQRT(D10) finds the square root of the value in cell D10

TODAY =TODAY() returns the current date (leave the parentheses empty)

SUM( ) function

The SUM( ) function is probably the most common function in Excel. It adds a range of numbers. To build a SUM( ) function, begin by typing the = sign; all functions begin with the = sign. Next type the word SUM followed by an open parenthesis. You must now tell Excel which cells to sum. Using the mouse, click and drag over the range of cells you wish to add. A dotted outline will appear around the cells and the cell range will be displayed in the formula bar. When you have the correct cells selected, release the mouse button, type a closing parenthesis and press the <Enter> key.

If you do not want to use the mouse, type in the references of the cells you want to sum. For example, to add cells B3 through B5, type =SUM(B3:B5). Excel interprets B3:B5 as the range of cells from B3 to B5.

Click the Insert Function button on the formula bar.

The Insert Function dialog box opens

In the Search for a function box, type a description of what you want to do.

Excel has hundreds of prewritten formulas which make it easy to do complex procedures with numbers, dates, times, text, and more.

Insert Function

•Type a brief description of what you want to do in the Search for a function box. In this example, you could type "mortgage payment" or some other keywords.

•Click Go.

Tips•You can also select a function category in the Or select a category box. This action will display a list of related functions, which you can then browse through.

•If you'd like help on how to enter the arguments, you could type the function name in the Search for a function box and click OK.

AutoSum

AutoSum button

In Excel, the editing group on the home tab has a button that simplifies adding a column or row of numbers. The AutoSum button, which resembles the Greek letter Sigma (shown above), automatically creates a SUM( ) function. When you click the AutoSum button Excel creates a sum function for the column of numbers directly above or the row of numbers to the left. Excel pastes the SUM( ) function and the range to sum into the formula bar. If the range is not correct, simply select the proper range with your mouse on the worksheet. When you have the correct range entered, press the <Enter> key to complete the function.

Autofilling Functions

Autofill can also be used to copy functions.  In the example below, column A and column B each contain lists of numbers and column C contains the sums of columns A and B for each row. The function in cell C2 would be "=SUM(A2:B2)". This function can then be copied to the remaining cells of column C by activating cell C2 and dragging the handle down to fill in the remaining cells. The autofill feature will automatically update the row numbers as shown below if the cells are reference relatively

                                                                               

Cell Reference

There are two basic types of cell references in Excel: relative and absolute. The difference between absolute and relative cell references becomes apparent when you copy formulas from one cell to another. When you copy a formula containing relative references, the references are adjusted to reflect the new location. Absolute references always refer to the same cell, regardless of where the formula is copied. Relative references are the default.

To create an absolute reference, type $ before each part of the cell address.

Relative / Absolute

This shows the formulas used to create the order form below.

We used the fill handle which usually gives us the relative reference.

For the sales tax calculation we needed to use the absolute reference in cell C9

Relative Absolute

To toggle between seeing the formulas and seeing the results, hold down the Ctrl key and press the tilde ~