View Single Post
  #2  
Old May 23rd, 2011, 12:23 PM
tarquinious tarquinious is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2011
Posts: 34
Default

Quote:
Originally Posted by jglasermd View Post
Hi all:

I have a couple of things I have been trying to do with Excel, to no avail. I am developing some Excel sheets and charts to use for nuclear medicine imaging test results. Here are my problems:

1. 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. 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.

2. Once the above is figured out, I am also trying to take the calculated value of 1/2 of the maximum y-axis value, and automatically generate a value for the corresponding x-axis coordinate. this corresponds to the time where the activity present at the beginning of the test has decreased by half. This has medical significance to us. At the present time, it is measured by hand and is laborious.

Please help!
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.