Skew (w2k)

Topic

New Reply

I could use some help with calculating a skew. I looked in help and it has the following:

SKEW(number1,number2…)
Number 1, Number 2,…are 1 to 30 numbers or references that contain number for which you want the skewness.

As easy as it sounds, I must not understand it because I could not get it to work. Can someone translate this for me? I may have taken this definition too literally.

Thanks.

Reply | Quote

Replies

Just like the Correlation Coefficient you asked about in November, SKEW is an Excel worksheet function. It is not part of Access.

You could use it in Access by creating an Excel application object, but that may be overkill. If you really need it, you could write a user-defined function in VBA, or someone may be willing to write it for you if you ask nicely …

Reply | Quote

OK. Thank you for your response…

I am asking nicely. Can someone write a user-defined function in VBA for SKEW?

Thanks.

Reply | Quote

What does SKEW do?
Pat

Reply | Quote

Skew indicates the level of symmetry or lack there of.

Reply | Quote

Well, since you ask so nicely …

Here is a function that calculates the sample skewness of a field in a recordset (existing table or query) with an optional where-condition imposed. If the field name or recordset name includes spaces, enclose them in square brackets [ ]. Examples of usage:
=Skew(“Price”,”tblCars”)
=Skew(“Price”,”tblCars”,”Brand=’Ford'”)
This function is the equivalent of the Excel worksheet function SKEW. If you want the population skewness, use DStDevP instead of DStdDev, and omit the line
Skew = Skew * (lngCount ^ 2) / ((lngCount – 1) * (lngCount – 2))

Public Function Skew _
(sField As String, _
sRecordSet As String, _
Optional sCondition As String) As Double

Dim dblAvgOfField As Double
Dim dblAvgOfSquare As Double
Dim dblAvgOfThirdPower As Double
Dim dblStDev As Double
Dim lngCount As Long

On Error GoTo Err_Skew
lngCount = DCount(sField, sRecordSet, sCondition)
dblAvgOfField = DAvg(sField, sRecordSet, sCondition)
dblAvgOfSquare = DAvg(sField & “^2”, sRecordSet, sCondition)
dblAvgOfThirdPower = DAvg(sField & “^3”, sRecordSet, sCondition)
dblStDev = DStDev(sField, sRecordSet, sCondition)
Skew = (dblAvgOfThirdPower – 3 * dblAvgOfSquare * dblAvgOfField + 2 * dblAvgOfField ^ 3) / (dblStDev ^ 3)
Skew = Skew * (lngCount ^ 2) / ((lngCount – 1) * (lngCount – 2))
Exit Function

Err_Skew:
Skew = 0
End Function

Notes:

• For large recordsets, this function will be slow, because the domain functions DAvg etc. are slow.
• I have tested this function on small sample data sets, but I can’t guarantee that it will return correct results under all circumstances. Make sure to test it before relying on it.
• Access has some basic statistical functions built in, but it is not a statistics program. Excel has many more statistical functions; it wouldn’t be sensible to try and emulate them all in Access. It’s often easier to import the data into Excel and use the built-in features.
  If you need to perform complex statistical analysis often, I would recommend using a dedicated statistics application such as SPSS, SAS or SPlus. You can import data from Access into, say, SPSS and do much more than is possible in Access or Excel.
  [/list]

Reply | Quote

Hans,

Thank you so much!!

I will also check into SPSS, SAS and SPlus and see if it will fit our needs.

Reply | Quote