CODEWITHSUNDEEP
excelexcel-2007
Pr0no
Pr0no

Reputation: 4109

How to let Excel group values before graphing?

Consider the following Excel worksheet:

   | A       B     C
 --+--------------------
 1 | company day  return
 --+--------------------
 2 | 1       1    2
 3 | 1       2    1
 4 | 1       3    1
 5 | 2       1    3
 6 | 2       2    2
 7 | 2       3    0
 8 | 3       1    1
 9 | 3       2    5
10 | 3       3    1

The worksheet is panel timeseries basically. In the example, it lists the stock returns for 3 companyies for 3 conseutive days. I now want to graph the total return (so: accumulated return for all companies) per day, as follows:

 return 8 |        * (C3+C6+C9=1+2+5=8)
        7 |
        6 |  * (C2+C5+C8=2+3+1=6)
        5 |
        4 |
        3 |
        2 |              * (C4+C7+C10=1+0+1=2)
        1 |
        0 +----------------------
             1     2     3    day

Is this possible to do in Excel and if so, how?

Upvotes: 2

Views: 7381

Answers (2)

RocketDonkey
RocketDonkey

Reputation: 37279

As @Gimp suggests, how about setting up a new table on a new sheet that refers to your original range (OriginalSheet in this case), and have it look like:

  |   A      B       C
--+---------------------
1 | day   return
--+---------------------
2 | 1     6
3 | 2     8
4 | 3     2

Where return in B2 would be calculated as 

=SUMIFS(OriginalSheet!C:C,OriginalSheet!B:B,"=" & A2)

and copied all the way down. To set it up, you would want to just list the days in column A and then add the formula. From that, you can set up a scatter plot by selecting the entire new range (in our example, A1:B4. The resulting chart will look like what you provided. Additionally, this will keep the number of columns low, so adding new companies will not widen the range.

Let me know if that doesn't make sense as I don't have a gift for communicating clearly :)

Upvotes: 1

danielpiestrak
danielpiestrak

Reputation: 5439

Setup a new chart graph on another sheet to group the data from your first larger chart into this format:

 --+--------------------
A1 | company1 company2 Company3
 --+--------------------
 1 | B2       C2    D2
 2 | B3       C3    D3
 3 | B4       C4    C4

Then, set the formula in cell B2 to something like = SUMIFS(C:C,A:A,A1,B:B,A2) Where C:C, B:B and A:A reference the columns in the original graph and the A1 and A2 Reference the grouping chart.

Note that the SUMIFS formula is for Excel 2007 / 2010. I've included example worksheets for this method and using the same approach with an Array formula in older versions of excel.

Here is an example file for Excel version 2007 / 2010: https://dl.dropbox.com/u/19599049/GroupData_NewExcel.xlsx

Here is an example for old versions of excel:
https://dl.dropbox.com/u/19599049/GroupData_OldExcel.xls

Upvotes: 3

Related Questions