Calculating the Angle in Excell

[blazblaz]

Can somebody help me. There is a math formula:

Tan(F)-F=B

where "B" is a known value, I need to find the "F" unknown Angle in radians
I can calculate F manually changing the value for "F" . Is there any way in Excell
to calculate automatically angle "F" base on given "B"?

 

[dsi]

You can try to use the Solver add-in to accomplish this.

 

[Zathras]

Excel already has the ability:

Menu: Tools/Goal Seek...

But first, set up the worksheet:

A1: 'F
B1: 'Tan(F)
C1: 'B
A2: 4 (Or whatever is your best first approximation)
B2: =TAN(A2)
C2: =B2-A2

Then click Tools/Goal Seek... and enter
Set cell: C2
To value: (your value for B)
By changing cell: A2

Then click OK.

You need to be careful to pre-set A2 to a reasonable first approximation. Especially in this case since there may be solutions in more than one quadrant. For example, if B=0 then there are solutions for F in both the first and third quadrants.


Incidently, for B=0 this equation is used to solve what I call the "Two Goats" problem. I would very much like to know what has led you to this equation.

 

[blazblaz]

Thanks Zathras for your advice, but it does not work.
You sugested me:

Menu: Tools/Goal Seek...
But first, set up the worksheet:
A1: 'F
B1: 'Tan(F)
C1: 'B
A2: 4 (assume 0.479348 rad, it is 20 degrees.)
B2: =TAN(A2)
C2: =B2-A2
Then click Tools/Goal Seek... and enter
- Set cell: C2
- To value: - your value for B (My value for B is already calculated in
some other field, and I don't want to enter it again, manually , I want just to
say where the calculated field is. Unfortunately I think Goal/seek required
exact value. Otherwise won't calculate, give a message: " Your entry can
not be used. An Integer or decimal number maybe required."
- By changing cell: A2
Then click OK.

For example, even I put the value manually:

Start value A=0.34906585 rad (20 degrees)
for required B=0.04043433, (I have to put manually this value), excel gives me back result of B=0.04033441, what is not equal with target value. I need accuracy in 7-8 decimals.

Try to open a sheet, enter the required value B indirectly calculated as a formula,
and you will see it doe snot work. Even you enter manually, it gives you fals result.
Why?

(By the way, this small formula is used in gear calculation for the involute of angle F)

 

[Zathras]

Needless to say, I'm very disappointed with Excel Goal Seek. I haven't run into this problem before, but then again, I haven't used Goal Seek all that much.

Try this user-defined function (If you don't know VBA, I will be happy to give you a step-by-step. The short version is Alt-F11 to bring up the VBA editor, insert a module and paste this code into the module.)

===================================================
Function AngleSolve(B As Double) As Double
' Find the value F such that F - Tan(F) = B

Dim Epsilon As Double
Dim Counter As Double
Dim F As Double

F = 0.4
Epsilon = Tan(F) - F - B
While (Abs(Epsilon) > 0.00000000001) And Counter < 1000
F = F * (1 - Epsilon)
Epsilon = Tan(F) - F - B
Counter = Counter + 1
Wend
AngleSolve = F

End Function

=======================================================

 

[blazblaz]

Thanks Zathras for your suggestion, I hope that it will work. I am doing
some programmming in Foxpro2 DOS version,and I would solve there this problem
very easy, but I am not familiar with VBA. How can I edit, and save the file after (AltF11), and how can I use this subprogram later, what is the tecnique? ? Assume that in A1 field I already have calculated value what &quot;B&quot; should be, base on some previous basic informations. Then my goal is just to find the appropriate angle and to have automatically enetered in Field A2. Entering the very basic informations in first field, will calculate my B value, will this new VIB program automatically calculate the angle, or I have to start it by clicking on some icon?
Could you give some step by step instruction, for this short program? I would realy
appriciate your help.

Thanks

 

[Zathras]

Once you have the function stored in a module all you need to do is enter the formula

=AngleSolve(A1)

in cell B1.

For all practical purposes you will have added a new function to Excel (at least in this worksheet) that acts just like TAN() or SQRT().

1. Alt-F11 opens the VBA editor.
2. Select Insert/Module from the menu bar.
3. Paste the code from my previous post into the open space. (Everything from Function... to End Function, inclusive.)
4. Go back to Excel and begin using the new function. It doesn't matter whether you close the VBA editor or not.
5. Save the Excel workbook in the normal way. The macro code that defines the new user-defined function will be saved automaticaly as part of the workbook. (As proof, you will probably be warned that the workbook contains macros the next time you open it, unless you have already switched off the warning messages.)


There is some information on VBA in the help files, but you should probably get a book to start you off.

There are generally two types of macros that you can write. The simplest is like this, called a user-defined function. The more complex can automate practically any task. Generally, they are coded between Sub and End Sub statements.

One more point. If you have ever used (or even if you have not used) the tool to paste a function, try it out. The icon looks like f(x) without the parentheses. It is also available from the menu under Insert/Function. When there are functions available in one or more modules, they also apear under the category &quot;User Defined&quot; (For example, AngleSolve.)

 

[blazblaz]

Thanks Zathras, finally it works great. I have to start to learn about VBA, I guess could
have a lot of opportunities to solve my problems with it.