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!!

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.

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...

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.