Thursday, May 10, 2012
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
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
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
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
Subscribe to:
Posts (Atom)