Automatic charting of any medical cell data when clicked - is it possible?

[fsgregs]

Hi. I am a true novice in Excel and if the question has already been answered, please forgive the repeat. I have created a log of all my medical blood and urine tests over time. In total, the log contains about 30 different tests, such as Glucose, Bun, HDL, Total Cholesterol, etc. Entries are made by date, which tracks downward in rows, with the appropriate values entered in each column. For example, entries might look like this:

Date ...... Glucose ....... BUN
..................plot ........... plot

3/4/2016 .....88 ............ 21
5/6/2016 .... 91 ............ 28 ....etc.
2/3/2017 .....84 .............19
5/12/2017 ...79 .............23

Medical data is of limited value unless trends can be spotted. As such, I would like to be able to set up a formula/function to automatically chart/plot/graph the data in a given column over time. When I click on the word, "plot" under Glucose, for example, the function in that cell is called, and does the following:

a) Examines all the values in the Glucose column and finds the lowest value, and the highest value (e.g. - 79,91)
b) Creates a line graph with those two values +/- 20% as the min and max values of the Y axis
c) Examines the dates in the Date column and finds the earliest value and the most recent values (perhaps dates can be converted to Julian numbers via a 2nd sub-routine for ease of use)
d) Sets those two data points +/- 20% as the min and max values of the X axis
e) Grabs the text "Glucose" and sets it as the Y axis title
f) Grabs the text "Date" and sets it as the X axis title
g) Plots the values in that column as a line graph, that pops up as a sub-menu automatically


In short, by clicking on the word, "Plot" under any blood test results, a line graph of that data will automatically be created and pop up, with the x and y axis automatically created by the sub-routine. I presume a few other parameters in the plot would also have to be specified in advance (rounding, etc.)

This would be such a useful, elegant spreadsheet that it could be posted publicly for free for everyone to use. Can such a sub-routine/function formula be created and inserted into Excel? If so, could someone point me toward the info I would need to do so, or be willing to give me some pointers?

Thanks in advance

Frank

 

[fsgregs]

Worf:

The plot works well for data sets that are all larger than 1. However, if the data set contains values between 0 and 1, some of the points plot below the axis line. Here is an example:


-- removed inline image ---


The data set is on the right. As you can see, the axis does not start at 0, but starts at 1, which results in two data points plotting below 1. Strangely, if I change one value in the data set from 0.8 to 0.6, and replot the graph, now the plot does start at 0, and the data points all plot well. I don't know why it did that. Here is the result. Maybe it has something to do with rounding??


-- removed inline image ---


If this can be fixed, I think the macro will be right on target. Thanks again so much for your effort. Once this is finished, do you know any website where our combined "Medical data log" could be posted/offered for everyone to use? I can upload it to a public cloud site. Lots of people would find it really useful.

Frank

 

[fsgregs]

Worf:

The plot works well for data sets that are all larger than 1. However, if the data set contains values between 0 and 1, some of the points plot below the axis line. Here is an example: https://1drv.ms/i/s!AlxY_utkDRE6hmeHN3AJfhQZPfk6

The data set is on the right. As you can see, the axis does not start at 0, but starts at 1, which results in two data points plotting below 1. Strangely, if I change one value in the data set from 0.8 to 0.6, and replot the graph, now the plot does start at 0, and the data points all plot well. I don't know why it did that. Here is the result. Maybe it has something to do with rounding??

https://1drv.ms/i/s!AlxY_utkDRE6hmZR1VfOy4BrprZF



If this can be fixed, I think the macro will be right on target. Thanks again so much for your effort. Once this is finished, do you know any website where our combined "Medical data log" could be posted/offered for everyone to use? I can upload it to a public cloud site. Lots of people would find it really useful.

Frank

 

[Worf]

Hi Frank

New version below.
This thread is public and searchable by Google, so people looking for something like it will eventually get here.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim L$, lr%, co As ChartObject, ser As Series, maxs#, mins#, step#, minv#
L = Replace(Split(Target.Address, "$")(1), ":", "")
 If Target.Row = 8 And Evaluate("=count(" & L & ":" & L & ")") > 0 And Trim(Target) = "plot" Then
    If Me.ChartObjects.count > 0 Then Me.ChartObjects.Delete    ' get rid of previous chart
    lr = Me.Range(L & Rows.count).End(xlUp).Row
    Set co = Me.ChartObjects.Add(Left:=Me.[b2].Left, _
    Width:=Me.[B1:L1].Width, Top:=Me.[c11].Top, Height:=Me.[A2:A18].Height)  ' position & size
    With co.Chart
        .HasLegend = False
        With .Axes(xlValue)
            maxs = Round(Evaluate("=max(" & L & ":" & L & ")") * 1.2, 0)
            minv = Evaluate("=min(" & L & ":" & L & ")")
            mins = Round(minv * 0.8, 0)
            .MaximumScale = maxs
            .MinimumScale = mins
            If minv < 1 Then .MinimumScale = 0
            .HasTitle = True
            .AxisTitle.Text = Me.Range(L & "6")             ' Y values
            step = Round((maxs - mins) / 10, 0)
            .MajorUnit = IIf(step <> 0, step, 1)
        End With
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = Me.[b6]          ' dates
        Set ser = .SeriesCollection.NewSeries
        With ser
            .Values = Me.Range(L & "10:" & L & lr)          ' Y values
            .XValues = Me.Range("b10:b" & lr)               ' X values
            .ChartType = xlLineMarkers
            .ApplyDataLabels
            .DataLabels.Position = xlLabelPositionBelow
        End With
    End With
End If
End Sub

 

[fsgregs]

Worf:

**** it works great now. I can't thank you enough. FYI, I edited one line and added one line as follows:

If minv < 1.001 Then .MinimumScale = 0
If maxs < 1.001 Then .MaximumScale = 1 * 1.4

I did this because I had a data set whose values ranged from 1.00 to 1.60, and another set whose values ranged from 0.4 to 1.00. When the first set plotted, the original line began the Y axis at 1.0. With the edit, a value of 1.0 now forces the min axis to start at 0. With the 2nd data set, the max axis was set at 1.0, resulting in the 1.0 data point being plotted on the top max line. The added line now adds a bit more space above the 1.0 line.

I will test out the routine as I enter data. Once I confirm it has no bugs, I will clean up my medical data log to remove personal info, and post the excel form on a cloud site for everyone to use.

Again, thank you so much for your effort. You ROCK!

Frank