Magnificent VBA Writers

[rnordquest]

I need an Excel macro that will take data in the following form, put a 2nd order curve thru it, evaluate curve at a given X and return Y:

261.5 276 286 303.5 72.2 76.4 79 84

I have many lines of data hence the need for the macro.

Thanks,

Roger

 

[jghrist]

Other than possibly to get the data into the spreadsheet, I'm not sure why you need a macro to calculate the curve fit parameters instead of using Excel statistical functions.

Do all of your rows of data have four values of X and then four values of Y?

 

[rnordquest]

I didn't know the stat functions would allow me to evaluate the equation of the line. How do I do that?

There are anywhere from 4-7 values of x and y.

 

[rnordquest]

Sorry, I didn't realize that's just what I'm using. Here is what I tried:

a = index(linest(a1:a4,a5:a8^{1,2}),1)
b = index(linest(a1:a4,a5:a8^{1,2}),1,2)
c = index(linest(a1:a4,a5:a8^{1,2}),1,3)

then

y = a x^2 + b x + c

Unfortunately the stat function has an error. I can't figure it out as it's working perfectly on another spreadsheet though I used named ranges there.

 

[MintJulep]

linest is an array function.

 

[rnordquest]

I found out that the array has to be vertical instead of horizontal. I'm just about there but I'm having trouble with the function array. This is what I want to do.

ActiveCell.FormulaR1C1 = _
"=INDEX(LINEST(cells(BegRow, 3):cells(LastRow, 3),cells(BegRow, 4):cells(LastRow, 4)^{1,2}),1)"

How do I actually write it? I want to dynamically specify the beginning and ending rows so this will work on all cases because I have varying quantities of data in each set.

 

[Mala]

Hi rnordquest
I have set up a file to do what you wish to.
This is how it will work:
1. Clear any data from previous runs
2. Paste your lines of data starting from range A1
3. Click the 'Run' button
Tha macro will parse the data and put the equation corresponding to each line of data in column A.

NOTE: Do not fiddle with the chart

The worksheet is set up with raw data so it is ready to run.
You will need to do steps 1 thru 3 for subsequent runs.

Let me know if you need further help



Mala Singh
'Dare to Imagine'

 

[pmover]

rnordquest,

please see thread:

thread770-154948

i believe this thread will help you.

if not, please advise.

good luck!
-pmover