View Single Post
  #3  
Old May 24th, 2011, 03:01 AM
jglasermd jglasermd is offline
Member
 
First recorded activity by OfficeFrustration: May 2011
Posts: 3
Default

Hi:

thanks for your help! I do need some things changed, though.

1. the x-axis still doesnt look like I want it to. I'll see if I can describe it better:

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.

These tests are graphed with the above times on the x-axis and activity (in imaging count rates) on the y-axis. I have been trying to fix the x-axis so that the spacing of the data points and the slopes between them are accurate even using this irregular interval; until now all the data points look like they are 5 minutes apart and this is not true. If I try to use empty cells to fill dummy points, the curve keeps dipping back to the x-axis assuming the value is zero - continuing from the above example, at 30 minutes, there is a data point. At 35, since there is no value (because we don't measure anything at that point) the curve dips down to a 0 y coordinate, stays there until 40 minutes on the x axis, then at 45 goes back up.

Do you have any suggestions as how to do this?

2. I need the instructions to paste the function into a module. I am using Excel 2007. However, can you please change it? I actually dont need the midpoint between the max and min values, but I need 1/2 the max. For example, if my max value was 10,000 and my min was 1000, midway between those two points is 5,500. However, I need the 1/2 max or 5,000.

I really appreciate the effort...I didn't realize this was so complicated. Is there a good resource to learn more of this on my own?

Quote:
Originally Posted by tarquinious View Post
Well, this took me half the morning to crack, but I finally have it. I'm sure far smarter people of this forum will know of an in-built Excel function to resolve point #2 for you, but I failed at Stats at high school so wrote a function from scratch.

Note: I am using Excel 2007 so I hope you can translate these steps if you're on a different version.

Here are my findings:
1. This may sound a bit silly, but if you represent your times as dates, the chart will fill in the blanks for you. To do this:
  • On your chart, select the axis in question and right click on it, selecting Format Axis
  • Under Axis Options, select Date Axis, and change the Major Unit to Fixed and what you would prefer to see - e.g. 10 works well with your sample data
  • Select the Number tab
  • Click Number under Category, 0 Decimal Places and untick the Use 1000 Separator
Now your chart should show you the data with the axis scaling properly. There are most likely better ways to do this, though I couldn't find them.

2. Now the tricky one. I may have this wrong, but can correct this if you need me to. I have taken the last value of your data, divided this by 2 to get the mid-point, and then calculated where this value would appear in your Timings.

To do this I wrote the following Function. Copy and paste this to a new Module (instructions available if you need to know how to do this), and then in your spreadsheet you can use this to calculate the mid-point Time by entering the formula:
=MidPoint(range1,range2)
...where range1 is the selected cells containing your Timings, and range2 is the selected cells containing the corresponding data.
This function will give you an error if there aren't the same number of Timing selected as there are Data items.

Code:
Function MidPoint(Timings As Range, InputData As Range)
    If Timings.Count  InputData.Count Then
        MsgBox "The selected data and selected timings count have to be the same.", vbCritical, "Invalid Input"
        End
    End If
    ' Find the mid-point in the data
    HalfData = InputData.Item(InputData.Count) / 2
    TimingCount = 1
    Do Until InputData.Item(TimingCount) = HalfData
        TimingCount = TimingCount + 1
    Loop
    Input1 = InputData.Item(TimingCount - 1)
    Input2 = InputData.Item(TimingCount)
    ' Calculate where this sits between 2 values (compared to upper value)
    InputDiff = Input2 - HalfData
    If InputDiff  0 Then
        ScaleOfChange = (Input2 - Input1) / InputDiff
    Else
        ScaleOfChange = 1
    End If
    ' Find the two timings this sits between
    If ScaleOfChange = 1 Then
        Timing1 = Timings.Item(TimingCount)
    Else
        Timing1 = Timings.Item(TimingCount - 1)
    End If
    Timing2 = Timings.Item(TimingCount)
    ' Calculate mid Timing Point
    MidPoint = Timing2 - ((Timing2 - Timing1) / ScaleOfChange)
End Function
Important note:This forum drops greater-than and less-than symbols. the code above requires these:
Line 2: If Timings.Count "doesnotequal" InputData.Count Then
...where "doesnotequal" is a lessthan and greaterthan sign
Line 9: Do Until InputData.Item(TimingCount) "greaterthanorequalto" HalfData
...where "greaterthanorequalto" is a greater than and an equals sign

Let me know if you have any problems, or need the function changed at all.