walkthru 2nd excel exam

lecture notes

connect to cs12 machine

if you have a mac,
to download fugu
http://download.cnet.com/Fugu/3000-2155_4-26526.html
instructions for setting it up with your text editor
http://cs12.cs.qc.cuny.edu/~xiuyi/fugu.html

if you can't log in using a wireless connection from your laptop
on campus, it is because you signed on as Guest. you need to
use real credentials

to convert from APR to annual rate
(1 + RATE/freq)^freq

Walkthru, first practice Excel exam

more to come...

we can nest
HOWEVER, only 7 deep
restriction of Excel


this is limitation
the other is that the formula becomes incredibly complicated

we can reduce the height of the tree by BALANCING the tree
then we can ask a lot more questions
and get a lot more results
specifically, 2^height results

AI = artificial intelligence
expert system
20Q

2^20?

Turing Test. by Alan Turing
http://en.wikipedia.org/wiki/Alan_Turing

http://www.sosmath.com/CBB/viewtopic.php?t=7238&sid=e7a5ec278f2f205c20b1b11074468e35

chatbots
eliza
http://www.cleverbot.com/

try interacting with one of these
=RATE(5,-4500,-25000,70000,0)
=NPER(0.6%, -1000, 12000, 1000000, 0)
=A2/12
=PV(0.7%, 10*12, -1000, 1000000, 0)
=PV(8%, 25, 25000, 0, 0)
forever?
=PV(8%, 3000, 25000, 0, 0)

=PV(8%, 1000, 0, 1000000)

lecture notes

Excel Financial functions

using John Walkenbach, Excel 2003 Formulas
Chapter 11
Not present in the 2007 book

PMT, FV, PV, RATE, NPER

Time value of money
FV = PV*(1 + RATE) ^ NPER

for one year:
FV = PV + PV * RATE
FV = PV * (1 + RATE)

if FV is PV for next year,
for subsequent year, mult by (1 + RATE)

FV = PV*(1 + RATE) ^ NPER
we can solve for PV

flow of money represented by a sign
money flowing towards me is positive
money flowing away from me is negative
answers:

=FV(7%, 3, 0, -1000)
=RATE(8, 0, -1000, 2000)
=NPER(14%, 0, -100000, 1000000)
=PV(1%, 12, 0, 10573.45)
=FV(1%, 24, -300, 0, 1)
=FV(7%, 3, 0, 1000)
=FV(0.75%, 36, -500, -5500, 0)

we finished problem #8

back to the Ifs
=(Subtotal+TaxAmt)* IF(Subtotal>B14, IF(Subtotal>B15, C15, C14), 0)

This is called Nesting
especially when we put one If inside of another If

lecture notes

Visual Basic, If statement

decision making
VB program to calculate semester avg
input?
output?

Private Sub cmdCalculate_Click()
    Dim semesterAverage As Integer
    Dim midterm As Integer
    Dim final As Integer
    Dim bribe As Integer
  
    ' get input
    midterm = txtMidterm.Text
    final = txtFinal.Text
    bribe = Val(txtBribe.Text)
  
    ' processing
    If bribe >= 50 Then
        semesterAverage = 100
    Else
       semesterAverage = (midterm + final) / 2
    End If
  
    ' output results
    cmdCalculate.Caption = semesterAverage
End Sub

HW: Make a greeting line, where if
the Title is absent,
 do something intelligent

HW: In Word MergeFields, use an If Else.
If the person has a pet, inquire about the
pet's well-being, by name. Otherwise, suggest
that they adopt a pet.

Excel
self-documenting
using the Name box
default is to name Absolute References

HW: In excel, using an If, taking in midterm, final, bribe, calculate semester average
Use names for it. All you need to submit is the formula, not a whole spreadsheet

=IF(Subtotal>=100, (Subtotal+TaxAmt)*DiscountPercentage, 0)
=(Subtotal>=100)*(Subtotal+TaxAmt)*DiscountPercentage
It turns out that TRUE is 1.
It turns out that FALSE is 0.

Nesting

HW: Using nesting, calculate a 20% discount
for over $200, a 10% discount for over $100,
otherwise, nothing

Web
Styles are defined within CSS
Cascading Style Sheets
http://www.w3schools.com/css/css_intro.asp

selector declaration
p {color:red;text-align:center;}

Quiz two mondays from now, only up to this lecture

The sorting spreadsheet

has been uploaded to BlackBoard, so check it out.