Lookings for optimal coefficient

T

The Surfer

I have a set of numbers A1:A10.

I have a formula A1:A10 ~ x*B1:B10 + y*C1:C10 + z*D1:D10

How do I find the optimal set of x,y,z to give the result closest to A1:A10
for every B1:B10,C1:C10,D1:D10
 
N

Nikki

I think you could use a Solver.

go to tools--> add-ins--> select Solver add-in

then go to tools again select Solver
Target cell: where your current formulla is (x*B1:B10 + y*C1:C10 + z*D1:D10)
,
click in vlaue of and type in how much a1:a10
in by changing cell select where your x,y,z are located and sepreate them by
coma. now if you click on solve you should get and answer.

Nikki
 
B

B. R.Ramachandran

Hi,

Select a 3-column x 1-Row block (e.g., E1:G1), enter the following formula,
and confirm with CTRL-SHIFT-ENTER

=LINEST(A1:A10,B1:D10,0,0)

The formula returns the optimized values of the coefficients, in the order
z, y, and x.

Regards,
B. R. Ramachandran
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top