Summary Statistics

[carojasa]

See attached picture



I have this table with several Fields labelled 1, 2.....n
I want to generate summary statistics from all of my field columns but the problem i have is the following.



1. Some records are empty "null"
2. Some records are ''0''

I want to perform calculations of summary statistics (mean, median, iqr, sdv, etc) all at once and exclude any ''null'' and ''0'' records from the process.
I want to commit the calculated statistical values to a new table in which:
The row headings are the name of the statistical parameter ( Mean, median, etc)
The column heading corresponds to Field 1, 2, 3 etc
The intersection of row and column heading is the result of the calculation.

Is this possible?

Thank you

[June7]

Nulls are not included in aggregate calcs.

Try:

Sum(IIf([1]=0,Null,[1])

How do you plan to do all those calcs? Except for Mean (Average) Access does not have intrinsic functions for the ones you listed.

[orange]

Are the 0 and Null fields real values or some "other issue"?
I defer to June7 re Nulls and aggregate calculations.

Where are these functions you wish to use?

There may be an Excel expert here who can tell us more about the possibility of calling and using Excel functions from Access. My knowledge of Excel is extremely limited.

I have done this when helping someone, so in concept ...

  Sub xlChiInv()
            Dim obj As Excel.Application
10          Set obj = CreateObject("Excel.Application")
20          MsgBox obj.Application.ChiInv(0.05, 10)
30          obj.Quit
40          Set obj = Nothing
   End Sub

You may find some ideas at this link.

[June7]

It is possible to invoke Excel functions in VBA without having to set and declare Excel object. Don't forget to set Excel Object Library in VBA References. https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

I experimented with Excel functions in VBA when I needed to do matrix multiplication. I found that I got 3 different results:

1. function calc done in Excel spreadsheet

2. function calc done in VBA

3. complex VBA code to emulate the Excel function

[orange]

There are a number of math functions in vba here