Formulas and Functions

Excel, like all spreadsheet programs, is designed to allow the user to perform calculations on large sets of data. The use of user-defined formulas and built-in functions accomplish these calculations.

Operators

As in any equation, operators are needed to define the desired action. Excel asserts the algebraic order of operations on all formulas. The basic operators needed for any equations include:

• Equality (=) is required of all formulas. Formula cells begin with equality, setting the cell equal to the given expression.
• Parentheses (()) will contain an operation and calculate the value before the remaining expression.
• Exponential (^) will raise the first number to the power of the second.
• Multiplication (*) will calculate the product of two values or cells.
• Division (/) will calculate the fraction of two values or cells.
• Addition (+) will calculate the sum of two values or cells.
• Subtraction (-) will calculate the difference of two values or cells.

Formulas

Entering a formula is simple. To begin, select the active cell you wish to place your formula.

example of a selected cell	begin each formula with an equality

The formula must begin with an equality. After that, insert your expression and press enter. In the example below, the cell A3 has been set equal to the product of 7 and 135. Pressing enter presents the value of 945.

insert your formula	press enter to finish

Cell Referencing

Entering numerical expressions is useful, but is a function easily accomplished with a common calculator. The real power of a spreadsheet lies in its ability to take data from a cell, or range of cells, and use it for calculations. Referencing cells and ranges is a powerful tool in accomplishing this goal.

Each cell in the spreadsheet has a reference, given by its column and row position. In the Formulas section above, the eventual value, 945, is located in cell A3. A3 is the reference for the value 945 on that spreadsheet. For further calculations, instead of continuously using the original value, A3 can be substituted in its place. This is the true power of a spreasheet.

For an example, repeat the above example using cell referencing. Insert the intial values; 7 in cell A1 and 135 in cell A2.

Now, in cell A3, create the needed formula to find the product of these two numbers. Instead of: =7*135 substitute the values with their references: =A1*A2 and notice the similar product.

At this point, any of the values in A1, A2, or A3 can be referenced and used in further calculations. Your spreadsheets can be programmed to continuously build upon the values you create.

insert your formula with references	press enter to finish

It is important to understand that when referencing, the reference points to the cell and not the value inside. A1 tells Excel to look at A1 and pull whatever contents it holds. A1 does not tell Calc to look for the value 7. Expanding on this idea, change the value of A2 to 123.

The value of A3 has changed to 861, but the formula of the cell is still A1*A2. This shows that changing the value of a cell changes the values of any cell that references it.

Autofill and Referencing

The autofill feature can be used to mass copy a formula. However, autofill works slightly differently when using referencing. When autofill is used with a reference, each cell in the autofill range will reference the cell adjacent to the previous reference.

An example will make this concept easier to understand. Enter a value in the A1 cell. In the cell below it, enter the formula: =A1+1

insert a value into A1	set A2 to =A1+1

 

Now, use the autofill feature and drag the formula down a few cells. Select cell A3. Notice how its contains the formula: =A2+1

A4 will reference A3, and so down the range. Autofill will follow the same task horizontally. If your range went from A2 to D2, then B2 would reference B1, C2 would reference C1, etc..

This capability is great if you wish to perform calculations on a range of data, but is limiting if you want to perform the same calcuation on a constant value. If autofill wanted to only reference A1, and not adjacent cells, the original formula must be modified. To signify a cell as constant, place a $ between the column and row reference.

=A$1+1

Autofill again, and select A3. Notice how A3 is now referencing A1.

Cell Addressing

In the section above, the idea of setting constant values using the $ symbol was discussed. This practice is useful, but can be cumbersome when a spreadsheet uses many constants or calls certain constants often. Excel includes the capability to name or address a cell. For example, instead of having to constantly reference A$10, the spreadsheet can now look for a cell named monthly_income.

To address a cell, select the desired location and right-click the cell to select Name a Range.

Within the pane, enter a name for your cell. You cannot use any name that contains a space, or is a reserved function name. Once you have entered a name, press OK.

Now, within your spreadsheet, you can reference your constant cell by its name. The autofill function will always reference the name of your cell if it is provided in the original formula.

Functions

Aside simple addition and subtraction, Excel is capable of performing a number of built in functions on a range of cells. To access these functions, use the Functions button.

Referencing cells was discussed in an earlier section above. Once topic that wasn't discussed was referencing a range of cells. To reference a range of cells, choose your starting cell and your ending cell.

A1:A4 - this continuous range includes cells A1, A2, A3, and A4.

A1:B3 - this continuous range includes cells A1, A2, A3, B1, B2, and B3

A1:A3,C1:C3 - this noncontinuous range includes cells A1, A2, A3, C1, C2, and C3

• sum(range) finds the sum of a range of values
• average(range) finds the average of a range of values
• min(range) finds the smallest value in a range
• max(range) finds the largest value in a range
• count(range) counts the number of cells in a range