FIS 303Three exercises to learn the basics of excel.

EXCEL EXERCISE #1: FORMATTING AND BASIC EQUATIONS

  1. Enter the information in the spreadsheet below. Be sure that the information is entered in the same cells as given, or the formulas below will not work.

You will notice that when you enter the information in the first column, the text runs over into the next cell. To adjust the size of the column, once all the information is entered for the first column, click on the column heading (that is the letter A). Then open the FORMAT menu, select the COLUMN options, and then select the AUTOFIT SELECTION command. 2.

  1. Enter the formula below into cell G5 and copy it into cells G6 to G8. This demonstrates the use of a "relative reference" (e.g., C5) that points to the contents of a cell

G5: =c5*.3+d5*.3+e5*.3+f5*.1

Now copy this formula to cells G6, G7, and G8. To do this click on cell G5 to make it the active cell. Then open the EDIT menu and choose the COPY command (a flashing border should now appear around the cell G5). Now click on cell G6 and drag the pointer so the range of cells from G6 to G8 are now highlighted. At this point you need to open the EDIT menu again, but this time selected the PASTE option. . Notice that when you copy this formula into other cells the row numbers for the cells change according to the row into which the formula has been copied.

  1. Enter the information below in the cell indicated.

B10: Averages

  1. Enter the formulas below in the cells indicated. These formulas demonstrate three methods for calculating averages for a column of data.

C10: =(c5+c6+c7+c8)/4

D10:=sum(d5:d8)/4

E10:=average(e5:e8)

  1. Enter the information below in the cells indicated. This will establish the weight each exam is given in a student's final average.

B12: Weights

C12: .3

D12: .3

E12: .3

F12: .1

  1. Enter the formula below into cell G5 and then copy it into cells G6 to G8. This demonstrates the use of an "absolute reference" (e.g., $C$12) that points to a specific cell in a spreadsheet. Notice that when the formula is copied into other cells, the absolute reference remains the same whereas the relative references change according to the location into which the formula is copied.

G5:=$c$12*c5+$d$12*d5+$e$12*e5+$f$12*f5

7. Make the changes to the cell contents indicated below and notice how the final averages change.

a. D5: 74

b. C7: 98

c. C12: .25 and E12: .35

d. E12: .30 and F12: .15

8. Just when you thought you were finished calculating final grades, you realize that you forgot someone. You know, that quiet student that always sits in the back of the room. Anyhow, you can start all over or simply insert a new row for the forgotten student.

a. Move the cursor to row 6 and click once (on any cell in this row or the row heading). Open the INSERT menu, select ROWS option. Notice how a new row is inserted after row 6. Also, check the formulas entered into cells D11, E11, G5, G7, G8, and G9 have now all changed to accommodate the newly insert row.

b. Now that an additional student has been added to your grade book, the formulas used to calculate the averages for Exams #1 and #2 are incorrect (this is because these formulas still assume only four grades are to be averaged. To correct this, copy the formula in cell E11 to cells C11 and D11. 3

c. Enter the information below in the identified cells.

A6: Linder, Barry

B6: 993-14-9283

C6: 81

D6: 73

E6: 83

F6: 65

d. Notice that the exam averages change when the new student's grades are entered but a final average is not automatically calculated for him. This is because the formula was not copied into that new row. Copy the formula in cell G5 into cell G6. Now your grade roll is completed.

9.IF statements can be used to automatically assign letter grades to each student.

a. Enter the following formula. H5: =if(g5>89,”A”,if(g5>79,”B”,if(g5>69,”C”,if(g5>59,”D”,”F”))))

b. Copy the formula in cell H5 to cells H6 through H9.

c. The IF command evaluates the first logical test (i.e., G5>89). If the statement is true an “A” is assigned. If the statement is false, the next logical statement is evaluated (i.e., G5>79). To place an IF statement inside another IF statement is referred to as “nesting.” Excel allows up you to nest up to seven IF statements.

10. SAVE your file as YOURNAMEEXCEL.xlsx

11.Print the first worksheet.

a. Go the File – Print.

b. On the bottom Box: Click “Fit to One Page”

12. Make sure you WRITE you name on your Printed Sheet.

EXCEL EXERCISE #2 MORE FORMATTING AND BASIC EQUATIONS

  1. Go to the bottom of the page where it says “Sheet 1” and hit the <+> symbol. Click on Sheet 2 to begin second exercise.
  1. Enter the text below in the cells indicated.

A1: YOUR NAME Balance Checkbook

A3: Ck. #

B3: Date

C3: Item Description

D3: Debit

E3: Credit

F3: X

G3: Balance

  1. Modify column widths for columns A through F. Instead of selecting the best fit option, indicate precisely the column width desired. Follow the steps below.

Step 1: Open the FORMAT menu.

Step 2: Select the COLUMN option.

Step 3: Select the WIDTH option.

Step 4: Type the desired number of columns in the box labeled “Column Width" (e.g., 5). Step 5: Click on OK>

Use the following widths for each column.

Column A: 5

Column B: 8

Column C: 30

Column D: 10

Column E: 10

Column F: 1

Column G: 12

  1. Format the numbers to show dollars and cents for all entries in columns D, E, and G. Follow the steps below.

Step 1: Click on the letter at the top of the column to be formatted. (The entire column should turn dark.)

Step 2: Open the FORMAT menu.

Step 3: Select the CELLS option.

Step 4: The NUMBER option automatically should be selected (if not, click on the tab labeled NUMBER).

Step 5: Under the Category label, select the option CURRENCY.

Step 6: Under the Format Codes label, select the format -$1,234.10 which is the third choice.

Step 7: Click on <Ok>

4. Format column B to enter the date of transactions. Follow the steps above but select the DATE as the category option and M/D/YY as the format codes option which is the first choice.

5. Enter the formulas below in the cells indicated.

G4: =-D4+E4

G5: =G4-D5+E5

6. Enter the information below in the rows indicated.

7. Copy the formula from cell G5 to cells G6 through G13.

8. Print Worksheet on One Page. Save Worksheet.

EXCEL EXERCISE #3: FORMATTING and BASIC FINANCE EQUATIONS

  1. Add Worksheet #3 to the Workbook to begin the final formatting exercise.
  2. Enter the labels below in the cells indicated.

A1: YOUR NAME Savings Account

A3: Year

B3: Amount in Account

C3: Interest Rate

D3: Interest Earned

E3: Total In Account

G3: Amount Deposited in Account (PV)

G4: Interest Rate(rate)

G5: Additional Funds In Account (pmt)

G6: Years(nper)

G7: Ending Value (FV)

G9: Ending Value (FV)

G10: Years (nper)

G11: Additional Funds In Account (pmt)

G12: Interest Rate (rate)

G13: Amount needed to deposit (PV)

3. Bold the text in cells that you just entered. Select these cells and click on the BOLD button.

4.Highlight Cells A1 – C1 and click <Merge and Center> in tool bar

5. Center the text in cells A3through E3 Select these cells and click on the CENTER button.

6.Size all cells appropriate.

Step 1: Click the grey cell between A and 1. This should highlight the entire worksheet.

Step 2: Hover your mouse between A and B in the grey area… it should turn into an line with arrows pointing each direction. Double click.

Step 3: Click into any individual cell to un-highlight the worksheet.

  1. Enter the numbers 1-10 in Colum A below the Years category (one number in each cell)
  2. Enter the following Data

B4: 800

C4: .05

  1. In Cell D4 Enter the Formula =B4*C4
  2. In Cell E4 enter the Formula =B4+D4
  3. In Cell B5 enter the formula =E4
  4. Copy C4, highlight C5-C13, click paste
  5. Highlight D4 and E4, Hit copy, highlight D5-D13, Click Paste
  6. Highlight B5 and copy, Paste into B6-13
  7. Format cells – format all Dollar Amounts for to show $X.XX and format %. Simply highlight the appropriate cells and click the “$” or “%” formula on your toolbar.
  8. This is the long way – not to show the short-cut. Enter the following Data:

H3: -800

H4:5%

H5:$0

H6:10

H9:-1303.12

H10:10

H11:$0

H12:5%

  1. The Future Value formula is constructed as FV(rate,nper,pmt,{pv},{type})

“rate” is the interest rate earned

“nper” is the number of periods

“pmt” is any payments during the period (must be a negative number)

“pv’ is any starting amount (must be a negative number)

“type” allows you to manipulate when payments are made – end of period is the default shown 0, beginning of the period would be a 1

Both pv and type are optional inputs

Enter the following formula:

H7: =FV(H4,H6,H5,H3)

  1. Present Valuing has the same inputs as future value but the formula is PV(rate,nper,pmt,{fv}, {type})

Enter the following formula

H13:=PV(H12,H10,H11,H9)

  1. Save and Print, fitted to one page and adjusted to landscape printing format.