How to graph array data

[bctyner]

I have an array in which column A is a list of transaction numbers, but only the 1st, 11th, 21st, and 31st numbers are there. Then in column B those transaction numbers are listed. Then it gets tricky - colum B has transaction #s 12, 22, 32, etc., column C has 13, 23, 33, D has 14, 24, 34, on and on and on.


So the first row of the array is transaction 1 through 10, second row is 11 through 20, third is 21 though 30.


How can I graph this mess? I need to show trends in the amount of each transaction and compare trends between groups. Each group's data is formatted in this same way.

 

[Hui]

Bctyner


If I assume your X Values are in Column A rows 1, 11, 21 etc

and your First y values are in Column B rows 2, 12, 22 etc

like:

[pre]

1
2
3

1
2
3

1
2
3[/pre]
Then you can use some Named Formula to setup arrays which can be plotted  


Your X Values in Column A are

[code]=N(OFFSET(Sheet1!$A$1,(ROW(OFFSET(Sheet1!$A$1,,,Counta($A:$A)))-1)*10,,1))


Your Y Values in Column B are

[code]=N(OFFSET(Sheet1!$B$2,(ROW(OFFSET(Sheet1!$A$1,,,Counta($A:$A)))-1)*10,,1))

I have assumed that you have no data above/below or between the cells with the data


If you do have data between or below the cells then the following will work


Your X Values in Column A are

=N(OFFSET(Sheet1!$A$1,(ROW(OFFSET(Sheet1!$A$1,,,n))-1)*10,,1))[/code]


Your Y Values in Column B are

=N(OFFSET(Sheet1!$B$2,(ROW(OFFSET(Sheet1!$A$1,,,n))-1)*10,,1))[/code]


where n is the number of items in each series you want to chart

 

[bctyner]

Thanks, Hui.


The X values cycle 0, 5, 10, 15, 20, 25. The data are all written in the same row - A2 is the first X value, which is session 0, B2 is the transaction amount for session 0. C2 is the amount for session 1, D2 is session 2 etc. B3 is the transaction amount for session 5, B4 is the amount for session 6. So they look like this (assuming "Session" is in A1):


Session: Transaction amounts per session:

00, 0, 1, 2, 3, 4

05, 5, 6, 7, 8, 9

10, 10, 11, 12, 13, 14

15, 15, 16, 17, 18, 19

20, 20, 21, 22, 23, 24

25, 25, 26


So N = 27 since the array starts at 0, and there are only 2 transactions in row 7.


I originally said it went from 0 to 10, but it's actually listed by 5s. That may be why I can't get your formula working. It usually returns all 0s.

 

[Hui]

Bctyner


Can you please upload a file as per the instructions on the Green Sticky Posts on the Main Forum page


It makes solving these type of problems so much easier for all concerned.


Please add any further instructions into the file as a text box or in cells off to the side

 

[bctyner]

Here's a sample. Thanks for the help.


http://dl.dropbox.com/u/10012690/Bctyner%20Array%20Data.xlsx

 

[Faseeh]

Hi bctyner,


See whether this could be of any help to you. File is still incomplete, need an approval before finishing it!


http://dl.dropbox.com/u/60644346/Bctyner%2520Array%2520Data(2).xlsx


Regards,

Faseeh

 

[bctyner]

Hi Faseeh,


That looks great. Most of the lookup function you use is way over my head; how easily can I add new data?


For example, if I paste a new array after the last one on this worksheet, how will I update your table to extract the values?


In any case, it works far better than what I've come up with (you can see here: http://dl.dropbox.com/u/10012690/Bctyner%20Array%20Data%201.xlsx


The blank rows in between each transposed array are a problem, and I have to update some of the cell references manually. That's my first attempt at using Match. I also got it working with Index, but it had the same limitations.


Thanks for the help!

 

[Hui]

If you want to chart the data the gaps don't matter

Select the entire area and chart the data, then delete the series which are blanks

Or add each series to a chart manually

 

[bctyner]

I'll also be calculating some stats, maybe using pivot tables and charts. For example, I'll be graphing the average and standard deviation for every 3 transactions (1,2,3; then 2,3,4; etc.). Definitely preferable to extract in a concise table, but I'm still working on it...


I got my solution from a tutorial by Mike Girvin btw (http://www.youtube.com/watch?v=gEvfr5Vfa2c&feature=digest_thu).

 

[Faseeh]

Hi bctyner,


Glad to know that you found a solution. Are you finished with this chart or still working? Should i resume working or it is done?


Faseeh

 

[bctyner]

It's a work in progress. I like your solution. Will it be simple to add more arrays to the original table and have them extracted to the new table?


Thanks again,


BT

 

[Faseeh]

Hi bctyner,


Nice to see your reply. I will try to make it as flexible as possible. At the moment you can add as many groups you want but addition with in a group is problematic. I will work over that.


Regards,

Faseeh