A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Published on

17,716 Points

In my article Calculating Special Means in Microsoft Access

Indeed, all four of those means, as well as the standard arithmetic mean, minimum, and maximum, are all specific cases of the generalized power mean, which we'll also examine in this article.

Special-Means-Part-2.mdb

This file contains all of the data and query definitions used in the four numbered examples in this article, and you may find the file useful for extending these examples or creating your own new examples.

The geometric mean can only be calculated when all members of the data set are positive.

For example, suppose you have a rectangular prism, with length = 20, width = 10, and height = 5. The volume for that shape is the product of the three dimensions: 20 x 10 x 5 = 1000. Now suppose that you want to know the dimensions of a cube having the same volume. Since the dimensions of a cube are, by definition, equal, then the dimensions are equal to the cube root of 1000, or 10. Thus, 10 is also the geometric mean of the data set {20, 10, 5}.

The simplest way to calculate geometric means in Microsoft Access is by leveraging a property of logarithms: the logarithm of the product of any set of positive factors is equal to the sum of the logarithms of each factor. Thus, we can re-write the basic formula for determining the geometric mean in terms of natural logarithms and the special number

Translating this into a generic SQL statement yields:

```
SELECT {group by columns if desired,} IIf(Min([ValueColumn]) > 0,
Exp(Avg(Log(IIf([ValueColumn] > 0,[ValueColumn], 1)))), Null) AS GeomMean
FROM {tables}
{GROUP BY {group by columns}}
```

```
SELECT {group by columns if desired,} IIf(Min([ValueColumn]) > -1,
Exp(Avg(Log(1 + IIf([ValueColumn] > -1, [ValueColumn], 1)))) - 1, Null) AS GeomMean
FROM {tables}
{GROUP BY {group by columns}}
```

Consider the per-period returns for the following two investments:

To find the CAGRs for these two investments, use the following SQL statement:

```
SELECT Investment, IIf(Min([AnnualReturn]) > -1,
Exp(Avg(Log(1 + IIf([AnnualReturn] > -1,[AnnualReturn], 1)))) - 1, Null) AS CAGR
FROM tblGeomMean
GROUP BY Investment;
```

That SQL statement yields the following results:To validate those results, consider the period-by-period results for Investment A:

Both approaches yield the same result: -0.0040, or -0.40%.

As above for the simple geometric mean, by using the rules of operations with logarithms we are able to simply the formula and place it into terms that Access can readily process by using natural logarithms.

As in the simple geometric mean, the values must all be greater than zero. Also, as with the weighted average and weighted harmonic mean, the weights cannot be negative, and there must be at least one non-zero weight. Any SQL statement used for calculating the weighted harmonic mean must test for those conditions.

Translating this into a generic SQL statement yields:

```
SELECT {group by columns if desired,} IIf(Min([WeightColumn]) >= 0 And Max([WeightColumn]) > 0
And Min([ValueColumn]) > 0,
Exp(Sum([WeightColumn] * Log(IIf([ValueColumn] > 0, [ValueColumn], 1))) /
Sum([WeightColumn])) - 1, Null) AS WtdGeomMean
FROM {tables}
{GROUP BY {group by columns}}
```

The “inner” IIf expression avoids the error that would arise from passing an invalid value to the Log() function; this inner IIf statement is necessary because Access always evaluates both the “if true” and “if false” parts of an IIf expression.

```
SELECT {group by columns if desired,} IIf(Min([WeightColumn]) >= 0 And Max([WeightColumn]) > 0
And Min([ValueColumn]) > -1,
Exp(Sum([WeightColumn] * Log(1 + IIf([ValueColumn] > -1, [ValueColumn], 1))) /
Sum([WeightColumn])) - 1, Null) AS WtdGeomMean
FROM {tables}
{GROUP BY {group by columns}}
```

Thus, to calculate the CAGR for each investment, we must use a weighted geometric mean, for which the weights are the length in years for each segment. To obtain the CAGR for each investment, use the following SQL statement:

```
SELECT Investment, IIf(Min([Years]) >= 0 And Max([Years])>0 And Min([AnnualReturn]) > -1,
Exp(Sum([Years] * Log(1 + IIf([AnnualReturn] > -1, [AnnualReturn], 1))) /
Sum([Years])) - 1, Null) AS CAGR
FROM tblWtdGeomMean
GROUP BY Investment;
```

This yields the following results:To validate those results, consider the period-by-period results for Investment A:

Both approaches yield the same result: 0.0309, or 3.09%.

Like the arithmetic mean and weighted average, but unlike the harmonic mean and geometric mean, the values in the data set can be any real number.

Translating this into a generic SQL statement yields:

```
SELECT {group by columns if desired,} Avg([ValueColumn] ^ 2) ^ 0.5 AS QuadMean
FROM {tables}
{GROUP BY {group by columns}}
```

To understand the variability of the differences between the theoretical and observed values, we can use the quadratic mean of the differences in the pair-wise values. To calculate the quadratic mean, use the following SQL statement:

```
SELECT Series, Avg(([Theory] - [Observed]) ^ 2) ^ 0.5 AS QuadMean
FROM tblQuadMean
GROUP BY Series;
```

This yields the following results:For the generalized mean, the following special cases may be of interest:

Translating this into a generic SQL statement yields (substitute your desired exponent for

```
SELECT {group by columns if desired,} Avg([ValueColumn] ^ p) ^ (1 / p) AS GeneralMean
FROM {tables}
{GROUP BY {group by columns}}
```

Translating this into a generic SQL statement yields (substitute your desired exponent for

```
SELECT {group by columns if desired,} (IIf(Min([WeightColumn]) >= 0 And Max([WeightColumn]) > 0,
Sum([WeightColumn] * [ValueColumn] ^ p), Null) / Sum([WeightColumn])) ^ (1 / p) AS WtdGeneralMean
FROM {tables}
{GROUP BY {group by columns}}
```

I am not providing examples for the generalized mean, as it is highly unlikely that you will ever need to handle any mean it can generate other than the arithmetic, harmonic, geometric, or quadratic mean, weighted or un-weighted, and this article already provides examples optimized for those sorts of means.

=-=-=-=-=-=-=-=-=-=-=-=-=-

Was this article helpful?

label that is just below and to the right of this text.

=-=-=-=-=-=-=-=-=-=-=-=-=-

0 Comments

Title | Views | Activity |
---|---|---|

Round by the power of two | 333 | |

Basic Error Handling Tool | 235 | |

MS Access true IDENTITY column | 305 | |

ISO 4217 Currency Codes in VBA | 118 |

Next Article:Sequential Rows in Microsoft Access