Simplification of Matrix
 

The following is a portion of a data set based on weekly data (see bottom)

Is there any way to compute this in a monthly format so it will sum on both
dimensions?

ie.
July 2008 Aug 2008 Sept 2008
Dec 2007 4
Jan 2008 332 5
Feb 2008 1,110 136

Thanks.


5-Jul-08 12-Jul-08 19-Jul-08 26-Jul-08 2-Aug-08
29-Dec-07 4
5-Jan-08 8 5
12-Jan-08 32 11 7
19-Jan-08 77 42 15 9
26-Jan-08 56 40 22 8 5
2-Feb-08 147 116 84 46 16
9-Feb-08 41 49 38 28 15
16-Feb-08 87 76 89 70 51
23-Feb-08 45 67 58 68 54
1-Mar-08 53 79 118 102 121
8-Mar-08 60 52 78 117 101

Simplification of Matrix
 

What are your expected results?
i.e.: When I see a table with row headers and column headers such as you
have, I expect the intersection to be relevant to each.

So how does July 5, 2008 and December 29, 2007 = 4? What exactly are you
summing.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Tuppie11" wrote:

The following is a portion of a data set based on weekly data (see bottom)

Is there any way to compute this in a monthly format so it will sum on both
dimensions?

ie.
July 2008 Aug 2008 Sept 2008
Dec 2007 4
Jan 2008 332 5
Feb 2008 1,110 136

Thanks.


5-Jul-08 12-Jul-08 19-Jul-08 26-Jul-08 2-Aug-08
29-Dec-07 4
5-Jan-08 8 5
12-Jan-08 32 11 7
19-Jan-08 77 42 15 9
26-Jan-08 56 40 22 8 5
2-Feb-08 147 116 84 46 16
9-Feb-08 41 49 38 28 15
16-Feb-08 87 76 89 70 51
23-Feb-08 45 67 58 68 54
1-Mar-08 53 79 118 102 121
8-Mar-08 60 52 78 117 101

Simplification of Matrix
 

Currently the column and row headers are dates of the week. I would like to
have the matrix simplified to sum for months. ie. if there are 4 columns for
the weeks in july, I would like to sum these into 1 and similarly for each
row.

If this was one dimensional, it could easily be done with a sumproduct
statement with the condition of the month. But I am unsure of how to do this
under two dimensions.

Any help would be very appreciated.
Thanks.

"John C" wrote:

What are your expected results?
i.e.: When I see a table with row headers and column headers such as you
have, I expect the intersection to be relevant to each.

So how does July 5, 2008 and December 29, 2007 = 4? What exactly are you
summing.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Tuppie11" wrote:

The following is a portion of a data set based on weekly data (see bottom)

Is there any way to compute this in a monthly format so it will sum on both
dimensions?

ie.
July 2008 Aug 2008 Sept 2008
Dec 2007 4
Jan 2008 332 5
Feb 2008 1,110 136

Thanks.


5-Jul-08 12-Jul-08 19-Jul-08 26-Jul-08 2-Aug-08
29-Dec-07 4
5-Jan-08 8 5
12-Jan-08 32 11 7
19-Jan-08 77 42 15 9
26-Jan-08 56 40 22 8 5
2-Feb-08 147 116 84 46 16
9-Feb-08 41 49 38 28 15
16-Feb-08 87 76 89 70 51
23-Feb-08 45 67 58 68 54
1-Mar-08 53 79 118 102 121
8-Mar-08 60 52 78 117 101

Simplification of Matrix
 

To verify, the first 4 would be included for any totals involving July 2008,
as well as any totals involving December 2007?
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Tuppie11" wrote:

Currently the column and row headers are dates of the week. I would like to
have the matrix simplified to sum for months. ie. if there are 4 columns for
the weeks in july, I would like to sum these into 1 and similarly for each
row.

If this was one dimensional, it could easily be done with a sumproduct
statement with the condition of the month. But I am unsure of how to do this
under two dimensions.

Any help would be very appreciated.
Thanks.

"John C" wrote:

What are your expected results?
i.e.: When I see a table with row headers and column headers such as you
have, I expect the intersection to be relevant to each.

So how does July 5, 2008 and December 29, 2007 = 4? What exactly are you
summing.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Tuppie11" wrote:

The following is a portion of a data set based on weekly data (see bottom)

Is there any way to compute this in a monthly format so it will sum on both
dimensions?

ie.
July 2008 Aug 2008 Sept 2008
Dec 2007 4
Jan 2008 332 5
Feb 2008 1,110 136

Thanks.


5-Jul-08 12-Jul-08 19-Jul-08 26-Jul-08 2-Aug-08
29-Dec-07 4
5-Jan-08 8 5
12-Jan-08 32 11 7
19-Jan-08 77 42 15 9
26-Jan-08 56 40 22 8 5
2-Feb-08 147 116 84 46 16
9-Feb-08 41 49 38 28 15
16-Feb-08 87 76 89 70 51
23-Feb-08 45 67 58 68 54
1-Mar-08 53 79 118 102 121
8-Mar-08 60 52 78 117 101

Simplification of Matrix
 

Yes, it would have the total for the column involving july and a row
involving december.

"John C" wrote:

To verify, the first 4 would be included for any totals involving July 2008,
as well as any totals involving December 2007?
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Tuppie11" wrote:

Currently the column and row headers are dates of the week. I would like to
have the matrix simplified to sum for months. ie. if there are 4 columns for
the weeks in july, I would like to sum these into 1 and similarly for each
row.

If this was one dimensional, it could easily be done with a sumproduct
statement with the condition of the month. But I am unsure of how to do this
under two dimensions.

Any help would be very appreciated.
Thanks.

"John C" wrote:

What are your expected results?
i.e.: When I see a table with row headers and column headers such as you
have, I expect the intersection to be relevant to each.

So how does July 5, 2008 and December 29, 2007 = 4? What exactly are you
summing.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Tuppie11" wrote:

The following is a portion of a data set based on weekly data (see bottom)

Is there any way to compute this in a monthly format so it will sum on both
dimensions?

ie.
July 2008 Aug 2008 Sept 2008
Dec 2007 4
Jan 2008 332 5
Feb 2008 1,110 136

Thanks.


5-Jul-08 12-Jul-08 19-Jul-08 26-Jul-08 2-Aug-08
29-Dec-07 4
5-Jan-08 8 5
12-Jan-08 32 11 7
19-Jan-08 77 42 15 9
26-Jan-08 56 40 22 8 5
2-Feb-08 147 116 84 46 16
9-Feb-08 41 49 38 28 15
16-Feb-08 87 76 89 70 51
23-Feb-08 45 67 58 68 54
1-Mar-08 53 79 118 102 121
8-Mar-08 60 52 78 117 101

Simplification of Matrix
 

Okay, the 'easy' way, is to have a helper row and a helper column. In my
example, the column headers are in C1:G1, and the row headers are in A3:A13.
I am trying to sum the numbers in the table of numbers (now C3:G13), based on
whether each column or row matches the month AND year of a date entered in
cell A1.
NOTE: My helper column is column B, and my helper row is row 2.
A1: my date that I am matching
B2: =SUM(C2:G2,B3:B13)
B3:
=SUMPRODUCT(--(MONTH($C$1:$G$1)=MONTH($A$1)),--(YEAR($C$1:$G$1)=YEAR($A$1)),(C3:G3))
.... copy the formula in B3 down to B13
C2:
=IF(AND(MONTH(C$1)=MONTH($A$1),YEAR(C$1)=YEAR($A$1 )),0,SUMPRODUCT(--(MONTH($A$3:$A$13)=MONTH($A$1)),--(YEAR($A$3:$A$13)=YEAR($A$1)),(C$3:C$13))
.... copy the formula in C2 over to G2

The key was to NOT count some cells twice. If it was in a column that was
matched, as well as a row that was matched, then you don't want it to be
doubled.

--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Tuppie11" wrote:


Yes, it would have the total for the column involving july and a row
involving december.

"John C" wrote:

To verify, the first 4 would be included for any totals involving July 2008,
as well as any totals involving December 2007?
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Tuppie11" wrote:

Currently the column and row headers are dates of the week. I would like to
have the matrix simplified to sum for months. ie. if there are 4 columns for
the weeks in july, I would like to sum these into 1 and similarly for each
row.

If this was one dimensional, it could easily be done with a sumproduct
statement with the condition of the month. But I am unsure of how to do this
under two dimensions.

Any help would be very appreciated.
Thanks.

"John C" wrote:

What are your expected results?
i.e.: When I see a table with row headers and column headers such as you
have, I expect the intersection to be relevant to each.

So how does July 5, 2008 and December 29, 2007 = 4? What exactly are you
summing.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Tuppie11" wrote:

The following is a portion of a data set based on weekly data (see bottom)

Is there any way to compute this in a monthly format so it will sum on both
dimensions?

ie.
July 2008 Aug 2008 Sept 2008
Dec 2007 4
Jan 2008 332 5
Feb 2008 1,110 136

Thanks.


5-Jul-08 12-Jul-08 19-Jul-08 26-Jul-08 2-Aug-08
29-Dec-07 4
5-Jan-08 8 5
12-Jan-08 32 11 7
19-Jan-08 77 42 15 9
26-Jan-08 56 40 22 8 5
2-Feb-08 147 116 84 46 16
9-Feb-08 41 49 38 28 15
16-Feb-08 87 76 89 70 51
23-Feb-08 45 67 58 68 54
1-Mar-08 53 79 118 102 121
8-Mar-08 60 52 78 117 101