Assistance with figuring out formula

fret_wimp2

Hey Guys.
Im currently trying to implement a formula in a piece of code at work, but unfortunately my maths skills are seriously lacking.
I'm hoping someone here can point me to a solution or at least the area of mathematics I need
to study.
Please let me know if my description of the problem is not clear and il try to elaborate and clarify.

I have a table of values and a percentage that I need calculate from this.

When the value is = 20,000, the percentage is 20%
When the value is = 50,000, the percentage is 15%
when the value is = 100,000 percentage is 10%
and so on.

This sequence continues indefinitely, and can go up to values of many billions.

I need to figure a formula to calculate the percent for any value (e.g. say the value is 100,000,000, what will the percentage be?), based on
the previous sequence .
Any help with this would be really appreciated.

bnt

This looks like an inverse relationship, but it's not clear what kind. Getting a formula to fit this relationship falls under the heading of "curve fitting".

If it was linear, you'd normally express it as y = k / x or x.y = k. (x and y being the two variables, and k being a constant.) However, the sample figures don't follow that relationship:
(20,000)(20%) = 4,000
(50,000)(15%) = 7,500
(100,000)(10%) = 10,000
i.e. k is not a constant, so we don't have a linear inverse relationship here.

So ... I cheat, like this: I put the three pairs of values in a spreadsheet, plotted an X-Y chart, and tried the "insert trend line") options.
An "Exponential" curve gives the best fit, and it tells me the formula is [latex]y = 0.2349\ e^{-8.6077 \times 10^{-6} x}[/latex]
(the "e" is the natural exponential function, usually EXP in programming languages.)

It's not precise: if I put in x = 20,000, I get 19.78%, not 20%
50,000 => 15.27%
100,000 => 9.93%
1,000,000 => 0.0043%
But numbers in the billions return zero, essentially.
If this seems clunky, well, yes it is, but it's the inevitable result of forcing a precise mathematical relationship on to numbers that are only loosely related.

fret_wimp2

Hey, just an FYI - this helped a lot!
As more data came to light the ability to plot a trend line and see the formula in excel really proved invaluable! This is functionality I didn't know existed before bnt's post.

Thanks a mil!

bnt

Excellent! I forgot to mention that you can tell the "best fit" using the R² number, which you can choose to display on the chart as you plot the trend line. The close R² is to 1, the better the fit to the actual data. That's how I could say that the Exponential chart was best.