What's the relationship between these values?

[gravy834]

Hi,

I need your help to find the relationship in these values. Hopefully this is the right place for it.

So I'm trying to reverse engineer a formula and am stuck at the final hurdle. I'm determined to crack it. I've listed 10 numbers below (column A) and the associated figure that's returned (column B). I can't work out the relationship between these figures to reverse it. I'm looking for a formula so I could enter a random figure e.g. 0.015 to get it's column B figure.

Random Value	Returns
0.01	100.00000000000000
0.02	25.00000000000000
0.03	11.11111111111110
0.04	6.25000000000000
0.05	4.00000000000000
0.06	2.77777777777778
0.07	2.04081632653061
0.08	1.56250000000000
0.09	1.23456790123457
0.10	1.00000000000000

The graph for the above looks like this:

I imagine this doesn't make much sense so please ask for more details if needs be.

Thanks in advance you Excel legends!

 

[Joe4]

Welcome to the Board!

I moved this question to the "General Discussion & Other Applications" forum, as it appears to really be a math question and not an Excel equation.

It has been many years since I graduated from college as a math major, but from the looks of it, it looks like your graph is showing a hyperbola.
I would recommend doing a Google search on something like "find the equation of a hyperbola from its points", and see what you find.

 

[Rick Rothstein]

To calculate Column B values from the Column A values, use this fomula...

=1/(100*A1*A1)

If you want to calculate the Column A values from the Column B values, use this formula instead...

=1/SQRT(100*B1)

 

[joeu2004]

I'm looking for a formula so I could enter a random figure e.g. 0.015 to get it's column B figure

Sounds like an Excel question to me. Of course, many Excel calculations require some math. But that does not make it a non-Excel question, IMHO.

In any case, I would start by putting the data into an XY Scatter chart and experiment with trendlines.

I tried exponential and logarithmic first, before discovering that a power trendline fits almost identically (R^2 = 1). See the image

Display the trendline formula. It is y = 0.01*x^(-2). That is: 0.01 times 1/x^2.

So with x in A2, the formula in B2 is: =0.01 / A2^2

 

[Rick Rothstein]

For the record, what I did was display the calculation of A1*B1 and convert the result to display as a fraction. Once I saw the result, the formula was simple to calculate from there.

 

[gravy834]

Rick you little beauty, that worked perfectly. Annoyingly though I found another fault in my reverse engineered formula. Will post another question about it later if I get stuck.

 

[Rick Rothstein]

Just so you know, my first formula is identical to the last formula joeu2004 posted... what joeu2004 did is divide the 100 through and used the shortcut notation A1^2 instead of A1*A1.