Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
backtransforming log10 SE
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
backtransforming log10 SE
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
backtransforming log10 SE
"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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
backtransforming log10 SE
Nitpick....
I wrote: 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 ----- "JoeU2004" wrote in message ... "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
back transforming log10 standard error | Excel Worksheet Functions | |||
back transforming log10 of standard error | Excel Discussion (Misc queries) | |||
calculation LOG10 using relationals | Excel Worksheet Functions |