A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

manipulating x-axis and calculating derived values from data



 
 
Thread Tools Display Modes
  #1  
Old May 22nd, 2011, 03:37 PM
jglasermd jglasermd is offline
Member
 
First recorded activity by OfficeFrustration: May 2011
Posts: 3
Default 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  
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.
  #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.
  #4  
Old May 24th, 2011, 10:17 AM
jglasermd jglasermd is offline
Member
 
First recorded activity by OfficeFrustration: May 2011
Posts: 3
Default

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:
Originally Posted by jglasermd View Post
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?
  #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.
  #6  
Old May 24th, 2011, 11:33 AM
tarquinious tarquinious is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2011
Posts: 34
Default

Quote:
Originally Posted by jglasermd View Post
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.
Excellent about the graph, though I suggest you try my later suggestion as it may be slightly better.

You will be getting the #NAME? error for one of 2 reasons:
  1. You have not yet copied and pasted the code onto a module within your excel spreadsheet
  2. You do not have macros enabled. To enable macros:
    1. Once you have the Developer tab available (see previous post) select Macro Security
    2. Ensure the option "Trust access to the VBA project object model" is ticked
Hopefully this will work for you now.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.