Posts by mike015

    Re: Conditional Slope Of Range

    Quote from Domenic



    Hope this helps!

    This works well.

    Quote from shg

    Your data is noisy (plot it), and your recent formula just takes the slope across the end-points of the segment, where the SLOPE function does a linear regression on the interval. I don't think you'll be happy with that solution.

    I agree that my current data is extremely noisy. This is not typical of my data. We are using a very low sample rate without any subsampling. generally there is not much difference in the values near the points. using the regression could be benefitial but I don't know that it would be necessary.

    Thank you

    Re: Conditional Slope Of Range

    Thank you for the solution, It does work.

    I came up with a way to do it without having to rearrange the columns, I have other data in those columns.

    by defining the following as named ranges

    Rise1 =LOOKUP(5.5,'830'!$C$2:$C$115,'830'!$F$2:$F$115)
    Rise2 =LOOKUP(5.5,'830'!$C$2:$C$115,'830'!$C$2:$C$115)
    Run1 =LOOKUP(4.5,'830'!$C$2:$C$115,'830'!$F$2:$F$115)
    Run2 =LOOKUP(4.5,'830'!$C$2:$C$115,'830'!$C$2:$C$115)

    and then placing this formula in the worksheet.


    I am trying to find the slope of column F when Column C is between 4.5-5.5

    I can do this by doing four seperate lookup formulas and having the slope function referring to the results.
    When I try to combine the four cells into a single cell Excel gives me an error.


    Attached is an example of the data, the functioning slope when spread over four cells, and my attempt at combining the formulas(currently with an ' at the beginning to prevent the error)

    Re: Matching Time Range

    I have continued to work on this, I have come up with a formula that is working. I put this into sheet1!q2 and filled down. values within range show up and those out of range show as #NA.
    .01 comes to around 14min.


    I am open to any suggestions on how to clean this up or convert this to VB.
    I am planning on pasting this formula in using a xlup then copying the results pasting as values.

    I am looking for a way to match a time (Sheet1!P2) within +/-15 min, adjustable, in the range (Sheet2!B:B)and report the corresponding (Sheet2!A_) value. The final result is to be placed at (Sheet1!Q2).
    This is to be applied to all the values in Sheet1!P_

    I was thinking of using a nested Index(match) combination but I am unable to get a good result or set the +/- 15 min time span.

    If there is no match I will end up deleting the row in sheet1

    Thank You

    I found a way to import some data from some csv files, and it works well for me. I don't want to keep the option to refresh the data.
    I can go into each tab created from each import and and uncheck the box
    Data>Import External Data>Data Range Properties>"save query definition" that removes it for me.
    Is there a way to do that with code. It doesn't work with the macro recorder.

    [Blocked Image:]

    I am trying to move to the end of one data set, then move down one row.
    What is the line of code that lets me move down one row?
    I am sure it is very obvious, but I cannot find it.


    Re: resample data

    Thanks Ger Plante,

    Your file works well, Even though I had done loops in school. I haven't been able to fully grasp how they work. One day I will look at them and it will all make sense. today is not that day.

    The files do all have a 2second interval. I have a second logger that ran a 5second interval, and naturally didn't have an issue. I will be changing the future recordings at a 3+interval. Trial and error, Ididn't know they would be recording full days, I thought it was going to be one shift a day. I realized I had the rate too high the first day but didn't want to change it until the run completed.

    21,500 rows would be fine.

    Excel or VBA, as long at it works. I'm not picky that way.

    The B column was not significant. It is generated by the logger.

    I have several files of data that are from a data logger. The data is broken up by day. Each day has roughly 43000 rows of data, at its sample rate. This has made charting the data a nuisance. Is there a way to resample the data so it fits in the 32000 points excel can chart? In the future I will set a sample rate that will keep the number of points below the 32K per series.

    I would like to be able to have the total number of points reduced by averaging the data not by simply deleting one of every four rows.


    Re: Creating an External VB Application?

    That at got me pointed to what I was wondering. I found this online article. It gave a quick once over of using automation.

    I will have to do more reading before delving too far into this project.

    Thanks Bob.



    Re: Creating an External VB Application?

    None taken, I agree that working on the user forms would be my first step.

    My first thought was to find how transparent of a process it is to take an excel vb and make it into a standalone. I have not tried to do it yet. If there are differences between the coding than I would rather begin in the standalone and debug once than to perfect the excel version and find all of the cell references and formulas are now different due to running outside of excel.

    I do not know if externally run VB applications will play nice with excel data, I am expecting that all the formulas that I have now in excel will have to be written in the code.

    If writing external code is not practical I will change my plans. I am hoping it is.

    My excel VB code is at the point where it is powerful enough to do what I need it to. the problem is that it is not very user friendly.

    I had planned to create some user forms to have a cleaner user interface, but I haven't had all that much success with it (and haven't been able to dedicate significant time to it yet either)

    Rather than just having a form running in excel my thought was to go all the way and write the code as a stand alone VB application. Having the application pointing to a excel file for the raw data and dumping the manipulated data into a seperate file.

    Has anyone done this before?

    Is there a way to have a streaming video (*.asx) play in powerpoint?

    I tried to add it by clicking the 'add movie from file' button, it said it needed to be stored locally.

    If I click insert, object, windows media player. I can add a media player in the presentation, but I would figure that it would need to have the web address added in VB but don't know how.