Basic Formulas

• Please start excel with a blank worksheet.
• Today we will work on some basic functions.
• Task 1, a party.
  • I would like to plan a party.
    • I don't know how many people are coming, but I want a flexible budgeting tool.
    • I know each person eats 1 slice of cake and one single serving (4 oz) of ice cream.
      • At Giant Eagle, a 14oz container of Haagen-Dazs is $4.99.
      • At Giant Eagle, a 1/4 Sheet Darth Vader cake, serving 15 people is $28.99
  • Insert the assumptions.
    • Assumptions are the things that are constant now but could change.
    • These include the information about the cake and ice cream.
    • I will put these on a worksheet by themselves.
    • I will do some basic computations for later use.
  • In cell A9: E9 place the following
    • A9: Item
    • B9: Price
    • C9: Container Size
    • D9: Serving Size
    • E9: People Served
  • Make all of these
    • Bold
    • Centered
    • Word wrap
    • Border Under
    • Make all words fit without a break.
    • 
  • Add an entry for each item (Cake, Ice Cream)
    • Fill in the supplied data.
      • Make sure that money is formatted as money.
      • 
    • Calculate the people served per container for each
      • This is just the container size divided by serving size.
      • Work it out in your head (approximately)
      • The formula in E10 should be =c10/d10
      • This should be about 15, is it?
      • 
      • 
    • The formula for E11
      • We could reenter the formula from above adjusting for the cell locations (=c11/d11).
      • But that would be bad.
        • It is extra typing and that is not good.
        • There is a chance we could type it wrong, and that is bad.
        • It is really the same calculation as above.
      • It is better (much better) to copy formulas than to retype them.
      • Select cell E10 and use the fill handle to drag it down to E11
      • 
      • What happened ?
        • Note that the Column letters did not change
        • But the row numbers did.
      • This is because the default cell addressing is relative
        • As we copy formulas the cell addresses change.
        • When we copy down, the row numbers are increased
        • When we copy across, the column letters are increased.
      • Relative addressing is normally a good thing.
    • A side trip
      • Add a new worksheet
      • Call it Sand Box
      • Rename the first worksheet Budget
      • I want to build an addition table.
      • Put 1 in cell B1
      • Put a 2 in cell C1
      • Select these two cells and drag to the right using the fill handle. Go up to 10 (k1)
      • Put a 1 in cell a2
      • Put a 2 in cell a3
      • Select these two and drag down. Go up to 10 (A11)
      • In cell b2, add b1 and a1
      • 
      • Select this and drag it down to B11
      • What went wrong?
        • What do we want to do in cell b11?
        • Add 1 to 10 to get 11
        • What do we actually do ?
        • Add 10 to 46 to get 56.
        • Why?
        • Because we added one to the row number each time we copied the formula down.
      • What should have happened?
        • We wanted the a2 to become a3, a4, ....
        • But we always wanted to go back to b1
      • What should we do?
        • We want to keep the 1 in b1 from changing
        • To do this, change the formula in b2 to be =a2 + b$1
        • Copy this down.
        • What happened?
        • The $ in the formula kept the 1 from changing.
        • This is called an absolute reference
        • Did we need to keep the B from changing?
      • Let's develop a formula to do the top row.
        • We always want to refer to the A column in the first part
        • We always want to refer to row 1 in the second part.
        • =$A2+B$1 in cell B2
        • Drag that to the left
        • That worked for me.
      • In your own words, what does a $ in a cell reference do?
  • The main computation.
    • Let's add a place to put the number of people who are coming to the part.
      • This is probably an assumption, but I want it at the top.
      • In cell A1 put People
      • In cell B1 put 10
        • Make A1 bold
        • Make cell B1 light blue to indicate that we will allow input here.
      • Later we will change the contents of B1
    • Create a new table.
      • 
      • Calculate the number of cakes we need based on the values in B1 and the assumptions
        • Keep in mind we will probably want to copy this formula down.
        • People divided by people served per cake.
        • Or B1/E10
        • But B1 should probably be $B$1 so it doesn't change when we copy it.
        • =$B$1/E10
        • ARG, I cant buy .666 cake, I need to round that up to the next integers
        • =roundup($b$1/E10,0)
        • Copy this down.
        • Smile.
      • Compute the cost of the items.
        • Number needed times price per item.
        • =b4*b10
        • Copy this down.
      • Find the total cost
        • Add C4 and C4
        • A top border would be nice here.
    • Test things by changing the number of people a bit.
    • Why do 11 and 12 people cost the same?
    • Is there a big jump between 15 and 16 people? Why?
    • Is your spread sheet accurate?
    • 
    •