Microsoft Office Tutorials and References
In Depth Information
sngResult = Application.WorksheetFunction.CountIf(.Cells, strCriteria)
MsgBox ("The number of cells that meet the criteria is " & sngResult & ".")
Case "Average"
sngResult = Application.WorksheetFunction.Average(.Cells)
MsgBox ("The average of the values is " & sngResult & ".")
Case "Mode"
sngResult = Application.WorksheetFunction.Mode(.Cells)
MsgBox ("The mode of the values is " & sngResult & ".")
Case "StDev"
sngResult = Application.WorksheetFunction.StDev(.Cells)
MsgBox ("The standard deviation of the values is " & _
sngResult & ".")
Case Else
MsgBox ("Unrecognized operation; please try again.")
End Select
End With
End Sub
Note You probably noticed how much work the With ActiveCell.CurrentRegion.
Columns(intColNumber) statement saved in the Summarize procedure. If it weren’t for
the With statement, the range reference code would have wrapped around every line, making
the code extremely hard to read. Always look for such opportunities to simplify your code.
Performing Financial Calculations
One of the most common financial calculations you’ll be asked to perform is to determine
the monthly payment on a loan. For that calculation you use the PMT function, which has
the syntax:
PMT( rate , nper , pv, fv, type )
Table 9-3 describes the five arguments used with the PMT function.
Table 9-3. An In-Depth Look at the PMT Function’s Arguments
Argument
Description
rate
The interest rate, to be divided by 12 for a loan with monthly payments
nper
The total number of payments for the loan
pv
The amount loaned ( pv is short for present value, or principal)
fv
The amount to be left over at the end of the payment cycle (usually left
blank, which indicates 0)
type
0 or 1, indicating whether payments are made at the beginning or at the
end of the month (usually left blank, which indicates 0, or the end of
the month)
Search JabSto ::




Custom Search