excel 2007 calculating with advanced formulas

8/12/2019 Excel 2007 Calculating With Advanced Formulas

1/15

Excel 2007

Calculating with Advanced Formulas

Michelle Loker

3/24/2010


2/15

Table of Contents

Absolute Cell Reference .......................................................................................................................................... 1

Naming a Cell or Range of Cells............................................................................................................................... 1

Using Names in Formulas .................................................................................................................................... 2

Finding a Named Cell or Range ........................................................................................................................... 2

Editing a Named Cell or Range ............................................................................................................................ 2

Formula Structure ................................................................................................................................................... 3

How to write a formula using a function ............................................................................................................ 3

Individual Arguments ...................................................................................................................................... 3

Through Arguments ........................................................................................................................................ 3

Calculating Across Worksheets ............................................................................................................................... 3

Referencing Multiple Worksheets ...................................................................................................................... 3

Sheet Separators ................................................................................................................................................. 4

3-D Cell References ............................................................................................................................................. 4

Using formulas for sheets in consecutive order: ............................................................................................ 4

Using formulas for specific sheets .................................................................................................................. 5

Functions ................................................................................................................................................................. 5

Function Categories ............................................................................................................................................ 6

Calculating with Date and Time Function ........................................................................................................... 7

Today Function ................................................................................................................................................ 7

Now Function .................................................................................................................................................. 7

Calculating with Financial Functions ................................................................................................................... 7

PMT Function .................................................................................................................................................. 7

Calculating with Math and Trig Functions .......................................................................................................... 8

Sum Function................................................................................................................................................... 8

SumIf Function ................................................................................................................................................ 9

Calculating with Statistical Functions ................................................................................................................ 10

Average ......................................................................................................................................................... 10

Count ............................................................................................................................................................. 10

CountA ........................................................................................................................................................... 10

Calculating with Look-up and Reference Functions .......................................................................................... 11

Using the Vlookup function: .......................................................................................................................... 11

Calculating with Logical Functions .................................................................................................................... 13


3/15

Page 1

Absolute Cell ReferenceAbsolute cell references can be very important when copying and pasting formulas. By default, when

you copy and paste a formula it will use a relative cell reference. When the formula is pasted it will

automatically change to a cell range, within the range of where the formula is being pasted. In some

cases that is fine, but not when you want to reference the same cell range in the original formula.

Absolute cell references need to be used if you want to reference the same cell in a copied formula.

To reference an absolute cell put a dollar sign ($) in front of the reference that should be absolute. Cell

references can be a mix of absolute and relative.

Example of cell reference combinations, using cell C2:

1. $C$2 This is an example of an absolute cell reference that would always reference the value incell C2.

2. $C2 This is an example using absolute and relative cell references. This cell reference wouldalways reference column C, but the cell row would change.

3. C$2 This is an example using absolute and relative cell references. This cell reference wouldalways reference row 2, but the column would change.

4. C2 This is an example of a relative cell reference that would change when the formula iscopied. The cells would change in reference to where the formula is pasted.

Note: The F4 key can be used to toggle between the cell reference options, or the dollar sign can be

typed in front of the cells that should be absolute cell references. To use the F4 option, click in the

formula next to the cell reference and push the F4 key to toggle through the above cell reference

options.

Naming a Cell or Range of CellsA named cell or range of cells can be used in formula references. Naming a cell or range of cells is a

good time saver when writing formulas that are calculating between multiple sheets. Named cells or

ranges can also be used in place of absolute cell references.

1. Select the cell or range of cellsto be named

2. To the left of the formula bar isthe name box, as shown in the

picture to the right. By

default, the name box will

reference a cell that is selected.

3. Click in the name box4. Type in a name for the selected cell or range of cells5. Press the enter key


4/15

Page 2

Note: If a cell or range of cells has been named, the cell or range will need to be selected for the name

to appear in the name box.

Using Names in Formulas

Names can be used in formulas by referencing the name instead of the cell reference.

For example: I want to use a formula that figures a discount of 5%. Cell B4 has the value of 5%. I have

named the cell Five. If I wanted

to multiple a cell by the value in

B4 (5%), I can simply reference the

cell name no matter where I am in

the workbook.

Example of formula with cell

name: = G8*Five, this would take

the value in cell G8 and multiply it

by the cell named Five, which is 5%.

Note: When using more advanced formulas it is much easier to reference a named cell or range of cells

than having to select a whole table.

Finding a Named Cell or Range

By using the name box it is easy to find a named cell or range of cells.

1. Click the drop-down arrow next to the name boxNote: The name box will reference the cell that is currently selected.

2. The drop-down menu will show a list of named cells or ranges3. Select the name. The cursor will select that cell or range of cells

Editing a Named Cell or Range

A named cell or range of cells can be changed by using the name manager.

1. Select the formulas tab2. Under the defined namesgroup, select the name managerbutton3. Select the name to be changed4. Click the edit button to change the name or cell range

Note: A new named range can also be created by using the

Newbutton in the name manager window. The filter button canbe used to search specific areas for defined names.

5. Click closewhen finished with the name manager dialog box


5/15

Page 3

Formula StructureTo write complex formulas you will need to know basic formula structure. There are multiple ways to

write formulas. Choose the best one that fits your needs for each scenario.

Formulas are made up of three basic parts, the equals sign, a function or operator, and arguments.

There are two main ways to write Excel formulas, with an operator or with a function. Functions can be

used as a short cut to create commonly used formulas.

Lastly are the arguments. Arguments are the numbers or cell references that will be used to figure a

formula.

Example using operator: =B1+B2+B3+B4+B5+B6+B7. The operator in this formula is the + sign.

Example using function: =SUM(B1:B7). The function in this formula is SUM (which is for adding values).

Formulas using functions have a different format than formulas using operators. Formulas that use

function also start with an equals sign, but instead of a mixture of arguments and operators, the

function is entered right after the equal sign with the arguments following in parentheses.

How to write a formula using a function

Individual Arguments

Individual arguments can be used to reference specific cells within a spreadsheet. Commas can be used

to separate individual arguments.

Example of individual cell arguments

= Function (Argument1, Argument 3). This example would only use the twospecific arguments

referenced.

Through Arguments

A colon can be used to reference arguments that are in consecutive order.

Example of consecutive cell arguments

= Function(Argument 1:Argument 3). This example would use arguments 1 through3.

Calculating Across Worksheets

Calculating across worksheets will allow you to pull values from multiple worksheets to create a desiredformula. When calculating across worksheets you will need to reference sheet names. The symbol to

reference a sheet is an exclamation point (!). The exclamation point will need to be added to the end of

the sheet name.

Referencing Multiple Worksheets

When referencing a different sheet than the one the formula will appear on, the formula will need to

include an exclamation point after the sheet name. For example I have a worksheet with the name


6/15

Page 4

Library, to reference that sheet name I would type Library! within the parenthesis of the argument area.

The cell reference will follow the sheet name.

Sheet Separators

If you will be referencing sheets that are in a consecutive row, use the symbol for through, which is a

colon (:).

If you will be referencing only specific sheets use a comma(,) between the sheet names.

For example, I have a workbook that has a separate expense worksheet for each department. Each

worksheet is named by department (Library, IT, Reeve). I want to keep a summary sheet of the total

expanses for all departments. I added a separate sheet called Totals, for the total of all department

expanses. I will need to calculate across worksheets to determine those totals. There are multiple ways

this can be done.

3-D Cell References

Three-dimensional cell references are used when referencing the same cell across multiple worksheets.

An example of a 3-dimentional reference is shown below.

Using formulas for sheets in consecutive order:

The example below shows how to create a formula with consecutive worksheets.

Example formula to calculate between consecutive worksheets: =SUM(Library:Reeve!B9)

Example typing out a formula using a function:

1. Click in the cell where the formulawill be entered

2. Type an equal sign3. Type in the function to be used. In

the example to the right, the SUM

function was typed.

4. Type a left parenthesis5. Click on the first sheet that should

be added, by using the sheet tabs at

the bottom of the workbook. You

can reference the formula bar to seethat the formula is being built.

6. Select the cell(s) that should becalculated in the formula. In the example below, cell B9 was selected.

Hold down the shift key, and then select the last worksheet that should be included in the

formula.


7/15

Page 5

Note: All worksheets between the first and last sheet selected will be included in the formula.

The example formula uses a colon to sum the Library worksheet throughthe Reeve worksheet

and uses a cell reference of !B9.

7. Type a right parenthesis8. Push the enter key for the formula results to appear

Using formulas for specific sheets

Specific sheets can be included in the formula by using a comma instead of the colon.

1. Click in the cell where the formula will be entered2. Type an equal sign3. Type in the function to be used.4. Type a left parenthesis5. Click on the first sheet that should be included in the formula, using the sheet tabs at the

bottom of the workbook. You can reference the formula bar to see that the formula is being

built.

6. Select the cell(s) that should be calculated in the formula.7. Type in a comma between each cell reference8. Select the next sheet name9. Select the next cell10.Proceed with step 7 9 as needed11.When finished push the enter key to complete the formula and see the results

Functions

There are many functions in Excel that help with writing advanced formulas.

To explore the different functions choose one of two methods.

Method 1:

1. Select the formulastab2. Select the insert functionbutton3. The function dialog box will open4. If you know the function name, type it in the search for

a function box and select the Go button

5. If you do not know the function name select a categoryfrom the drop-down list. By default the most recently

used functions will appear. A list of functions

pertaining to the selected category will appear below,

under select a function.

6. Click on a function to see a brief description


8/15


9/15

Page 7

Calculating with Date and Time Function

Calculating with date and time can be complicated in Excel. Excel store dates and times as serial values,

which can make it difficult when it comes time to perform calculations. The number tab, in the format

cells dialog box can be used to choose from multiple date and time formats.

Today FunctionThe today function can be used to insert the current date.

To use the today function:

1. Click in the cell where the function will be entered2. Type =TODAY()

Note: There are not any arguments for the today function.

3. Push the enter key for the formula results to appearNote: If the formula does not appear as you would like, change the formatting to a different

date and time format.

Now Function

The now function can be used to insert the current date and time.

To use the now function:

1. Click in the cell where the function will be entered2. Type =Now()

Note: There are not any arguments for the now function.

3. Push the enter key for the formula results to appearNote: If the formula does not appear as you would like, change the formatting to a different

date and time format.

Updating the Now Function

The now function does not update automatically. The formula will only update when the worksheet is

opened or printed.

1. Click in the cell that contains the function to be updated2. Select the F9 key on the keyboard

Calculating with Financial Functions

Financial functions can be used to perform business or accounting calculations.

PMT Function

The payment function can be used to figure a payment based on three components: interest, number of

payments, and cost of item. Each component should be inserted in its own individual cell.

Figuring payment:

1. Select the cell that will contain the formula (in the example it is cell B9)


10/15


11/15

Page 9

Note: For a series of cells the colon is used.

Sum a series of cells:

1. Click in the cell were the total should appear2. Select the home tab, under the editing group select the AutoSum button3. If there are numbers next to where the formula is being built, Excel will automatically select

those values

4. If those are not the values that should be added, simply select the range of values you want5. Push the enter key on the keyboard for the formula results to appear

SumIf Function

The SumIf function can be used to sum certain

cells based on meeting a condition within the

spreadsheet. In the example sheet above there

are work study students indicated by a WS and

student assistant students indicated by a SA. Cell

G29 will represent the total paid if they are a

work study student. Cell G30 will represent the

total paid if they are a student assistant.

Using the SumIf function:

1. Click in the cell were the total should appear2. Select the formula button3. In the search for function box type SumIf4. Click Go5. Select SumIf6. Click OK7. When using the SumIf function there are three arguments that have to be filled in Note: Arguments that need to be filled in will be in bold.

Range: What cells should be evaluated. (In the example the range to be evaluated is B4:B27,

which includes the values for work study (WS) or student assistant (SA).

Criteria: What criteria should the range chosen meet. (In the example if we are building theformula for cell G29 we would like to add the values if they are a work study student and if we

are building the formula for cell G30 we would like to add the values if they are a student

assistant).

Sum_range: What range of cells should be added for the meet criterion. (In the example we

would like to sum the range from G4:G27)


12/15

Page

10

8. Select the range, criteria and sum_range by using the red arrow to the right of the field name.Note: The red arrow next to the field name will allow you to toggle between the worksheet and

the function argument dialog box.

9. Select OK when all arguments have been entered.Calculating with Statistical FunctionsStatistical functions can be used to perform analysis on a series of cells.

Average

The average function can be used to figure the average of a series of numbers.

Using the Average function:

1. Click in the cell were the average should appear2. Select the hometab3. Select the drop-down arrow next to the AutoSum button4. Select average5. If there are numbers next to where the formula is being built, Excel will automatically select

those values

6. If those are not the values that should be averaged, simply select the range of values that should7. Push the enter key on the keyboard for the formula results to appear

Count

The count function can be used to count a range of cells with numberedvalues.

Using the Count function:

1. Click in the cell were the total count should appear2. Select the formula button3. In the search for function box type count4. Click Go5. Select count6. Click OK7. Select the red arrow next to the Value1 field to toggle to the spreadsheet8. Select the cells that should be counted9. Select the red arrow to toggle to the arguments dialog box10.Select OK when all arguments have been entered

CountA

The counta function can be used to count a range of cells with textvalues.

Using the CountA function:

1. Click in the cell were the total count should appear2. Select the formula button


13/15

Page

11

3. In the search for function box type counta4. Click Go5. Select counta6. Select the red arrow next to the Value1 field to toggle to the spreadsheet7. Select the cells that should be counted8. Select the red arrow to toggle to the arguments dialog box9. Select OK when all arguments have been entered

Calculating with Look-up and Reference Functions

Lookup references can be used to create formulas that pull information from a part of one worksheet

and will fill in that value in another part of a second worksheet.

Two types of commonly used lookup references are, Vlookup and Hlookup:

VLookup:Will look for a table in vertical format or columns. The index value being referenced must be

in the first columnand sorted in ascending order.

HLookup: Will look for a table in horizontal format or rows. The index value being referenced must be

in the first row and sorted in ascending order.

The most important thing to remember when using a lookup reference is to sort the column or row

containing the lookup value in ascending order

Using the Vlookup function:

Spreadsheet Using Vlookup Function (06-07 worksheet):


14/15

Page

12

Vlookup Source Example (Vlookup worksheet):

Using the Vlookup function:

In the example 06/07 worksheet above we will be using the JobID to fill in the Title, Location, Supervisor

and UDDS. All of the information needed for the 06/07 sheet is located on the Vlookup sheet above.

The Vlookup sheet will be used to pull the needed information.

1. Click in the cell were the formula should appear2. Select the formula button3. In the search for function box type Vlookup4. Select Go5. Select Vlookup in the list and click OK6. There will be three argument fields that need to be filled in for the Vlookup function

Lookup_value: The value to be looked-up. Note: The value being looked up must also be in the

first column of the lookup table (table_array)and sorted in ascending order.

Table_array: Is the lookup table that will be used to retrieve the desired values or data. Note:

If the formula will be used multiple times it is best to name the table range, by creating a named

range, which is described above.

Col_index_num: Is the column numberin the lookup table that holds the value or data you

want returned. Note: Column A = 1, Column B = 2, Column C = 3, etc.

7. Click on the red arrow next to the lookup_value field8. Click on cell of the value to be looked up (in the example cell G3 is select from the 06-07

worksheet)

9. Click on the red arrow to toggle back to the argument dialog box10.Click on the red arrow next to the table_array field11.Click on the worksheet of the table that will be pulling the values and select the whole table (in

the example, the Vlookup sheet was selected and the whole table was selected, the whole table


15/15

Page

was named Lookup, so it is not showing specific cell references. Note:To name a table use the

directions provided above in the Naming a Cell or Range of Cells section of the manual).

12.Click on the red arrow to toggle back to the arguments dialog box13.Click in the Column_index_num field14.Type in the column number of the look up table (table_array) that holds the value or data you

want returned (in the example we want the value for job title, which is held in column 2 of the

Vlookup table). Note: Column A = 1, Column B = 2, Column C = 3, etc.

15.Select OK when all arguments have been enteredCalculating with Logical Functions

Logical functions are used to create what if analysis. If the formula results are true than one value will

appear, but if formula results are false than another value will appear.

In the example formula if a student is a work study student, which is represented by a WS on the

spreadsheet, then the student wages paid will be multiplied by 40% to get the actual wage paid.

The logical statement used in the formula below is If. The if function requires three arguments:

Logical_test: This test if the formula is true or not. This first formula, test if the value in B4 is =

WS

Value_if_true: If the logical test is evaluated to true what should happen. The example formula

will take the pay amount in column G and multiply it by 40%, which is the actual amount paid for

a work study student.

Value_if_false: If the logical test is evaluated to false what should happen. The example

formula will take the value in column G and copy it to column I.

excel 2007 calculating with advanced formulas

Documents