Quote:
Originally Posted by jglasermd
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:- If you don't already have the Developer button-bar in Excel do the following:
- In Excel, click on the round Windows button (top left-hand corner) and click on the Excel Options button at the bottom
- Tick the Show Developer tab in the Ribbon option and select OK
- Click on the Developer Tab and select Visual Basic
- In the Microsoft Visual Basic window that comes up, select Insert/Module
- Copy and paste the function code provided to this window (don't forget the greater-then, less-than issues highlighted on my previous post)
- 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.