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