Afia Kona
If you want to know about how to arcsine transform data in Excel, this article is for you. Here, we will walk you through 4 handy and effective methods to do the task smoothly.
Download Practice Workbook
What Is Arcsine Transform?
The incorporation of the arcsine and square root transformation functions is known as the Arcsine Transformation. When X is a real number from 0 to 1, Arcsine Transformation comes along as ASIN(SQRT(X)). The Arcsine Transformation assists in handling and stretching out the data points of probabilities, proportions, and percents if they are in the range of 0 to 1.
4 Methods to Arcsine Transform Data in Excel
The following table has Event and Probability columns. To arcsine transform data of the Probability column, we will use 4 effective methods. Here, we used Excel 365. You can use any available Excel version.
1. Using ASIN Function to Arcsine Transform Data in Excel
In this method, we will use the ASIN function to arcsine transform data.
Steps:
- First, we will write the following formula in cell D5.
=ASIN(C5)
Formula Breakdown
- ASIN(C5) → The ASIN function returns the arcsine or inverse sine of a given number.
- ASIN(0.5) → becomes
- Output: 0.523598776
- After that, press ENTER. Then, we will see the result in cell D5.
- Afterward, we will drag down the formula with the Fill Handle tool.
Finally, you will see the arcsine transform data in the Arcsine Transform column.
Read More: How to Transform Data to Normal Distribution in Excel (2 Easy Methods)
2. Use of DEGREES, ASIN, and SQRT Functions to Arcsine Transform Data in Excel
Here, the following table has a Percentage column. To arcsine transform data of the percentage value, we will use the combination of DEGREES, ASIN, and SQRT functions.
Steps:
- First, we will type the following formula in cell D5.
=DEGREES(ASIN(SQRT(C5/100)))
Formula Breakdown
- C5/100 → divides the value in cell C5 by 100.
- Output: 0.005
- SQRT(C5/100) → The SQRT function returns a square root of the positive numbers.
- Output: 0.07071067812
- ASIN(SQRT(C5/100) → returns the arcsine or inverse sine of a given number.
- ASIN(0.07071067812) → turns into
- Output: 0.0707697366622136
- DEGREES(ASIN(SQRT(C5/100))) → converts radian values into degrees.
- DEGREES(0.0707697366622136) → becomes
- Output: 4.054807228
- Explanation: Here, 4.054807228 is the arcsine transformation of the Percentage value.
- After that, press ENTER. Then, we will see the result in cell D5.
- Afterward, we will drag down the formula to the rest of the cells with the Fill Handle tool.
Finally, you will see the arcsine transform data in the Arcsine Transform column.
3. Applying ASIN & SQRT Functions for Arcsine Transformation of Values Ranges from 0 to 1
Here, the following table has a Probability column and we can see that all the values are between 0 and 1. Now, we will use the combination of ASIN and SQRT functions to arcsine transform data of the Probability column.
Steps:
- First, we will type the following formula in cell D5.
=ASIN(SQRT(C5))
Formula Breakdown
- SQRT(C5) → The SQRT function returns a square root of the positive numbers.
- Output: 0.707106781186548
- ASIN(SQRT(C5)) → The ASIN function returns the arcsine or inverse sine of a given number.
- ASIN(0.707106781186548) → becomes
- Output: 0.785398163
- Explanation: Here, 0.785398163 is the arcsine transformation of the Probability.
- After that, press ENTER. Then, we will see the result in cell D5.
- Afterward, we will drag down the formula with the Fill Handle tool.
Finally, you will see the arcsine transform data in the Arcsine Transform column.
Read More: How to Get and Transform Data in Excel (4 Suitable Examples)
4. Arcsine Transformation of Values Greater Than 1 by Using Combined Functions
In the following table’s Data column, we can see that the values are greater than1. Here, we want to arsine transform data of the Data column. However, we can only arcsine transform data when the values are between 0 and 1.
In that case, to arcsine transform data of the Data column, we have to divide all the data values with the maximum value. After that, we will apply the combination of ASIN and SQRT functions.
Steps:
- First, we will write the following formula in cell C5.
=B5/MAX($B$5:$B$11)
Formula Breakdown
- MAX($B$5:$B$11) → returns the largest value from cells B5:B11.
- Output: 23
- B5/MAX($B$5:$B$11) → divides the values of cell B5 by the Maximum value 23.
- Output: 0.304347826
- After that, press ENTER. Then, we will see the result in cell D5.
- Afterward, we will drag down the formula with the Fill Handle tool.
- Afterward, we will type the following formula in cell D5.
=ASIN(SQRT(C5))
Formula Breakdown
- SQRT(C5) → The SQRT function returns a square root of the positive numbers.
- Output: 0.551677284367371
- ASIN(SQRT(C5)) → The ASIN function returns arcsine or inverse sine of a given number.
- ASIN(0.551677284367371) → becomes
- Output: 0.584373897
- Explanation: Here, 0.584373897 is the arcsine transformed value of the outside range of 0 to 1.
- After that, press ENTER. Then, we will see the result in cell D5.
- Afterward, we will drag down the formula with the Fill Handle tool.
Finally, you will see the arcsine transform data in the Arcsine Transform column.
Things to Remember
- In order to arcsine transform data, the values of the probabilities, percentages, and proportions must be between 0 to 1.
- If the values are greater than 1, we have to divide the values with the maximum data.
- Here, you can’t use any negative value in the SQRT function otherwise it will show #NUM! error.
Practice Section
In the practice section of your sheet, you can practice the explained methods.
Conclusion
Here, we tried to show you 4 methods to arcsine transform data in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.
I do think, in these calculation(=DEGREES(ASIN(SQRT(C5/100))), you have to put 50 in the excel cell. Not 50%. This is a grave mistakes.
Dear Tanzir,
Thank you for your comment.
Arcsine transform is done for real numbers ranging from 0 to 1.
In the formula, DEGREES(ASIN(SQRT(X/100))), X indicates the percent value to be transformed. Therefore, when we put 50% in the formula it actually becomes 0.5. As a result, we can Arcsine transform the data.
However, if we put 50 instead of 50% then Arcsine transform is not possible.
Regards
Afia Aziz Kona
Hello Afia,
Thank you for responding. The real numbers should be X. Yes. However, you must convert it to proportions (divide by 100), which are already included in the formula DEGREES(ASIN(SQRT(X/100)). As a result, if you enter 50%, which is already 0.5, it will be divided by 0.5 by 100 once more. Please look at this book where the arcsine transformation of percentage data is done, as I said you and how it differed from your data.
Gomez and Gomez, 1984. Statistical procedures for agricultural research. John Wiley & sons,306-308.
Thank you
Tanzir Hossain