Page 1 of 5 123 ... LastLast
Results 1 to 15 of 65
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Structure long daily chart of meds and test result (Office 2003)

    Is there a good way to format what will be a long multicolumn spreadsheet of day columns and test results listed on a given day and meds given on a given day. The spreadsheet can have 365 columns approx for all the days in the year.
    I am not certain how to make it most efficient and what pivot tables are ideal.
    What is the best way to clean up the clutter in what will be a long spreadsheet.
    Rows:
    Meds: Venofer, Procrit, Testosterone, B12 etc
    Test Results: Wbc, hemoglobin, hematocrit.etc
    THanks for "organizational skill" suggestions
    JR

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    I don't understand what you want, but an Excel worksheet has a maximum of 256 columns (A, B, ..., IV), so you won't be able to create a 365 column table.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    Hans
    Actually I did not know that. Any way I only give meds and get test results for a patient once or twice a week so that should be okay.
    I am looking for your spreadsheet skills to make a very long multicolumn spreadsheet user friendly for say a hospital type spreadsheet. Saw once or twice a week i give say 3 medications then I draw a blood test and say get back 4 results. (picking numbers off the top of my head). is there a way to organize it, or have I done as much as one can expect, to get the most information out of the sheet quickly.
    The end result is does the hgb and the hct go up with all the meds. might be one pivot table and how has the wbc white blood cell count risen with say procrit injections.
    I am looking for a pretty spreadsheet that was organized correctly for something like this before i put in all the numbers and am told that it was a poorly constructed / organized spread sheet.
    Thanks again for all the help in the past , it has been invaluable
    JR

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    I'm afraid I can't help you with this. Hopefully someone else will have a suggestion.

    I doubt that a pivot table will be of much use here - the data field in a pivot table is always used to summarize data (sum, average, max, ...), not to display individual data.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    Hans
    Thanks for looking at it.
    Regards
    JR

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    You could transpose the data. Dates in 1 column and each item in the columns. Instead of test with numbers and units, the units could be part of the column heading and just keep the number. Autofilter could be used to scan thru the data, charts could be created this way (and with autofilter would only plot visible data). I think this "control chart" type of data is the easiest way to look at trends.

    A Chart could be made interactive to only look a 1 column of data with comboboxes or even scatter charts of 2 columns could be done to look for correlation...

    Pivot table could summarize the numbers and the dates could be grouped.

    If you let us know what you want to do with the data, we may provide other info for viewing and or organizing...

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    Steve:
    Thanks, this makes more sense than what I have been doing. I am attaching a.xls to see if this is what you mean. I would love to map hct (hematocrits) agains procrit med and venofer med and nandrolene med. Is there a way of doing this.
    Also how would you format the xls for easiest eye pleasing color readability
    JR

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    Yes that is what I mean.

    Not sure what you mean by "map", but if you want to plot one against the other: You can make the "hct" range the "X" and make individual XY scatter charts with the procrit, the venofer, or the nandrolene as the Y or combined XY charts (with all of them as the Y's)

    The problem will be that they all have to be in the same row to be correlatable unless you "massage your data" in some way. The "40K" in C8 I assume is an error and should be just the number 40, otherwise it will plot as a zero.

    I tend not to use a lot of colors unless there is a reason to highlight them. To do it, just select the range and format cells - and choose the Patterns(tab) to select the color.

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    Steve:
    Great I will experiment with this thanks
    JR

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    Is there a way ie macro etc, since I started the spreadsheet in the non ideal way, to have excel swap the columns and rows ie to make the day columns rows and the meds and lab rows now columns??
    JR

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    You can copy the cells you have now to the clipboard.
    Select the upper left corner of the destination range (for example in another sheet)
    Select Edit | Paste Special...
    Tick the Transpose check box.
    Click OK.

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    Hans
    Many thanks, just perfect now, and it is now more useful and functional. Thanks to all
    JR

  13. #13
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    One last refinement if possible. Is it possible to search column A dates and at the appropriate points pick weekly intervals and insert a blank row and say Treatment week 1 and Treatment week 2 etc. This may be difficult as the dates in column A are incremental but not consecutive only days are listed when meds and / or tests given.
    It would be nice to see how things have changed on a a weekly basis.
    The extra rows for week 1 and 2 are my additions manually for illustration purposes
    JR

  14. #14
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    Hans
    Whatever makes sense and is the easiest
    Thanks
    JR

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Structure long daily chart of meds and test result (Office 2003)

    Do you want calendar weeks (Sun - Sat) or do you want to start at whatever is the first date (in cell A2)?

Page 1 of 5 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •