Calculate Average in a Query

  • Thread starter Thread starter SteveL
  • Start date Start date
S

SteveL

I have a query which returns 5 values in 5 separate
fields. (V1, V2, V3, V4, and V5). In the next field I
want to average these 5 values. How do I write the
formula to do this?

Then to make it even more complicated, if the value in
any of the fields is "0", I don't want it ncluded in the
calculation.

Finally, what I really want is the MEDIAN calculated.

Can anyone help?

--Steve
 
You can do the average with a horrendous mess of nested IFs to get rid
of the zero values (is it really zeros you want to ignore - not
Nulls?). You will have to do the Median with VBA, so you might as
well do both that way. I do question, however, the statistical value
of a median of just five (or less) values. Having said that, I have
previously posted a general purpose Access Class to do medians,
quartiles, deciles, percentiles, etc., and can give you a link to
download it, if you wish. You could then call the code in it it from a
short VBA stub.


I have a query which returns 5 values in 5 separate
fields. (V1, V2, V3, V4, and V5). In the next field I
want to average these 5 values. How do I write the
formula to do this?

Then to make it even more complicated, if the value in
any of the fields is "0", I don't want it ncluded in the
calculation.

Finally, what I really want is the MEDIAN calculated.

Can anyone help?

--Steve
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Rather than upsetting people by posting what would be an even longer
message and occupying bandwidth unnecessarily, I have uploaded a zip
file to my business website. The file contains the Class as a .cls
file and a small database that demonstrates its use in a report.

Note that you do have to change .BucketSize from its default to get
the median of five or less values.

After importing the Class into your Application, your Median function
would look something like this:

Function DoMedian (varA, varB, varC, varD, varE) As Variant

' You can declare everything in the declaration with numeric data
' types if you know that they are all going to be numeric

Dim objStats As CumStats

Set objStats = New CumStats
objStats.BucketSize = 1
objStats.AddItems (varA, varB, varC, varD, varE)
DoMedian = objStats.Median

set objStats = Nothing

End Function


The link is http://www.parish-data.com/data/cumstats.zip

The externally visible Methods of the Class are as follows:
AddItem(VarValue as Variant) - Adds an value to the internal list of
values of which the Median, first Decile, or whatever boundary value
you want will be returned. If you pass AddItem a Null it will simply
be ignored. The data type (Date, Integer, String, etc.) of the first
value added to the list is recorded. Subsequent values added must be
of the same type, or you will get an error box and the new item will
not be added.
AddItems(VarArray as Variant | VarValue1, VarValue2,....,VarValueN as
Variants) - Adds multiple values to the list. Accepts either an Array
containing multiple values or a list of values. This calls AddItem
internally, so it follows the same rules regarding data types.
Median - Returns a Variant containing the median value from the list.
This is written as a Method but can be regarded as a readonly
Property. If there are less than 2 * BucketSize (see below) total
values in the list, Median returns Null.
Slice(NSlices as Integer, SliceNumber as Integer) - Returns a Variant
containing the specified boundary value. This does quartiles, deciles,
or whatever you want - for the first quartile boundary value you would
invoke it as Slice(4,1). Median converts internally into Slice(2,1).
If the number of values in the list divided by NSlices is less than
than BucketSize (see below), Slice returns Null, as it does if
SliceNumber>= NSlices, or either argument is <1.

The externally visible Properties of the Class are:
Count - Returns a Long containing the number of values in the list.
BucketSize - Gets or Sets the integer number of values in a slice
below which Slice (or Median) will not return a value. This is
read/write and the default value is 3. The object of this is to avoid
reporting stupid statistics (like the 4th decile boundary of a list of
8 values!).

Let me know if you have any problems with it.

Peter,

Thanks, and yes, please provide the link.

--Steve
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Peter,

I just realized that the solution you've presented will
calculate median on the same field for any number of
records, or so I believe. But what I need to to do is
calculate media on 5 fields... Val1, Val2, Val3, Val4,
Val5 within one record. Am I missing something?

--Steve
 
It will calculate the Median of whatever values are passed to it. If
you define a calculated Column in your query as DoMedian(Val1, Val2,
Val3, Val4, Val5), you will get back the median value of these fields
for each record.


Peter,

I just realized that the solution you've presented will
calculate median on the same field for any number of
records, or so I believe. But what I need to to do is
calculate media on 5 fields... Val1, Val2, Val3, Val4,
Val5 within one record. Am I missing something?

--Steve
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top