Lookings for optimal coefficient

[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

[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


"The Surfer" wrote:

> 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
>
>
>
>

[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




"The Surfer" wrote:

> 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
>
>
>
>