spreadsheet problem

[brn]

hi to all I would have a trivial problem with excel (I'm not a dragon):

I am making a spreadsheet to simulate the opening of a lid by means of cylinders.
I wanted to make an excel sheet that calculated the various parameters for each degree of opening of the cover (or more correctly for each advance of the cylinder).

I'm stuck at the software level:

data for example n points that simulate the race of the cylinder, for everyone I would like to calculate the position given by the system given by the closing equations, then from here calculate the forces

a*cos(alpha)-b(t)*sen(beta)=c*cos(gamma)
(a)alpha)-b(t)*sen(beta)=c*sen(gamma)

with alpha and beta incognite. I do not find analytical method to solve the system ( but I am very rusty) therefore I wanted to solve it point by point in numerical way, through the sum of the squares it is trivial to arrive to a problem solved with the solver.
but here's the problem: in an excel sheet how can I automatically solve non-linear systems? ?
how do I avoid setting the resolver n times?
for a similar problem I had set a macro but were 5-6 points not 100.
with matlab or similar would be a trivial problem with a cycle and a function but with excel I do not know how to solve it
Thank you very much

 

[biz]

a design would help us.
would you like to find the coordinates (x,y) of a point in the Cartesian plan? You mean that?

 

[brn]

My problem is software/analytical level not to break forces etc., I have to solve in a sheet excel n systems like the one above (with n the points where I go to evaluate the position of the system) in bold are represented the unknown. being non-linear systems and having only excel available, I don't like to automatically solve everything in a spreadsheet. in matlab I would have done a cycle for each point and solved the non-linear system. I have no idea how to do this without applying the solver.
I hope I'm clear.

 

[Legs]

If the equations you wrote are correct then you should note that your is a linear system in cos(alpha) and sin(beta).
maybe you can see it better if we put: x = cos(alfa) and y=sin(beta).
a*x-b*y = c*cos(gamma)
a*c*x-b*y = c*sin(gamma)
or in matrice form:[math]\begin{bmatrix} a & -b \\ a*c & -b \end{bmatrix} * \begin{bmatrix} x \\ y \end{bmatrix}=\begin{bmatrix} c*cos(gamma) \\ c*sin(gamma) \end{bmatrix}[/math]as you can see it is a system of immediate resolution (change the terms on the main diagonal, change sign to the other two terms, divide by the determining and then multiply everything by the known vector=>ottieni x and y).
once found x and y it is then immediate to find alpha and beta.
Note that electronic sheets also have commands to directly solve linear equations systems if you don't want to solve it by hand (look in online help).

 

[brn]

Thank you very much, you are double right

1) the written system is clearly linear and solveable with a change of variables as you did (we called it so in high school)
2) the initial premise is correct: if the equations you wrote are correctI realized I was wrong to transcribe equations from my calculations (vergogna a me)
the correct system is:

a*cos(alpha)-b(t)*cos(beta)=c*cos(gamma)
a*c*sen(alpha)-b(t)*sen(beta)=c*sen(gamma)

and that doesn't seem linear at all.
I apologize for the above error

 

[Legs]

I honestly imagined that breasts and things had been exchanged.
in this case it is however possible to solve the problem simply by overturning it.
a way could be to build a table.
For example, you could put alphas in the first row at the top and beta in the first column on the left.
define a step, maybe every 1 or 5 or 10 degrees. depends on the accuracy sought.
In the internal cells you can then refer to the corresponding alpha and beta values.
define an objective function.
For example:[A*cos(alpha)-b(t)*cos(beta)-c*cos(gamma)]^2+[A*c*sen(alpha)-b(t)*sen(beta(c)
If you want to extract the square root so much to give it a geometric meaning (you might want to use absolute values instead of elevating to the square).
basically the alpha and beta values you are looking for minimize the objective function (in theory they should cancel it).
if you fill your table with this function the most likely values of alpha and beta are those that match the minimum value of the objective function.
Of course, it's an estimate. It's an approximation.
but in this way with simple search operations in the table you can estimate a good approximation of alpha and beta (you do not need to calculate the gradient to see how to move to look for the best solution).

attention: the problem could also be solved directly with excel optimization tools. in the end we have transformed the equation system into an optimization system of an objective function. constraints will also be imposed (of limitations on alpha and beta values) to limit the search. I remember using many years ago libre office extensions that allowed this type of application and these things are definitely inside excel.
It also recalls that excel is now programmable in python (a problem like this can be solved with scipy tools but not if it is usable within excel programs). also excel is also programmable in vba (at least I think).

 

[brn]

Clear. However, that's not the problem: I'll attach a sheet screen to make you understand:
see the list of points where I have to solve the system. on the line (which represents the point where I am evaluating the system) I entered the sum of the squares to cancel the resolver(the objective function) to find the location. by imposing the resolver to cancel that objective function by varying the two corners that I have to find the solution finds it immediately.
but how do I find the solution for the other points you see below, without setting the solver for each line? (then obvious to optimize the accounts as an initial condition I give the solutions of the previous line).
If there was a function instead of the solver it would be easy.

 

[biz]

It is not clear to me what you want to find with the equation system, but, so to the eye if you are interested in the corners do not first use the carnot theorem and the seni theorem, being known the sides a,b and c?

 

[Legs]

I don't think I've ever faced multiple solver use.
perhaps you can solve with the use of scenarios. But here you have to study it.
on youtube I saw that this video is present:
search multiple lens, many different rows and columns - excel tutorial

Take a look.
or follow the biz board if applicable.

 

[brn]

excuse the delay in the answer but it is a challenging period. . .

actually the system can be solved with carnot and breast theorem, initially I had tried
but the corners I found of the triangle were not exactly what I needed and I had abandoned this attempt mistakenly thinking that the solution of a non-linear system of that type could not have a closed solution. in reality with a little change not immediate the corners sought are found. . .
Thank you so much for bringing me back to the right path.

I'm just quieter trying to solve the system using the video indicated by legs

Thank you all!