Solve Many equations

[p30tec]

Hi everybody
How can I do this in excel??
y is my funtion and x is variable , I have y values in A2:A101 and x is Unknown
my formula is a bit complex but just Suppose that formula is y=sin(x) for simplicity

How can I solve all 100 equations easily in excel?

Example: Problem.xlsx

[alansidman]

In B2 type =Sin(A2) and then copy it down

[MrShorty]

Assuming y=sin(x) is truly representative of the actual formula, I would first suggest that the problem is a math problem rather than an Excel problem. The first thing I would do is to solve the problem algebraically/trigonometrically -- then program the result into Excel:
y=sin(x) -> x=arcsin(y)
Look up any needed Excel functions (see http://office.microsoft.com/en-us/ex...#_Toc309306715)
Formula for x become =ASIN(A2) and copy/fill down.

Since there are plenty of functions which do not have an easy inverse (such as 3rd order and larger polynomials), some cases will require numerical methods (like the Newton-Raphson method) to solve. If you are not familiar with these algorithms, it will probably be easiest (even though a bit tedious) to use the built in Goal Seek/Solver utility to find the result.

[p30tec]

In B2 type =Sin(A2) and then copy it down

No , you dont understand my quetion , I told x is unknown

[p30tec]

Assuming y=sin(x) is truly representative of the actual formula, I would first suggest that the problem is a math problem rather than an Excel problem. The first thing I would do is to solve the problem algebraically/trigonometrically -- then program the result into Excel:
y=sin(x) -> x=arcsin(y)
Look up any needed Excel functions (see http://office.microsoft.com/en-us/ex...#_Toc309306715)
Formula for x become =ASIN(A2) and copy/fill down.

Since there are plenty of functions which do not have an easy inverse (such as 3rd order and larger polynomials), some cases will require numerical methods (like the Newton-Raphson method) to solve. If you are not familiar with these algorithms, it will probably be easiest (even though a bit tedious) to use the built in Goal Seek/Solver utility to find the result.

My formula is not y=sin(x) I just told this equation for simplicity , my formula is
y = 1/2pi*(1-sin(x)/x)^(1/3)*tan(x*cos(x))

So when y=1 what is x? when y=2 what is x?
So when y is A:A what is B:B ??

I now Goal Seeker can do this just for single cell , I want do this for many cells
I know numerical methods can solve problem but I think I dont need to define these methods and excel can do this

[MrShorty]

I know numerical methods can solve problem but I think I dont need to define these methods and excel can do this

Solver/Goal Seek is how Excel implements (at least pre-programmed for us) numerical method.

One possible approach might be to treat it like a "curve fitting" or "regression" type problem:

1) Where you have "unkown", enter an initial guess for each x.
2) Add a column where you calculate y using your equation for each of those X's
3) Add a cell (or group of cells, depending on your style) that will calculate the "error" in the "regression" (=sumxmy2() is a good function for this http://office.microsoft.com/en-us/ex...in=HA102752955).
4) Call Solver and tell it to set target cell with your "error" term to a minimum by changing your X values.
5) Evaluate the result to see if Solver converged on a suitable solution.

[p30tec]

Solver/Goal Seek is how Excel implements (at least pre-programmed for us) numerical method.

One possible approach might be to treat it like a "curve fitting" or "regression" type problem:

1) Where you have "unkown", enter an initial guess for each x.
2) Add a column where you calculate y using your equation for each of those X's
3) Add a cell (or group of cells, depending on your style) that will calculate the "error" in the "regression" (=sumxmy2() is a good function for this http://office.microsoft.com/en-us/ex...in=HA102752955).
4) Call Solver and tell it to set target cell with your "error" term to a minimum by changing your X values.
5) Evaluate the result to see if Solver converged on a suitable solution.

Yes , I know but this approach is good for few unknown if I want to do this 5 step for all 100 cell's this approach is Boring
I need a way to extend this approach , but I dont know how ? because I cannot drag&drop to extend or use Array in Goal Seek

[Sam Capricci]

it is a long time since I dealt with equations but if it were me, i'd treat it like any equation i'd have to solve for X.
so if, for example I have a series of values in col A that represent y, and I know that the equation for this is y=x-2 and col B is where I want my values calculated for x, then I would solve my first equation for x, so in this case it would be that for every value i have in col A, col B should be A2+2. Then I'd copy down. So if you are looking for the variables in col B to solve for y in col A, then solve it on paper if you have to then write that formula in B2 then copy down. (and i'm sure given time and the desire I suppose I could try to solve your equation for x but not really feeling that energetic .)

[MrShorty]

Yes , I know but this approach is good for few unknown if I want to do this 5 step for all 100 cell's this approach is Boring
I need a way to extend this approach , but I dont know how ? because I cannot drag&drop to extend or use Array in Goal Seek

I am sorry if you find this approach boring. What kind of approach would you like that would be less boring? According to Frontline and Microsoft, Solver should be able to handle up to 200 "by changing" cells. Properly structured, it seems possible to structure a single Solver model to solve, in one step, for all 100 unkowns.

I have frequently written my own NR (or other root finding algorithm) directly into the spreadsheet. This can be fun, though it would be significanlty more work -- especially if this will be your first attempt at writing a spreadsheet with intentional circular references and iteration enabled.

Sometimes, I find it easier to write these kind of iterative algorithms into a programming language like VBA. Code the root finding algorithm into a user-defined function, then call the function from the spreadsheet. Would you be interested in this kind of approach?

While I personally generally find it easier to code my own NR type algorithm, some users here prefer to write a loop in VBA that will call Solver for each row and solve each row separately.

There are several different approaches we can use for a problem like this, let us know how you want to approach it, and we should be able to help you with programming that approach into Excel.

[shg]

The easiest way would probably be to write a loop for GoalSeek.

Please Login or Register  to view this content.

	B	C	D	E	F
2	y	x	target
3	0.100	1.511	0.1		B3: = PI()/2 * (1 - SIN(x) / x) ^ (1/3) * TAN(x * COS(x))
4	0.200	1.442	0.2		C3 (from GoalSeek): Input
5	0.300	1.361	0.3		D3: Input
6	0.400	1.258	0.4
7	0.501	1.069	0.5
8	0.600	-1.824	0.6
9	0.700	-1.854	0.7
10	0.800	-1.880	0.8
11	0.900	-1.905	0.9
12	1.000	-1.927	1.0

[p30tec]

Ok ,Problem Solved , thanks excel forum's members

[shg]

You're welcome.