Thursday, May 10, 2012

Circular references



Watch them from 1 to 7:
circular references in excel 5
circular references in excel 4
circular references in excel 3
circular references in excel 2
circular references in excel 1

Tuesday, May 8, 2012


to learn some access (if interested)
Access practice exam files.

Practice Access Exam 1.
Practice Access Exam 2.
Access Review Presentation.



Practice Access exam walkthroughs









For the cs88 final


walkenbach:
ch 9 (database functions) (dsum, dcount), which are based on Criteria ranges. Advanced filtering.
ch 17 (advanced charts). that there is something called a SERIES formula, and how to manipulate it.
appendix C (custom formatting). and recall that can also use these in the TEXT function.
ch 16: intentional circular references. recall that you may need to use a seed.
ch 19: conditional formatting and data validation. recall that in Custom, you can validate data (or color values in cond formatting) based on a formula.

In Grauer:
ch 7 - 11

Sample exam:
1) using database functions, give me the name of the salesman who sold the most widgets.
2) For extra credit, do this without using a range on the spreadsheet.
3) Show a chart which will encompass all salesmen and their widgets sold, which dynamically grows.
4) Show me, using fractions, the percentage of sales that this max salesman brought in. If it is less than 50 percent, color it red. Otherwise, color it Green. Do this using appendix C.
5) Do the same using conditional formatting.
6) give me the all time max and min.
7) Within the salesman table, color any salesman who has less sales than the saleman above red; otherwise, blue.
8) VBA question returning an array. Give me the sum of two arrays.

Tuesday, April 17, 2012

vba answers

Option Explicit

Function JoshFunc(r As Range)
    Dim temp As Double
    temp = WorksheetFunction.Sum(r)
    JoshFunc = Evaluate("sqrt(" & temp & ")")
End Function

Function JoshFunc2(r As Range)
    Dim theSum As Double
    Dim cell As Range
    For Each cell In r
        theSum = theSum + cell.Value
    Next cell
   
    JoshFunc2 = Evaluate("sqrt(" & theSum & ")")
    JoshFunc2 = theSum ^ 0.5
End Function

Sub Macro1()
    For Each cell In Selection
      If cell.Value >= 5 And cell.Value <= 50 Then
        With cell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
      End If
    Next cell
End Sub






Sample midterm 2


Date and Time:
1) Using just arithmetic, give me a date 5 years from now. (Pretend there are no leap years.)

2) Do the same using data and time functions.

Counting and Summing:

Generate a list of Salespeople and the number of Widgets they sold, and total revenue they brought in.
1) Count how many people sold between 8 and 23 widgets. Use summing functions. (e.g. sumif, countif)
2) Do the same using an array formula

Do the same based on length of salesperson's name (between 4 and five letters long)
1) using summing funcs
2) using array formulas

Lookup functions:
Look up for me Bob's revenue and #widgets sold, from same table

financial functions
If I borrow 1 million for 100 years, at 7% APR, compounded quarterly, with a balloon payment of half a million, what will be my payment?


If I borrow 1 million for 100 years, with a PMT of $1000 monthly, compounded quarterly, with a balloon payment of half a million, what will be my APR? What will be my annual effective rate?


VBA question:
write a UDF that takes in a range and returns the square root of the sum of all the elements in the range
make a macro that will color all values in a range between 5 and 50 the background color red.

Tuesday, March 27, 2012


Private Sub cmdAddThem_Click()
On Error GoTo hello
    Dim num1 As Double
    Dim num2 As Double
    Dim result As Double
   
    num1 = Val(txtFirstNum.Text)
    num2 = Val(txtSecondNum.Text)
   
    result = num1 + num2
   
    lblOutput.Caption = result
    Exit Sub
hello:
    MsgBox "error: it needs to be a number"
End Sub

Thursday, March 22, 2012


What have we covered past the midterm?

nothing yet in Grauer (would be ch 5 and 6)
In formulas book:
ch 6 (dates and times)
ch 7 (Counting and summing)
coupled with
ch 14 and 15, array formulas
ch 8, lookup functions
ch 11, financial formulas
on codecademy, the rest. (loops, functions, arrays)

VBA:

Option Explicit

' a UDF that processes every cell in a RANGE
Function MySum(r As Range) As Double
    Dim sum As Double
    sum = 0
    Dim cell As Range
    For Each cell In r
            sum = sum + cell.Value
    Next cell
    MySum = sum
End Function


Function MySpecialSum(r As Range) As Double
' only adds red cells
    Dim sum As Double
    sum = 0
    Dim cell As Range
    For Each cell In r
        If cell.Font.Color = 255 Then
            sum = sum + cell.Value
        End If
    Next cell
    MySpecialSum = sum

End Function





Tuesday, March 13, 2012


Option Explicit
' write a UDF to convert a word to pig latin
Function PigLatin(T as String) As String
   ' 1. extract first letter
   Dim firstLetter As String
   firstLetter = Left(T, 1)
   if firstLetter = "A" Or firstLetter = "E" Or firstLetter = "I" Or firstLetter = "O" Or firstLetter = "U" Then
PigLatin = T & "way"
   else
PigLatin = Mid(T, 2, 1000) & firstLetter & "ay"
   end if
End Function

latin
1. extract first letter =LEFT(G2)
2. is it a vowel? =OR(G3="A", G3="E", G3="I", G3="O", G3="U")
3. calculation for vowel
3a. Take word, add 'way' =G2 & "way"
4. calculation for consonant
4a. Rest of word, besides first =MID(G2, 2, 1000)
4b. What was that first letter? =LOWER(G3)
4c. Concatenate =G8 & G9
4d. Add 'ay' =G10 & "ay"
5. Select vowel or consonant path =IF(G4, G6, G11)

ch 11: financial functions
time value of money
present value vs. future value
PV, FV
related by RATE
FV = PV + PV * RATE
FV = PV * ( 1 + RATE )
FV2 = PV * ( 1 + RATE ) * (1 + RATE)
FV3 =  PV * ( 1 + RATE ) * (1 + RATE) * (1 + RATE)

for year NPER
FV = PV * (1 + RATE) ^ NPER

nominal rate, APR (annual percentage rate)
7%
periodic effective rate. for a given compunding period (say, a month), what is the rate?
APR / numperiodsperyear

1. What function should I use?
2. What are my parameters?

Thursday, March 8, 2012

some code

Function Identity(x)
    Identity = 1
    Exit Function
    Identity = x
End Function

Function Square(x)
    Square = x * x
End Function

Function IsOdd(n)
    If n Mod 2 = 0 Then
        IsOdd = False
    Else
        IsOdd = True
    End If
End Function

Function isLost(x)
    Dim lost(5) As Integer
    lost(1) = 4
    lost(2) = 8
    lost(3) = 18
    lost(4) = 20
    lost(5) = 22
    For i = 1 To 5
        If x = lost(i) Then
            isLost = True
            Exit Function
        End If
    Next
    isLost = False
End Function

for each loop
Function isLost(x)
    Dim lost(5) As Integer
    lost(1) = 4
    lost(2) = 8
    lost(3) = 18
    lost(4) = 20
    lost(5) = 22
    For Each Item In lost
        If x = Item Then
            isLost = True
            Exit Function
        End If
    Next
    isLost = False
End Function