View Single Post
  #5  
Old May 24th, 2011, 11:02 AM
tarquinious tarquinious is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2011
Posts: 34
Default

Quote:
Originally Posted by jglasermd View Post
the test in question uses images taken at various time intervals (in minutes): 5, 10, 15, 20, 25, 30, 45, 60, 75, 90, 105, 120. As you can see, the first several intervals are in increments of 5, while the later values are in increments of 15 -- this means that for the first several data points, I want intervals of 5 and for the latter points, I want intervals of 15. In other words, once it hits 30 minutes, I want the next data point to be at 45 and have a clean line connecting them. I dont want there to be any points at 35 or 40 minutes (because they don't exist) and I want the slope between the line segments between the longer intervals to be accurate. I have been trying to post a graphics snapshot of what I am getting so I can show you but it keeps failing.


2. I need the instructions to paste the function into a module.
1. Well I've found a "better" way of displaying your data without the silly date workaround I suggested earlier. If you graph this as a XY Scatter graph, your data should scale appropriately. Unfortunately there is no way (that I've found so far) to only display your specific times in the x-axis though and you will still get a regular 20, 40, 60, ... interval, but the data will be graphed accurately. You can, however put the correct times next to the points graphed by right-clicking on the graphed line and selecting Format Data Labels and selecting X Value from the Label Options.

2. Well funnily enough, the macro works the way you have requested I believe (if I'm reading your request right). It takes the final value of your data and divides this by 2 (it doesn't take into account any of the other values), so if your last value is 1,000, it will find the time when 500 would have occurred.

To add a new module in Excel:
  1. If you don't already have the Developer button-bar in Excel do the following:
    1. In Excel, click on the round Windows button (top left-hand corner) and click on the Excel Options button at the bottom
    2. Tick the Show Developer tab in the Ribbon option and select OK
  2. Click on the Developer Tab and select Visual Basic
  3. In the Microsoft Visual Basic window that comes up, select Insert/Module
  4. Copy and paste the function code provided to this window (don't forget the greater-then, less-than issues highlighted on my previous post)
  5. Close the Visual Basic window - the code will remain in place within the Excel spreadsheet
With the above code in Visual Basic you can now use the function in the way previously described, e.g.
=MidPoint(B1:M1,B2:M2)
...where B1:M1 holds the times of your tests and B2:M2 holds the resulting data for those times.