Solving implicit equations in excel

**harshavardhank15** · 06-15-2018, 06:00 AM

I have an implicit equation of two angles. I need to find one angle when I give the other angle as input. Can this be solved by using excel. If so, how?

(SIN(CL))^2 +( SIN(CL))^2 - ((0.5)*(SIN(2*CL))*(SIN(2*CR))) - (2*((SIN(CL))^2)*((SIN(CR))^2)) - (SIN(CL-CR))^2 = 0.

This is the equation and I need to find CR when I give CL as input

**Pauleyb** · 06-15-2018, 09:22 AM

Rather than doing the algebra/trig to try to solve for CR in that equation, I'd recommend using solver.

**harshavardhank15** · 06-16-2018, 01:05 AM

I know that solver has to be used but I need help in using solver to solve this particular equation.

**Alf** · 06-16-2018, 08:42 AM

One possible way of setting up solver

calc_ang.jpg

where B2 is the given CL value and B4 is the CR value calculated by solver but, but but. If you run solver with no value in B4 you get a result less than 1 so you have to convert that to an angle or you could give B4 a starting value greater than 1.

Problem is that this function oscillates so by changing the starting point i.e. value of B4 you will find a number of solutions and the most obvious one where you don't need solver is to set angle CL equal to angle CR.

Alf

**joeu2004** · 06-16-2018, 11:48 AM

Originally Posted by Alf

Attachment 578438
where B2 is the given CL value and B4 is the CR value calculated by solver

I believe there is a design error. In your example, B2 is 40. I suspect that means 40 degrees. But you use SIN(B2). That should be SIN(RADIANS(B2)). Similarly for the other SIN usages.

Alternatively, I recommend the design in attached Excel file (below). We enter CL in degrees in D2. It is converted to radians in C2; and we use SIN(C2), for example.

Solver calculates CR in radians in C3. It is converted to degrees in D3. So D3 is the solution (CR) in degrees. See the attached JPG file (below).

However....

Originally Posted by harshavardhank15

(SIN(CL))^2 +( SIN(CL))^2 - ((0.5)*(SIN(2*CL))*(SIN(2*CR))) - (2*((SIN(CL))^2)*((SIN(CR))^2)) - (SIN(CL-CR))^2 = 0
This is the equation and I need to find CR when I give CL as input

I suspect the equation is incorrect, perhaps the victim of overuse of parentheses, which obscures the design.

If the equation were correct, the first two terms are identical, and they can be combined as 2*SIN(CL)^2, or 2*(SIN(CL)^2) if you insist.

The simplest rewrite of the equation is:

=2*SIN(C2)^2 - 0.5*SIN(2*C2)*SIN(2*C3) - 2*SIN(C2)^2*SIN(C3)^2 - SIN(C2-C3)^2

or

=2*(SIN(C2)^2) - 0.5*SIN(2*C2)*SIN(2*C3) - 2*(SIN(C2)^2)*(SIN(C3)^2) - SIN(C2-C3)^2

In any case, I suspect the equation is wrong, again perhaps the victim of the overuse of parentheses.

It appears that CR = CL for all values of CL between 0.2 and 83 degrees.

(I suspect that differences for other angles between 0 and 90 degrees are due to computational anomalies of the Excel SIN function.)

What is the source of the equation?