Banjarbaru Pulsa Paling Murah Kalimantan Selatan

Banjarbaru Pulsa Paling Murah Banjarbaru Kalimantan Selatan

read more | digg story

DMax DMin and DSum Macro

Session III Part 2

Database function in excel had definied to the following Function :

1. DAVERAGE Returns the average of selected database entries
2. DCOUNT Counts the cells that contain numbers in a database
3. DCOUNTA Counts nonblank cells in a database
4. DGET Extracts from a database a single record that matches the specified criteria
5. DMAX Returns the maximum value from selected database entries
6. DMIN Returns the minimum value from selected database entries
7. DPRODUCT Multiplies the values in a particular field of records that match the criteria in a database
8. DSTDEV Estimates the standard deviation based on a sample of selected database entries
9. DSTDEVP Calculates the standard deviation based on the entire population of selected database entries
10. DSUM Adds the numbers in the field column of records in the database that match the criteria
11. DVAR Estimates variance based on a sample from selected database entries
12. DVARP Calculates variance based on the entire population of selected database entries
    

In This Sections, we will try to create macro for DSUM, DMIN dan DMAX function. Now, open your macro in your microsoft excel work, create module and insert code below :

Function JMin(data, Field, citeria) As Double
JMin = Excel.WorksheetFunction.DMin(data, Field, citeria)
End Function

Function JMax(data As Range, Field As String, citeria As Range) As Double
JMax = Excel.WorksheetFunction.DMax(data, Field, citeria)
End Function

Function JSum(data As Range, Field As String, citeria As Range) As Double
JSum = Excel.WorksheetFunction.DSum(data, Field, citeria)
End Function




In your worksheets, the maro will work like this :



JMin, this function looks at the records of the minimum profit of apple trees over 12 in height.
JMax, this function looks at the records of the maximum profit of apple trees over 12 in height.
JSum, this function looks total profit from apple trees at all.

List of Worksheet Functions

Session III Part 1

Before go to the next code experiment, you should be know the list all of the worksheet functions. The following function definied in worksheet functions (by category) :

• Add-in and Automation functions
• Cube functions
• Database functions
• Date and time functions
• Engineering functions
• Financial functions
• Information functions
• Logical functions
• Lookup and reference functions
• Math and trigonometry functions
• Statistical functions
• Text functions

In This Sections, we will try to create Math and trigonometry functions.

Three common Statistical function Formula

Macro for Statistical function Formula
Session II Part 2.

In Statistical function, we will try to use excel function in macro. The common Statistical function in excel had definied to following function :

Now, can will create 3 function formula, MIN, MAX, and MEDIAN function. Open your module and copy code below :
Function nMin(data As Range) As Currency
nMin = Excel.WorksheetFunction.Min(data)
End Function
Function nMax(data As Range) As Currency
nMax = Excel.WorksheetFunction.Max(data)
End Function
Function nMedian(data As Range) As Currency
nMedian = Excel.WorksheetFunction.Median(data)
End Function



In worksheets, the macro will shown like this :









It's so easy, you can try another function in your code. Macro in excel is very usefull in a big data processing, i show you on next article. Sorry for my english.

Macro for Statistical function Formula

Macro for Statistical function Formula
Session II Part 1.

In Statistical function, we will try to convert code to macro. Statistical function in excel had definied to following function :
AVEDEV
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
BETADIST
BETAINV
BINOMDIST
CHIDIST
CHIINV
CHITEST
CONFIDENCE
CORREL
COUNT
COUNTA
COUNTBLANK
COUNTIF
COUNTIFS
COVAR
CRITBINOM
DEVSQ
EXPONDIST
FDIST
FINV
FISHER
FISHERINV
FORECAST
FREQUENCY
FTEST
GAMMADIST
GAMMAINV
GAMMALN
GEOMEAN
GROWTH
HARMEAN
HYPGEOMDIST
INTERCEPT
KURT
LARGE
LINEST
LOGINV
LOGNORMDIST
MAX
MAXA
MEDIAN
MIN
MINA
MODE
NEGBINOMDIST
NORMDIST
NORMINV
NORMSDIST
NORMSINV
PEARSON
PERCENTILE
PERCENTRANK
PERMUT
POISSON
PROB
QUARTILE
RANK
RSQ
SKEW
SLOPE
SMALL
STANDARDIZE
STDEV
STDEVA
STDEVP
STDEVPA
STEYX
TDIST
TINV
TREND
TRIMMEAN
TTEST
VAR
VARA
VARP
VARPA
WEIBULL
ZTEST