Inverse Sine calculation with formualr1c1 function

[bradyj7]

Hi there,

I want to find the inverse sine (sin^-1) of numbers in a column to get a column of angles next to it. I cant figure it out. This is what I have so far. The values are in the previous column.

    Range("m1").Value = "Angle of road"
    Range("m2").Value = 0
    Range("m3:m" & LastRow).FormulaR1C1 = "=Atn( rc[-1] / Sqr(rc[-1] * rc[-1] + 1))"

I got the inverse sine function from this page http://www.vbforums.com/showthread.php?t=161043

When I compile it, there is no error but this #NAME? is returned in the cells

Thank you

 

[GlennUK]

There is no ATN function. Are you trying to use ATAN? Same for SQR .... SQRT maybe?

 

[bradyj7]

To be honest I don't know what ATN is, I just took it form the link above. Would you know how to calculate the inverse sine of a number in vba.

 

[GlennUK]

Why don't you just use the ASIN worksheet function?

 

[bradyj7]

Do you mean this

Range("m1").Value = "Angle of road"
    Range("m2").Value = 0
    Range("m3:m" & LastRow).FormulaR1C1 = "=ASIN (rc[-1])"

I tried this but it just returns the value in the previous cell?

Thank you

 

[GlennUK]

What values are in your cells? Show the first few values in column M.

 

[bradyj7]

What values are in your cells? Show the first few values in column M.

Hi,

They are all decimals, the first few values are 0.039999999, -0.023441861, 0.025200002, 0.022941175

Cheers

 

[GlennUK]

Your values are all very closr yo zero, that's what I'd expect as the results. See some more examples here:

Excel Workbook
	A	B
8	0.6	0.643501
9	-0.823	-0.96667
10	0.92	1.16808
11	0.95	1.253236
12	0.995	1.470755
Sheet12

What exactly were you expecting for those values anyway?

 

[bradyj7]

Oh I think your right. I'm working in degrees not radians. So when I was doing sin-1(0.6) on my calculator (which is in degrees) was getting 36.87 degrees as the angle. But sin-1(0.6) in radians is 0.6435 as above. I just need to convert degrees to rads.

Cheers

 

[pgc01]

Do you mean this

Range("m1").Value = "Angle of road"
    Range("m2").Value = 0
    Range("m3:m" & LastRow).FormulaR1C1 = "=ASIN (rc[-1])"

I tried this but it just returns the value in the previous cell?

Thank you

Hi bradyj7

Please notice that for very small values of x, sin(x) and asin(x) are approximately equal to x.

For ex., for your first value, if you are showing in the cell 3 decimals, asin(0,04) will display 0,04

If you try a bigger vakue, for ex. 0.4 you'll see the result 0.412. Here, since it's a bigger value you can already see the difference.

For the values you posted, {0.039999999, -0.023441861, 0.025200002, 0.022941175}, they are close to zero and so expect asin() to show a value close to the argument. If you are displaying 3 decimals you'll see no difference in the argument and the result of the function.