Excel Formulas & Functions

To start, let's view this YouTube Video through 5:05

Basic Parts of a Formula

= all formulas MUST begin with an equal sign

(content between these) is the argument, range, etc.

Functions

SUM, AVERAGE, MIN, MAX, COUNT is the function

colon : which would denote a range of sequential numbers

**to create a formula you need an equal sign, function (if needed), parenthesis are a good idea but not ALWAYS necessary (depending on mathematical operation to be performed), an argument and/or cell references and mathematical symbols as needed (+ / * -)**

--------------------------------------------------------------------------------

Let's write one together now. If I want to add cells A4+B10+D4+G3 what would I do?

Start with an =

Add your left parenthesis =(

Add your cell references =(A4+B10+D4+G3

Close your parenthesis =(A4+B10+D4+G3)

----------------------------------------------------------------------------------

Let's write another one together. If I want to add sequential cells A4+A5+A6+A7+A8+A9+A10 what would I do?

Start with an =

Add your left parenthesis =(

Add your cell references =(A4+A5+A6+A7+A8+A9+A10

Close your parenthesis =(A4+A5+A6+A7+A8+A9+A10)

or

Because they are sequential, you can use the SUM function and colon (:)

so your formula would look like =SUM(A4:A10)

----------------------------------------------------------------------------------

Tips:

  • ALL formulas in Excel MUST begin with an equals sign
  • You can enter a cell reference (B3) into a formula by manually typing the letter and number combination or you can enter it by physically clicking on the cell you wish to use
  • There are NO spaces in formulas

Basic Functions:

· Addition example =(B4+B5+B6+B7+B8+B9+B10) or =SUM(B4:B10)

· Subtractions example =(B7-B8)

· Multiplication example =(B4*B7)

· Division example =(B9/B2)

Advanced Excel Functions:

· Average – will calculate the average of a range of numbers example =AVERAGE(B4:B10)

· Minimum – will return the smallest number of a range of numbers example =MIN(B4:B10)

· Maximum – will return the largest number of a range of numbers example =MAX(B4:B10)

· Count – will count the numbers in a range example =COUNT(B4:B10)

-------------------------------------------------------------------------------------------------------------

Now let's actually try this in a spreadsheet:

  • Make sure that you are logged into Google
  • Copy the link below into a browser

https://drive.google.com/file/d/0B9zsBsEN-t45TThmVWpzZGVrTVk/edit?usp=sharing

  • When it appears, select File - Open with - Google Sheets
  • Follow these 7 steps to fill the appropriate formulas into the shaded cells:

1. Copy the Fee per Credit Hour from F7 into F8 through F16

2. Calculate the appropriate Tuition Fee for G7 through G16

3. Calculate the Total Student Fees in G18

4. Calculate the average Credit Hours for all students in E20

5. Calculate the minimum Credit Hours for all students in E22

6. Calculate the maximum Credit Hours for all students in E24

7. Calculate how many students have Credit Hours listed in E26

When you are done, share your file with your teacher at the appropriate email address

-------------------------------------------------------------------------------------------