find the relation ship between the numbers

[ibro0079]

Hello,

I want to find if there is a relationship between the following numbers, and find the formula that is been used, and then use the formula to find the value in E5

Please Login or Register  to view this content.

excel.png

I know that the answer is 5 BUT I am trying to use excel to find the relationship/formula.
Happy holidays

[FlameRetired]

What logic did you use to know that the answer is 5?

[FDibbins]

And is the relationship going across - or down - or both?

[FlameRetired]

Good question, Ford. I hadn't thought of that.

[ibro0079]

The relationship is going down.
I can give you the formula used. But the idea is not just to guess but rather make excel find out. So, if I alternate the formula used and then give sample data, I am trying to make excel guess the formula used.

[FDibbins]

hmm I just noticed you have 3 input columns and then an output column, so are you sure it goes down?

And what is the formula you have?

[joeu2004]

hmm I just noticed you have 3 input columns and then an output column, so are you sure it goes down?

English is an ambiguous language. I would agree with your interpertation.

And what is the formula you have?

See the crossposted discussion at http://www.mrexcel.com/forum/excel-questions/909810-find-relation-ship-between-numbers.html

[FlameRetired]

It looks like you have your answer in the crosspost

=(C2+D2)/B2

It works at my end, and please don't cross-post.


http://www.excelforum.com/forum-rule...rum-rules.html

[ibro0079]

It looks like you have your answer in the crosspost

=(C2+D2)/B2

It works at my end, and please don't cross-post.


http://www.excelforum.com/forum-rule...rum-rules.html

Please ready my post carefully

I know that the answer is 5 BUT I am trying to use excel to find the relationship/formula. obviously I can mathematically find out the above formula but I need to utilize excel for that!

[ibro0079]

And is the relationship going across - or down - or both?

the formula used in above example is
=(C2+D2)/B2

but I need to know how can I make excel find that? I have another much harder one and I need to use excel for that

[MrShorty]

It looks like a basic regression type problem (once details like identifying the dependent and independent variables and such are understood). I use a flow chart like this for regression problems:

1) Select your desired regression equation. (y=ax+b or other polynomial, ln(y)=ax+b, ln(y)=aln(x)+b, etc.)
2) Regress the parameters for the equation
2a) If the equation selected in (1) is a "linear" equation and you want to perform a "linear least squares" regression, you can use Excel's built in LINEST() function.
2b) If the equation selected in (1) is "non-linear", then you will need to use Solver to perform your desired regression.
3) Once you have regressed the parameters of the equation, evaluate the regression.

It is not clear to me where you are stuck, but it almost seems like you are stuck on step 1 -- identify the type of relationship to use in the regression. This is often the most difficult part, and the part that Excel really cannot do for you. If you don't know what to use in step 1, then you must come up with several different equations, regress parameters for each one, then, using the results of step 3, decide which equation is the best choice.

[ibro0079]

It looks like a basic regression type problem (once details like identifying the dependent and independent variables and such are understood). I use a flow chart like this for regression problems:

1) Select your desired regression equation. (y=ax+b or other polynomial, ln(y)=ax+b, ln(y)=aln(x)+b, etc.)
2) Regress the parameters for the equation
2a) If the equation selected in (1) is a "linear" equation and you want to perform a "linear least squares" regression, you can use Excel's built in LINEST() function.
2b) If the equation selected in (1) is "non-linear", then you will need to use Solver to perform your desired regression.
3) Once you have regressed the parameters of the equation, evaluate the regression.

It is not clear to me where you are stuck, but it almost seems like you are stuck on step 1 -- identify the type of relationship to use in the regression. This is often the most difficult part, and the part that Excel really cannot do for you. If you don't know what to use in step 1, then you must come up with several different equations, regress parameters for each one, then, using the results of step 3, decide which equation is the best choice.

Thank you for your reply, I am a little lost. Can you please give me an example written in excel that explains how to use your method?

I applied whats in here to solve it but I got a different result than the expected one which made me more confused
http://educ.jmu.edu/~drakepp/spreads...o/matrices.pdf

[MrShorty]

It can be difficult to put together an adequate example for such a broad topic (it would probably take several examples to really cover the topic well). As a starting place, you might look at the examples in the LINEST() help file: https://support.office.com/en-us/art...a-fa7abf772b6d

Can you narrow down your request somewhat? Are you intending to do a linear least-squares regression or some other regression algorithm? I sometimes think step 1 (choose an equation) can be the most difficult step, but it has almost nothing to do with Excel. Have you chosen an equation for this regression?

I applied whats in here to solve it but I got a different result than the expected one which made me more confused
http://educ.jmu.edu/~drakepp/spreads...o/matrices.pdf

Do you know what the expected result is? There are errors in this document that make it difficult to know what they intended to teach with it. The problem lists a-c=0 as the middle equation, and the resulting matrix is actually singular (meaning that this system of equations has no solution). The 2nd page, where they show the Excel solution, shows a-2c=0 as the middle equation. Solving this new system of equation, I get the same result as the Drake's document. Perhaps if you upload your attempt at solving this system of equations, we can help you locate your error in implementing the matrix functions.

[MrShorty]

From your PM, it sounds like what you are really working on is in a field called cryptanalysis (https://en.wikipedia.org/wiki/Cryptanalysis ). Unfortunately, this is a field that I have no knowledge of (other than knowing it exists). Some kind of "brute force algorithm" (where one would simply try all possible combinations until the correct combination is discovered) is all I know. If no one here can contribute more, I would suggest that this question is better suited to a forum or group that is discussing cryptography.