How to find a formula with two variables

**NinjaZec** · 12-08-2010, 03:12 PM

ok here is the example
i have a table with 3 columns
Variables
i really hope i can explain it coz i have no idea how to do it or if it is possible

i have a table with 3 columns
Variables
a| b | c

there are a bunch of numbers in column 'a', 'b' and 'c'
'c' is the solution and 'a' and 'b' are what make that solution

so its like for example c= a*x + b*y +z

I am wondering how to find 'x' 'y' and 'z'

any suggestions would be great?
maybe you can at least help me what to search for

Thank you in advance.

**alansidman** · 12-08-2010, 03:20 PM

Please post a spreadsheet with sample data so that we can get a really good feel for what you are attempting to accomplish.

**NinjaZec** · 12-08-2010, 03:29 PM

ok here is the example in the attachment i want to find a formula that will calculate Solution column based on variable a and variable b

in this example i have used a formula to get the solution so there is a correlation between numbers, and there is a formula to calculate the solution from variables A and B, i want to know how can i find for the data when i dont have the formula but just the numbers

i have also made an example 2 in the sheet which says how i would do it if it where only one variable (the solution with a graph and a trend-line)

**shg** · 12-09-2010, 12:40 AM

For a power series fit, select D12:F12 and array enter

=LINEST(LN(C14:C27), LN(A14:B27))

In D14 and down,

=EXP($F$12) * A14^$E$12 * B14^$D$12

Please Login or Register  to view this content.

Not a great fit.

**NinjaZec** · 12-09-2010, 09:49 AM

Ok, your method is not great but your function is great, can you explain it in a more detail what is it supposed to do, as i don't really understand it, also how did you get the numbers in E12 and F12
Using your function but with this code:

Please Login or Register  to view this content.

i did get the exact number -0,403

Then i used the same formula as above but i swapped the places of A and B and got the exact number -0,205 which is also another number from the formula

Then i used the Factors a=-0,205 and b=-0,403 to calculate A*a and B*b which gave me some numbers then i subtracted that numbers from the solution and got another variable which is variable c=100,253

Here is the solution in the attachment.

I got the exact formula for the exact numbers... now the think is i knew the formula so i pretty much reversed engineering it, anyway i am gonna post a solution here for you and maybe someone else to learn how these functions really work.

Also here is a bit more detail explanation on how it works:

Please Login or Register  to view this content.

Using this

Please Login or Register  to view this content.

you get the number factor b

Now swat places of var A and var B

Please Login or Register  to view this content.

Using the exact formula from above on the swapped columns a and b

Please Login or Register  to view this content.

you get the number factor a

Now you have 2 factor a and b Factor a*var A+factor b*var B or short A*a+B*b this is the first part of the formula now only thing we need to do is get a constant X
Solution C = A*a+B*b + X
Now all we have to do is
X= C-A*a-B*b
now we have X=100,253

And the complete formula to get C from variables A and B

Please Login or Register  to view this content.

Now if you have any comment please do comment as i find this out by poking and trying not by knowledge of how the function works, but i guess i know know and if you read this you should to.

Also this can work for any number of variables the formula calculates a factor for the variable next to the solution