backtransforming log10 SE

[fluff]

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.

 

[Bernie Deitrick]

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

 

[JoeU2004]

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 -----

 

[JoeU2004]

Nitpick....

Similarly, if N1:N3 are log normal

It's a little hard to prove that 3 data points fit a normal distribution
. Of course I had a larger data set in mind.



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

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 -----

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