If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
manipulating x-axis and calculating derived values from data
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! |
#2
|
|||
|
|||
Quote:
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:
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 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. |
#3
|
|||
|
|||
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:
|
#4
|
|||
|
|||
you can ignore #1, I got it to work with your suggestion. Still trying to get your function to work; I keep getting a !NAME error.
Quote:
|
#5
|
|||
|
|||
Quote:
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:
=MidPoint(B1:M1,B2:M2) ...where B1:M1 holds the times of your tests and B2:M2 holds the resulting data for those times. |
#6
|
|||
|
|||
Quote:
You will be getting the #NAME? error for one of 2 reasons:
|
Thread Tools | |
Display Modes | |
|
|