Hallo Kathi,
After examining the effect of arcsine transformations of percent and proportion data on
residuals, it appears as though this particular transformation is not all it’s cracked up to be. On a
broad scale, in 85-90% of the cases, the arcsine transformation had either no effect on the
residual plots, or actually made them worse.
[...]
The arcsine transformation is not likely to change the decision
that was arrived at using unaltered data, indicating that transformations may not be necessary.
aus:
https://www.mun.ca/biology/dschneider/b ... ec2010.pdfJenseits der allgemeinen Kritik wird 23x "0" und 1x "ein Wert" bei keiner Transformation normalverteilt sondern durch 23X "A" und 1x "B" ersetzt. In Deinem Fall 23x "0" und 1x "0,28". Andererseits bringt es oft wenig, sich gegen die Betreuer aufzubäumen.
Eine einfache Rechenregel für die gewünschte Transformation findet sich hier
The arcsine transformation (also called the arcsine square root transformation, or the angular transformation) is calculated as two times the arcsine of the square root of the proportion. In some cases, the result is not multiplied by two (Sokal and Rohlf 1995). Multiplying by two makes the arcsine scale go from zero to pi; not multiplying by two makes the scale stop at pi/2. The choice is arbitrary.
aus:
http://strata.uga.edu/8370/rtips/proportions.htmlDemnach brauchst Du die Excelfunktionen für die Quadratwurzel und für den arc-sinus:
https://support.microsoft.com/de-de/off ... f955c6d347https://support.microsoft.com/de-de/off ... 65e4040fdfWenn Du es damit noch nicht hinbekommst, kannst Du diese Schritt-für-Schritt-Anleitung hier finden:
In a blank column, enter the appropriate function for the transformation you've chosen. For example, if you want to transform numbers that start in cell A2, you'd go to cell B2 and enter =LOG(A2) or =LN(A2) to log transform, =SQRT(A2) to square-root transform, or =ASIN(SQRT(A2)) to arcsine transform. Then copy cell B2 and paste into all the cells in column B that are next to cells in column A that contain data. To copy and paste the transformed values into another spreadsheet, remember to use the "Paste Special..." command, then choose to paste "Values." Using the "Paste Special...Values" command makes Excel copy the numerical result of an equation, rather than the equation itself. (If your spreadsheet is Calc, choose "Paste Special" from the Edit menu, uncheck the boxes labeled "Paste All" and "Formulas," and check the box labeled "Numbers.")
To back-transform data, just enter the inverse of the function you used to transform the data. To back-transform log transformed data in cell B2, enter =10^B2 for base-10 logs or =EXP(B2) for natural logs; for square-root transformed data, enter =B2^2; for arcsine transformed data, enter =(SIN(B2))^2
und Dich dabei beziehen auf:
http://www.biostathandbook.com/transformation.htmlIch kann nicht anders als darauf hinzuweisen, dass viele Dinge mit einem echten Statistikprogramm besser und weniger fehleranfällig zu machen sind als in einer Tabellenkalkulation. Willst Du wirklich die Messwiederholungs-ANOVA in Excel rechnen?
HTH,
Bernhard
