Create a chart from data in every other column

I have a spreadsheet, which is structured like this;

17/07/2013 18/07/2013 19/07/2013
Skip Graph Skip Graph Skip Graph
Value Set 1 100 223.00 101 225.70 100 223.00
Value Set 2 101 179.80 103 185.40 100 180.10
Value Set 3 102 100.23 104 102.45 99 99.76

I want to create a graph with 3 lines on it, one for Value Set 1, one for Value Set 2, and one for Value Set 3.

The graph should graph the date (17/07/2013 etc.) against the data in the columns labelled Graph, so Value Set 1 would graph as follows;

17/07/2013 against 223.00
18/07/2013 against 225.70
19/07/2013 against 223.00

I am not the 'sharpest tool' when it comes to Excel, but I am thinking the best idea is to re-tabulate this data elsewhere, and graph that. I am think a column for the Data, and a column for the 'Graph' data.

Obviously the above table is going to grow as more dates are added, so I am looking for a programmatic way to do this, but I cannot see how to write something that will easily access every other cell.

Does any of the above make sense I hope so, and hopefully someone can shove me in the right direction.

Thanks in antcipation

You can set up your subsidiary table using formulae. Post an example workbook if you want further details (the FAQ describes how to attach a file).

Pete

I too need the same thing.

@pkhopkar

If you have a question that needs answering please start your own thread rather than hi-jack someone else's (see Rule 2). It is unlikely that your data is laid out in exactly the same way as the OP.

Pete

I have attached a sample spreadsheet

I want to graph for Fund 1, Fund 2, and Fund 3. In each case plotting Date against it's respective Total.

I appreciate your help and guidance.

I can't see an attached file. While you are editing/composing a post, click on Go Advanced then scroll down and click on Manage Attachments, then Add Files | Select files and navigate to where your file is and double-click the file icon. Then click on Upload File | Done and Submit post.

Hope this helps.

Pete

Attachment is hopefully here;

Why have you got ?? in G1:H1 - shouldn't that be 19 July 2013 ?

Will your table expand to the right as more data becomes available?

Will you want to see all data that is in the table, or just 3 days' worth?

I don't want to spend/waste time putting something together which is not what you want, so please describe your requirements clearly with reference to the cells in your attached file.

Pete

Wow, I literally have the same exact question, with the same set up; two columns for each date entry and need to skip the first column set for each date, date entries will continue to be added on to the right as time passes. I will be waiting to see how this is done!!!

anyone know how to help us on this graphing problem?

Thanks

In the attached file I've set up a subsidiary table with the values you want to plot, mainly using an INDEX formula. The formulae have been copied down to row 20 to give you seven days' worth of data, but you can copy them further if you wish.

I've also set up a simple line chart showing the three funds over 3 days, with the capability of showing up to 7 days. Just add some more data in the same format on rows 1 to 6 to see this reflected in the chart. If you need to display more than 7 days then you can adjust the chart ranges - click on the chart, then right-click and choose Select Data, then for each series click on Edit and change the end row number.

Hope this helps.

(And sorry for not getting back to you - I forgot about it !!)

Pete

Pete, Thank you very much for putting that together. Is there a way to do it without going through a subsidiary table? or does the data HAVE to be reorganized for reading the data the chart picks up? Is there no way to use formulas within the data selection range in the graph formatting???

Thanks!!

David

David,

take a look at post #4

(sorry, I don't make the rules !!)

Pete

I saw that earlier,.... but I literally have the SAME exact set up as the original poster does and am trying to accomplish the SAME exact outcome as the original poster. It does not make sense to me to have multiple threads answering the SAME exact question. You have provided one solution to the problem at hand, but like everything in life, there is more than one way to skin a cat, so i was hoping to keep all the possible solutions to this single specific question in one thread (will benifit me and anyone else down the road that is searching for answers.) If I really MUST re-post this all in a new thread, I will, but it just doesn't make logical sense. I understand the intent of the "no hijacking a thread" rule but feel this isn't hijacking and isn't taking away from the original poster, just adding more options for him as well...
Please don't take the above as aggression by any means (all caps can come across differently to different people), I do appreciate your help!

Well, I don't think this is the ONLY thread about graphing data from non-adjacent cells, so posting elsewhere would not be an issue.

Essentially you need to put the data into adjacent cells so that means setting up a table specifically for that. There are alternative approaches - I had the table going down (easier to grow that way), but it could just as easily go across the sheet. Also, you could use a different formula (with INDIRECT) to extract the data.

Hope this helps.

Pete