Creating Formulas and Functions

I will use this workbook to illustrate these ideas.

Creating simple formulas: Totals and averages
- Functions and formulas are the heart of the strength of excel
- EXCEL is not a fancy typewriter.
- Formulas are user entered computations
  - all start with an =
  - Basic operations +, -, /, *
  - () do as we expect and change the order of operations.
  - ^ is power 2⁵ is =2^5
- Functions are shortcuts for formulas
  - I would create a list of functions we have discussed.
  - =SUM()
    - =sum will calculate the sum of a range of cells
    - =sum(a1:a5)
    - =sum(a1,a2,a3,a4,a5), but why would you do this?
  - =average(range), =average(cell,cell,cell)
    - This will calculate the average (mean) of the given cells
    - This is a good example of a "shortcut"
- If there is a function, and you know it exists, you should use the function instead of creating a formula
  - Functions are more likely to be accurate, in all situations
  - And are more likely to detect and deal with errors correctly
  - And may be faster.
  - And are more likely to be trusted by others.
  - And are more likely to be understood by others.
  - And are better documented.....
Copying a formula of adjacent cells
- This is a critical skill
- Notice how cell references change when a formula/function are copied
  - When a formula is copied to the right, the letters increase by the distance copied
  - Same for row numbers when copied down.
- You can copy with the fill handle.
- Or with copy and paste.
Calculating year-to-date profits
- Notice the technique he uses here.
- A running total is a common task.
  - Just put the first amount in the starting cell
  - The next cell is the sum of the previous running total and the current amount.
Creating a percentage-increase formula
- Percent change = (new-old)/old
  - If the change is negative, it is a percent decrease
  - If it is positive, it is a percent increase.
- Notice that () are important in the formula
- So we need to add these to formula in excel as well.
- Remember your Order of operations, PEMDAS
Working with relative, absolute and mixed references
- This is a critical section.
- The $ in a cell reference keeps the item proceeds from changing when the reference is copied
  - a3 - both will change when the formula is copied.
  - $a3 - the a will not change but the 3 will.
  - a$3 - the a will change but the 3 will not
  - $a$3 - neither will change.
Using SUM and AVERAGE
- He demos the autosum tool
- This is cool, but I am not sure I trust it.
  - I am sure that it is accurate,
  - But I am not sure I want it to fill in a bunch of formulas at once.
- This is very much a brain first game.
Using other common functions.
- he introduces how to find formulas.
- The grouping ins nice.
- He reviews a few useful functions
  - =counta(range) : counts nonempty cells in a range.
  - =count(range) : counts cells in a range that have numbers
  - =median(range) : gives the median for a range.
  - =max(range), =min(range)
  - =large(range, position)
    - =large(range,1) is the max
    - =large(range,2) is the second largest
  - =stdev.p(range)
On the worksheet posted above
- Calculate the Amount in cells G7-G13
- Calculate the Discount in cells h7-h13, use a mixed reference
- Calculate the price in cells i7-i13
- Calculate an order total in j8, j11, j13
- Calculate the percent change from the previous order in j11 and j13
- Calculate a running total in l8-l13
- Calculate max,min, average for each column in F15:I17
- Calculate counts of column A and D in F20 and 21
- Calculate subtotal, tax and total in C16-18.