Need Formula

R

Rail

New Member
Joined
Feb 3, 2004
Messages
25
Hello everyone!

I would like to figure out the formula for the given two columns of data.

The data:

X Axis = 0.5|0.75|1.0|1.5|2.5|3.5|4.5|7.5|15.0|25.0|35.0|45.0
(Numbers separated by vertical lines for ease of viewing)
Y Axis = (number's too large to list here...see below:)

1.509E-09 (this number corresponds to the 0.5 column)
1.111E-09 (this number corresponds to the 0.75 column)
8.536E-10 (ETC...)
5.001E-10
2.647E-10
1.788E-10
1.206E-10
5.231E-11
1.711E-11
1.248E-11
3.737E-11
6.706E-11

Should be a reverse exponential curve! I would like the formula to figure out the value that falls between the given numbers. Example: If I have a number (x axis) that falls between 7.5 & 15...say 10.1, I would like the formula to figure out the y-axis number...the number should fall between these to numbers: 5.231E-11 (which represents the 7.5) & 1.711E-11 (which represents the 15.0). I would like to set this formula up in Excel in a way that I could just type in a value and it will spit out the y-axis number.
I had a dos program that used to extrapolate these numbers but it's an old program and wont work on my computer.

I have about 15 sets of numbers that will have a different formula...so if you have time could you go over how to set up the data to get the formula. I want to thank you in advance for the effort on your part to help me out. Much appreciative!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
I would estimate a power regression function by graphing the data and then fitting a trend line to it, display the resulting formula (which happens to be 6*10^-10*x^-.2875) and then use cell refrences to spit out the desired results.
Another way to perform the same type of analysis would be to perform a transformation on the data. So we take the the nautral log of X and Y, run a standard linear regression using the Analysis ToolPak (or use the SLOPE and INTERCEPT functions), then exponentiate the results. So the linear regression is -9.219883294*Log(10)-.28754*Log(x)+e=Log(Y)... so then exponentiating we have 10^-9.219883294*x^-.28754+e=Y simplifying .... approx 6.0257*10^-10*x^-.28754+e=Y.

So if you were to put an x value in cell A1 and wanted the forecasted Y value in B1 you would type "=6*10^-10*A1^-.2875" in B1. If you wanted the model to be more dynamic you could type the formula =10^SLOPE(C1:C12,D1,D12)+A1^Intercept(C1:C12,D1:D12), where the Log transformations of the data are in Y=Col(C) and X=Col(D).

I think this is what your after... let me know
 
Upvote 0
Thanks for getting back to me rhmerrill! I tired your formula:

=6*10^-10*A1^-.2875

I tried putting the numbers into the A1 cell and the numbers were coming out wrong. For instance: If you put 1.0 in cell A1 the answer should be 8.536E-10, If you put 4.5 in cell A1, you should get 1.206E-10. try it! The answers to the formulas should be the original numbers used to create the graph and eventually the curve. Huh! Don't know why it's not working. The numbers I'm getting with your formula are as follows:

1.0 = 6.000E-10 (should be 8.536E-10) - see original post
4.5 = 3.894E-10 (should be 1.206E-10) - see original post

rhmerrill, thanks again for your valuable time. I hope we can get this to work. Thanks again and hope to here from you soon!
 
Upvote 0
hi!
Im not sure if this is what you want!
Book1
ABCDE
26xy
270.51.509E-09lookforans
280.751.111E-092.52.647E-10
2918.536E-10
301.55.001E-10
312.52.647E-10
323.51.788E-10
334.51.206E-10
347.55.231E-11
35151.711E-11
36251.248E-11
37353.737E-11
38456.706E-11
Sheet3



What if the number you look is not in the X values?
will you have to interpolate?
 
Upvote 0
95% off the numbers are NOT in the set and will have to interpolate. But a good way to check the formula is to input a known number from the x value to see if it comes out right! So, yes I'll have to interpolate. Just trying to find the right formula...rhmerrill might have it but it doesn't seem to be working for me, I'll have him try the known values for x to see if he can duplicate the number's I gave him for y. Thanks though!

Rail
 
Upvote 0
My data looks like this

A B
0.50 1.509E-09
0.75 1.111E-09
1.00 8.536E-10
1.50 5.001E-10
2.50 2.647E-10
3.50 1.788E-10
4.50 1.206E-10
7.50 5.231E-11
15.0 1.711E-11
25.0 1.248E-11
35.0 3.737E-11
45.0 6.706E-11


I used the chart to graph the A & B columns. Then used a trend line to extrapolate data used that might not be in column A. The formula that Excel showed was y=2E-09e to the power of (-0.4073x). How do I use this formula to give me numbers that aren't in the A column...like 4.63, 7.04, 1.22 etc.... I need the formula to extrapolate these. For instance, I would like to put this formulsa in cell d1 so if I put a value in c1 it will show the exrapolated number. Thanks again!

Rail
 
Upvote 0
Just plug in the formula like you did mine... If you look at your data you will notice that they don't follow a nice smooth mathematical formula, so a using a trend line estimates a "best fit" if you will. Excel uses Oridinary Least Squares to minimize the distance between the line and your points. The curve I gave you was not a very good fit, but you specified that you wanted an exponential curve. If there are more data points then it would be helpful to include those as the quality of fit will improve ... what program did you use to extrapolate before?
 
Upvote 0
Oops, what chart graph option should I use, line or scatter? The 2 types give me two different formula's?????
 
Upvote 0
and for the trend line....power? If so, this is the formula I get!

y=6E-10x power -0.9627 close to what you got origianlly rhmerrill but still not working. The numbers are still off! Any ideas!

Rail
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Need help with my formula
Replies
0
Views
461
kirkley08
K
S
  • Question
Need Help - Formula
2
Replies
16
Views
1K
ststern45
S
L
  • Solved
Formula help with sumifs based on multiple criteria and between date
Replies
4
Views
151
etaf
etaf
N
  • Solved
Sum total between bottle refill times
Replies
5
Views
284
Georgiboy
Georgiboy
G
  • Solved
Need Single Formula
2
Replies
15
Views
676
gnaga
G

Forum statistics

Threads
1,215,065
Messages
6,122,945
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top