CSC 110 -Intro. to Computing

Lecture 11:Spreadsheets

Announcements

Make sure you get a “PALGO” handoutWill use handout for next few weeks

Service learning pages due todayFor future deadlines, please e-mail me pages

Homework #3 handed out at end of classDue in my box at 5PM Monday, Feb. 27th

Quiz #3 will be in class next Thursday

Computer vs. Professor

Computer Professor

Memory Huge (> 60GB) Can’t remember 10 names

ComputingSpeed

Fast (> 2 billion/second) Slow (needs fingers & toes)

Takes direction

Does exactly as told Still leaves the toilet seat up

Speed of updates

Nearly instantaneous Still wears t-shirts from 1987

Ability to plan/reason

Cannot make plans; No reasoning (lacks common sense)

Makes (semi-)complicated plans;Good reasoning skills (but lacks common sense)

People vs. Computers

People: good at reasoning & analyzingOften have trouble evaluating all possibilities

Cannot consider all the different data combinations Slow computing outcome of all decision outcomes

Computers: quickly do simple tasksHave lots of memory and are good at mathBut lack concept of future, ability to plan,

determine best choice from range of options

Spreadsheets present a grid in which data can be entered We call each location a cell Normally name cells using their

row and column Just like in game of “Battleship”

Each cell can contain a label, value, or an equation

Spreadsheet

B2

C4A5

Using Cells

Easiest way to use cell is to enter a valueCan be a number like 4, -1, or 56.2271Can be a label like Tutors, Interest Rate, Average,

or Lbs. of Raw Beef ConsumedCan be an equation

All equations must begin with = Can then use +, -, *, / to do simple arithmetic More complex calculations are also possible using

functions included in spreadsheet

Spreadsheet Calculations

Power of spreadsheet comes from ability to use cells within an equationUse cell’s name to mean the value of that cellSo to add the values in cell A2 and A3 type: = A2 +

A3 Can even use result of equation within another

equationSpreadsheet does not differentiate cells containing

equations or values

Spreadsheet Usefulness

Cells display current resultsWhen a cell’s value changes, the spreadsheet

immediately recomputes equations using that cellAlso reevaluates equations which rely on those

automatically propagates values And cells that use this second set of updates, and cells

that use those cells… Updates are automatic and very quick!

Spreadsheet Differences

Every Spreadsheet program is uniqueHave different names for some functions Include/not include identical functionsSpecify equations in other mannersUse different characters to specify ranges

But rare for normal people to find these differences

Common Spreadsheet Functions

AVERAGE(cells)Computes mean of the values in the cells

MIN(cells)Determines smallest numerical value in cells

MAX(cells)Returns largest number in the cells

COUNT(cells)Calculates how many cells actually contain a value

Common Spreadsheet Functions

SUM(cells)Computes sum of the values in the cells

IF(test,TRUE_VALUE,FALSE_VALUEPerforms test; if test is true, it evaluates and

returns TRUE_VALUE, otherwise it computes and returns FALSE_VALUE

Circular References

Circular reference exists when a cell’s value depends on its own valueFor example if we defined cell C2 as:

= D2 + C2Spreadsheet would have to update cell C2

whenever the value in cell C2 changes Circular references would force programs to

recompute a cell’s value forever

Circular References

May occur through multiple cell definitionsB2 = F2 - 1

C2 = B2 - 2D2 = C2 * 2E2 = D2 / 2F2 = E2 + 2

Creates infinite cycle of updates: B2 C2 D2 E2 F2 B2 C2 D2 E2 F2

Spreadsheet Benefits

In addition to normal uses, dynamic nature of spreadsheets enables what-if analyses:What if I charge 10% more per murder? What if drug cartel reduces shipments by 50%What if I double my profit per widget? What if we threw a war and no one came?

(Probably not this last one)

For Next Lecture

Get excited about programming and start reading handout

Complete and submit homework #3E-mail me/visit my office if you have questions

Start looking over topics where you are still uncertainClarify thoughts with me/classmates/tutorsMidterm will be here sooner than you think