"Bernie Deitrick" <deitbe @ consumer dot org wrote:
You cannot 'backtransform' mean values, either:
[....]
Avg: 8 0.893747079
10^0.893747079 = 7.829735282 not 8.

I assume that Fluff is talking about a situation where the geometric mean
makes sense; for example, a time series of numbers such as the periodic
return (percentage change) of stock prices.

Consider your example, where N1:N3 are the annual percentage change of stock
prices, namely 6%, 8% and 10%. Then, one way to compute the geometric mean
is with the following array formula (commit with ctrl-shift-Enter):

=10^AVERAGE(LOG(1+N1:N3))-1

Compare with the array formula =GEOMEAN(1+N1:N3)-1.

Similarly, the population and sample geometric standard deviations can be
computed with the appropriate following array formula:

=10^STDEVP(LOG(1+N1:N3))-1

=10^STDEV(LOG(1+N1:N3))-1

Finally, the population and sample standard errors can be computed with the
appropriate following array formula:

=10^(STDEVP(LOG(1+N1:N3))/SQRT(COUNT(N1:N3)))-1

=10^(STDEV(LOG(1+N1:N3))/SQRT(COUNT(N1:N3)))-1

I don't know why Fluff thought the log standard error cannot be transformed
into the geometric standard error in the same way that the log average is
transformed into the geometric average. It can. The formula is 10^logSE-1,
however Fluff decides to compute the log standard error.

Perhaps Fluff is confused about how to use the (transformed) geometric
standard error.

Personally, I would work with the log average, log standard deviation and
log standard error, then transform the result back using 10^whatever.
Otherwise, things get a little weird when applying a z or t factor.

For example, the confidence limits around the population geometric mean are
u*SE^z and u*SE^-z (or u/SE^z).

Similarly, if N1:N3 are log normal, the distribution around population
geometric mean is defined by u*s^z and u*s^-z (or u/s^z).

It gives me a headache just to think about it :-).


----- original message -----

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Fluff,

You cannot 'backtransform' mean values, either:

Log10
N1 6 0.77815125
N2 8 0.903089987
N3 10 1
Avg: 8 0.893747079

10^0.893747079 = 7.829735282 not 8.

The greater the spread of numbers, the greater the error. You can use an
array formula (entered using Ctrl-Shift-Enter) like

=AVERAGE(10^A2:A4)

to directly calculate the average (mean).

I'm sure Harlan will come around to set me straight, but you calculate the
standard error directly, again with an array formula (entered using
Ctrl-Shift-Enter)

=STDEV(10^Rng)/(SQRT(COUNT(Rng)))

Like

=STDEV(10^A2:B20)/(SQRT(COUNT(A2:B20)))

Bottom line - only use a log transform if there is a meaningful reason to
do so....

Bernie
MS Excel MVP

"fluff" wrote in message
...
Hi everyone,

I'm trying to find the back transformed values of my log10 transformed
standard error values.

The mean is 4.5857. I did 10^4.587 which gave me the back transformed
value of 38521.

The standard error is 0.11893. I have realised you cannot back
transform it in the same way as you can with the mean. Does anyone
know the calculation for backtransforming standard error which I can
put into excel?

Many thanks.