+ Reply to Thread
Results 1 to 17 of 17

Visual Basic Help to find the slope of a line

  1. #1
    Registered User
    Join Date
    11-27-2015
    Location
    Rolla, MO
    MS-Off Ver
    2010
    Posts
    9

    Visual Basic Help to find the slope of a line

    Hello All, I am a new member to this forum and would appreciate your help. I have a relatively large data set (27,000+ rows of data and ZZ columns).

    I am trying to develop a simple macros to perform some data reduction for this large set and need assistance. I have no experience in creating a macros or VBA.

    In column A I have time in seconds. This is a continuous incremental reading take by LabView software. (i.e. 1,2,3,4...

    In column B I have a counter value. This is also continuous reading also. I have a piece of equipment connected to LabView software that records a count of Items as a function of time. The value is either 0 or 1. This counter records values that can occur any time between 1 second and 1000's of seconds, depending upon the rate. (i.e. 0,0,0,1,0,0,1,0,1,0,0,0,0,0,0,1....

    I would like to to determine an incremental counter rate or simply the frequency of counts per second for each increment . Because the recorded incremental count is not continuous, it fluctuates, and I am having difficulty determining an incremental frequency. I would like to avoid manually entering equations for each incremental time, because this is such a huge data set.

    Any input would be greatly appreciated. I have provided a small example of my excel file if that helps. I can be reached at [email protected]

    Thanks for your time and assistance.
    Attached Files Attached Files
    Last edited by magnerirish; 12-02-2015 at 07:44 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Visual Basic Help

    I can't envision your desired output. Can you provide an example?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-27-2015
    Location
    Rolla, MO
    MS-Off Ver
    2010
    Posts
    9

    Re: Visual Basic Help

    SHG,
    Thanks for the speedy reply. I've attached an excel file of what I'm trying to accomplish. I would like to find a way to calculate column C. This column is the flow rate and is a function of the change in time that occurs during the interval of column B. Again this is a huge data set (that I'm going to repeat many times) and I'd like to find an easier way to calculate column C.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-18-2015
    Location
    Madrid
    MS-Off Ver
    2016
    Posts
    95

    Re: Visual Basic Help

    Hi! Have a look at the file attached. It worked for me!

    Please Login or Register  to view this content.
    Attached Files Attached Files
    excelbat.com: free Excel tools, macros...

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Visual Basic Help

    magnerirish, welcome to the forum

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (note: this change is not optional )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    11-27-2015
    Location
    Rolla, MO
    MS-Off Ver
    2010
    Posts
    9

    Re: Visual Basic Help

    Excel Bat, Thank you so much! This is exactly what I was trying to accomplish. Now if I can figure out how to copy the macros into my data file I'll be set. Really, thank you very much! I really appreciate it.

    FDibbins, I apologize that I did not title my thread properly. I am new to this site and have no experience using VBA. I was not sure what exactly to ask for, more than some introductory VBA help. I feel that my question was a rather basic question and your advanced members were able to provide a solution. Your member Excel Bat was more than helpful and courteous. He was able to resolve my issues. That is much appreciated.

    Thanks again,

  7. #7
    Registered User
    Join Date
    11-18-2015
    Location
    Madrid
    MS-Off Ver
    2016
    Posts
    95

    Re: Visual Basic Help

    Great!

    If the answer helped you, please consider to add some reputation by clicking the little star below this post. Thanks!

  8. #8
    Registered User
    Join Date
    11-27-2015
    Location
    Rolla, MO
    MS-Off Ver
    2010
    Posts
    9

    Re: Visual Basic Help

    Excel Bat, Thanks for the help with the macro. I'm trying to modify a macro to determine flow rate of water. I've attached a file with an existing macro (Modulo1.calc) and my modified macro (ModuleCumFlow2.calc).

    I've run into an issue and I can not make my macro work. My data starts on row 25. Discharge volume values are in column F. Time values are in column R. The total discharge volume is based upon incremental volumes (column E). The total discharge volume is constant over a time interval. For example for the time interval of 3586-3602 seconds the discharge volume is equal to the discharge volume at t=3602 minus the discharge volume at t=3586. So (0.004182-0.004147)/(3602-3586)=2.2e-6 cubic feet per second. I am trying to calculate this using a macro to identify those values. This is for the calculation of the flow rate.

    Alternately if you plot the discharge volume vs time, I would like to find the incremental slope of that line, or the flow rate at the different specific time intervals. This is the flow rate. I created a second macro (ModuleCumFlow2.calcuate) using Excel Bat's as a template (Modulo1.Calculate) but it will not calculate.

    Any suggestions or assistance would be appreciated.
    Last edited by magnerirish; 12-02-2015 at 03:00 PM.

  9. #9
    Registered User
    Join Date
    11-18-2015
    Location
    Madrid
    MS-Off Ver
    2016
    Posts
    95

    Re: Visual Basic Help

    Hi! Have a look at the file attached and check if it works like you expect.

  10. #10
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Visual Basic Help

    excel_bat

    There's a link to the forum rules at the top of the page.
    I think you should familiarize yourself with 7a and 7b.

    All participants:
    Please do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc. Thanks.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  11. #11
    Registered User
    Join Date
    11-18-2015
    Location
    Madrid
    MS-Off Ver
    2016
    Posts
    95

    Re: Visual Basic Help

    Understood! Sorry about that...

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Visual Basic Help

    magnerirish and excel_bat , Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

    If you are unclear about the request or instruction then send a private message to them asking for clarification.

    All participants:
    Please do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc. Thanks.

  13. #13
    Registered User
    Join Date
    11-27-2015
    Location
    Rolla, MO
    MS-Off Ver
    2010
    Posts
    9

    Re: Visual Basic Help to find the slope of a line

    Sorry for all the confusion, I'm new to the site and learning the rules. I really appreciate everyone's help so far.

    I'm trying to find the incremental slope of a stepped line (the curvlinear portion that contacts the stepped line). My description may not be the best, so I've attached a diagram of what I'm trying to solve. I'd like to find a macro that would select the appropriate time values when the volume changes and then calculate the change in volume over that change in time (the slope of the blue lines in my attached diagram). This is for a large data set (20,000+ entries) as shown by the example red lines. The time intervals can be 1000's of seconds.

    All data starts on row 25. Volume is in column F, time in column R, and I'd like results in column t.

    Please let me know if you can be of any assistance.
    Attached Files Attached Files

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Visual Basic Help to find the slope of a line

    All data starts on row 25. Volume is in column F, time in column R,
    Not in the workbook attached to post #1; only columns A & B are used.

  15. #15
    Registered User
    Join Date
    11-27-2015
    Location
    Rolla, MO
    MS-Off Ver
    2010
    Posts
    9

    Re: Visual Basic Help to find the slope of a line

    In the notebook attached to post #1, that was a very basic example I created to ask my first question. I thought looking at incremental volume would be best, but now I see that analyzing flow rate as a function of cumulative volume works better for my data acquisition system. Please disregard the workbook in post #1.

    My question is probably better detailed in the .pdf attachment above. Sorry for any confusion.

  16. #16
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Visual Basic Help to find the slope of a line

    Does it have to be a macro? I would probably analyze this in the spreadsheet using simple worksheet functions. Analysis steps that I would probably use:

    1) Identify points 1 (i), 2 (n), 3 (p), and 4 (x).
    a) A helper column computing the instantaneous slope m(j)=[v(j)-v(j-1)]/[t(j)-t(j-1)]. If your pdf is accurate, this should result in several near 0 values, and the points of interest are the entries where m(j)>>0
    b) A second helper column to label the points of interest. Probably something like =IF(m(j)>1E-3,previous+1,previous). previous refers to the cell immediately above (maybe start at 0). 1E-3 is a small value that should represent the largest possible slope you would see along the "flat" regions.
    2) With the points of interest identified and labeled with 1,2,3,4; a lookup function should be able to return each point of interest.
    a) =MATCH(1,column_from_1b,0) will return the row#
    b) =INDEX(V_column,result_from_2a) will return the V(i) of interest
    c) =INDEX(time_column,result_from_2a) will return the corresponding time.
    3) After extracting each point of interest, then use the slope functions as outlined in the pdf.

    Does that make sense? This computer does not have Excel, so I cannot put these into a spreadsheet for you, but they should be simple enough formulas to arrange in a spreadsheet.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  17. #17
    Registered User
    Join Date
    11-18-2015
    Location
    Madrid
    MS-Off Ver
    2016
    Posts
    95

    Re: Visual Basic Help to find the slope of a line

    Hi, see the file attached. Column T shows the calculation. Hope it helps!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. New to Visual Basic.
    By mcatmull in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-24-2014, 08:20 PM
  2. Help with visual basic
    By peperammi69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-30-2012, 09:24 AM
  3. Visual basic Help
    By jrortiz2 in forum Excel General
    Replies: 1
    Last Post: 10-27-2010, 04:53 AM
  4. Visual Basic Help
    By adam2308 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-02-2009, 06:32 PM
  5. Visual Basic changes?
    By LMieth in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-22-2008, 03:36 PM
  6. [SOLVED] Can I run Visual Basic procedure using Excel Visual Basic editor?
    By john.jacobs71 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-26-2005, 10:25 AM
  7. [SOLVED] Visual Basic Help
    By John in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-23-2005, 10:15 PM
  8. Replies: 1
    Last Post: 09-13-2005, 07:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1