×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Magnificent VBA Writers

Magnificent VBA Writers

Magnificent VBA Writers

(OP)
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

RE: Magnificent VBA Writers

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?

RE: Magnificent VBA Writers

(OP)
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.

RE: Magnificent VBA Writers

(OP)
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.

RE: Magnificent VBA Writers

linest is an array function.

RE: Magnificent VBA Writers

(OP)
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.

RE: Magnificent VBA Writers

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'

RE: Magnificent VBA Writers

rnordquest,

please see thread:

thread770-154948: Excel Trendlines

i believe this thread will help you.

if not, please advise.

good luck!
-pmover

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources