Assignment 2 Excel Skills: Formulas

SKILL 1: Creating and Using Formulas

·  What you need to know:

o  A formula calculates numbers by addition +, subtraction -, multiplication *, division /, exponents ^,

o  To enter a formula into a cell ALWAYS begin with an = sign and press enter at the end

o  When a formula is copied to other cells its cell references will automatically change. To make a cell “static”, so it does not “move” a $ is placed before the Column letter and/or row letter of the cell that you do not want to “move” when copied. Example: (=B7 not static) ( = $B$7 static)

o  Functions which can be found in the insert menu are useful for a wide variety of calculations. Go to INSERT > FUNCTION and learn the names of at least 3 functions and what they calculate.

o  A function’s arguments, cells used to figure out the answer. To enter a cell range you can type in the range such as A4:A13 or you can “highlight” from A4 to A13 and marching ants will appear.

·  Practice 1

  1. Open Microsoft Excel
  2. Type in the spreadsheet as shown & save name the file formulas
  3. Click on B6 & enter a formula to calculate the area as described below
  4. The Area of a rectangle equals length times width
  5. Type an = sign click on cell B4 then type * then click on B5
  6. Press enter
  7. Enter a formula in B7 to calculate the perimeter
  8. Hint: Perimeter of a rectangle equals 2 times the length plus 2 times the width
  9. Click on cell B3 use the fill handle by placing the cursor on the bottom right corner of cell B3 a black plus sign will appear, click and drag to G3, click on the menu box, the little white and blue box, that appears and be sure fill series is selected.
  10. Fill in the length and width for each of the 6 rectangles as shown

Rectangle 1 / Rectangle 2 / Rectangle 3 / Rectangle 4 / Rectangle 5 / Rectangle 6
Length / 50 / 75 / 20 / 15 / 14 / 21
Width / 25 / 13 / 10 / 14 / 12 / 20

6.  Highlight cells B6:B7 USE the FILL HANDLE & drag over to row G to copy the formulas

7.  Add items to colum A as shown below

8.  Fencing costs $1.67 per foot and fertilizer costs $1.35 per sq ft place these values in cells B1 and B2 appropriately.

9.  In B9 enter the formula to calculate the cost of fertilizer for the first rectangle =$B$1*B6 we don’t want B1 to “move” when we copy the formula so we use $ before the B&1 to make it static

10.  In B10, create a formula that calculate the cost of fencing for the 1st rectangle (Hint times the perimeter by the cost of fencing be sure to make B2 static by using $ signs)

11.  Highlight cells B9 and B10 then use the FILL HANDLE to copy the formula for the other rectangles. IF ###### signs appear make the column wider

12.  In cell H3 type total

13.  In cell H4In the formula menu INSERT a FUNCTION that will add up the numbers in cells B4 to G4 hint the function begins with the letter S you used it in your grade sheet

14.  Copy cell H4 by right clicking and choose copy, Paste in cells H5,H6,H7,H9,H10

15.  Save your work

·  Practice 2

o  Click on the tab sheet 2 at the bottom of the worksheet number column A from 1 to 10 as shown

o  Match the description & formula type the letter in column B( Like you are taking a quiz and the spreadsheet is the answer sheet)

·  Practice 3

o  Click on the tab Sheet 3 and type in the values as shown

o  Enter formulas in the specified cells that will perform the requested operations below.

Cell / Operation
C3 / Adds the values in A3 and B3
C4 / Subtract the value in B4 from the value in A4
C5 / Multiply the value in A5 by the value in B5
C6 / Divide the value in A6 by the value in B6
D3 / Add the values in A3 and A4, then multiply the sum by 3
D4 / Add the values in A3 and A4, then multiply the sum by B3
D5 / Subtract the value in B6 from the value in A6, then divide by 2
D6 / Divide the value in A6 by 2, then subtract the value in B6

.

Save Your Work as formulas