How to Arcsine Transform Data in Excel (4 Handy Methods)

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.

How to Arcsine Transform Data in Excel


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.

How to Arcsine Transform Data in Excel

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

How to Arcsine Transform Data in Excel

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.

How to Arcsine Transform Data in Excel


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.

How to Arcsine Transform Data in Excel

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

How to Arcsine Transform Data in Excel

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.

How to Arcsine Transform Data in Excel


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.

Afia
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo