+ Reply to Thread
Results 1 to 5 of 5

Graph from scattered data

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    florence
    MS-Off Ver
    office 2010
    Posts
    14

    Graph from scattered data

    Hi i've been ask to create some graphs on this Excel

    It is basically a weird macro-table where in every cell there is a mini-table. Every macro-line (or macro-record) is referred to a different patient and every macro-field contains a mini-table with some data about the results of an exam made on that patient. Ignoring the first two columns of the Macro-tbl, the graphs should be an analysis of the percentage as a function of a date. Precisely, the ordinate should be the percentage in "Risultato %" of the mini-table, and the abscissa the date in "Data arrivo campione" ....and every patient (i.e. every record of the macro-tbl) should have his graph to see the trend of his percentage.
    I hope i've been understood
    For now i've only noticed that the cells containing the data of interests are all equidistant (there are 5 cells between them), but i dont know if it can be of any help...
    Thanks!!
    Last edited by gmasterrr; 06-30-2014 at 06:01 AM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,459

    Re: Graph from scattered data

    It often seems that the hardest part of some charting/graphing problems is in the spreadsheet layout. IMO, the first step in a problem like this is to extract the desired data from the existing spreadsheet into something more conducive to graphing -- A list of values. I would probably use your observation that there are 5 columns between each result and an INDEX() function to pull the desired values from each mini table into another table that I can use to plot the data.
    Please Login or Register  to view this content.
    The other complication you have is that some results are text strings ("99% R") and others are actual values (35%). So, before or after this data extraction step, you will need a step to extract the number from the text string. Maybe something like =VALUE(LEFT(D2,3)). You might need something more complex (I'm not very proficient with Excel's text manipulation functions).

    Another thought -- how is this data entered into the spreadsheet originally? By hand? Macro? Other? If you had any control over how this data was entered into the spreadsheet, I would be tempted to tackle this problem at that level. Set up the data into something more like a standard data base instead of this "mini-table" format. In the long run, something that looks more like a database will be easier to work with.

    This kind of "database management" is not something I'm very good at, so I'm not sure I can be of much more help.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-26-2014
    Location
    florence
    MS-Off Ver
    office 2010
    Posts
    14

    Re: Graph from scattered data

    Hey thanks for the precious help!!
    I'm sorry but i'm not very familiar with the INDEX funct., i'm trying to use it as you write it but it gives me an error (doesn't accept the "$A2,$B2" reference in the second part of the formula)... and what do you mean with "copy of A2, =B1-5, etc.."?
    And i completely agree with the data management you propose, but unfortunately, despite they're actually entered by hand, this scheme cannot be changed....

    Edit: I think i'found a way t use INDEX but it doesn't gives me the correct values, i mean, instead of the date it gives me a number and the same for the percentage...
    Last edited by gmasterrr; 06-27-2014 at 04:16 AM.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,459

    Re: Graph from scattered data

    Edit: I think i'found a way t use INDEX but it doesn't gives me the correct values, i mean, instead of the date it gives me a number and the same for the percentage...
    Remember way back to grade school when you learned how percentages are simply another way of displaying fractions and decimals. I expect it is returning the correct value, and you just need to go in and format the cell to display as "percentage with the appropriate number of decimal places".

    Same thing with the date/time values. Excel stores dates and times as serial numbers (http://www.cpearson.com/Excel/datetime.htm#SerialDates). It is probably returning the correct "value", you just need to format the cell to display the value in the appropriate date/time format.

  5. #5
    Registered User
    Join Date
    06-26-2014
    Location
    florence
    MS-Off Ver
    office 2010
    Posts
    14

    Re: Graph from scattered data

    Thanks!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Bring scattered data to correct columns
    By vadaniels in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 12-22-2020, 10:32 PM
  2. Data Shuffling (with a Condition) in Scattered Cells (Selection)
    By quanom in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-18-2014, 03:51 PM
  3. Bilinear interpolation of scattered data points
    By Sebi in forum Excel General
    Replies: 15
    Last Post: 09-19-2012, 01:05 AM
  4. Scattered Graph
    By Shijesh Kumar in forum Excel General
    Replies: 6
    Last Post: 02-17-2009, 06:12 AM
  5. time scale-x with scattered data-y
    By searching for answer in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-20-2005, 05:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1