Matrix and calculations

[Michel Khennafi]

Good evening to all of you...

Is it a challenge? Check this out

I would like to perform some calculations in a spreadsheet where I have a
matrix, for instance (column A= group, column b= subgroup, column C=turnover
and the values in A1=1, B1=3...)


A B C D E
1 1 3 100
2 1 3 100
3 1 2 50
4 1 2 50
5 2 1 100
6 2 1 100
7 3 2 100

I would like to create a summary table using a matrix and have the following

Group Sub Group Occurences Turnover
1 3 2 200 --> (C1=100
+ D1= 100)
1 2 2 100 --> (C3=50 +
C4= 50)
2 1 2 200
3 2 1 100

Is there a way using a matrix to calculate the turnover for a given
combination group / Sub/group.... I know how to calculate the occurence
using a matrix, I would like some help on summing the turnovers

Regards,

Michel

[Michael R Middleton]

Michel -

Looks like a straightforward Pivot Table task.

- Mike

www.mikemiddleton.com

++++++++++++++++++++

"Michel Khennafi" <mkhennafi@catalystinternational.com> wrote in message
news:O$k$SJ39EHA.1084@tk2msftngp13.phx.gbl...
> Good evening to all of you...
>
> Is it a challenge? Check this out
>
> I would like to perform some calculations in a spreadsheet where I have a
> matrix, for instance (column A= group, column b= subgroup, column
> C=turnover
> and the values in A1=1, B1=3...)
>
>
> A B C D E
> 1 1 3 100
> 2 1 3 100
> 3 1 2 50
> 4 1 2 50
> 5 2 1 100
> 6 2 1 100
> 7 3 2 100
>
> I would like to create a summary table using a matrix and have the
> following
>
> Group Sub Group Occurences Turnover
> 1 3 2 200 -->
> (C1=100
> + D1= 100)
> 1 2 2 100 --> (C3=50
> +
> C4= 50)
> 2 1 2 200
> 3 2 1 100
>
> Is there a way using a matrix to calculate the turnover for a given
> combination group / Sub/group.... I know how to calculate the occurence
> using a matrix, I would like some help on summing the turnovers
>
> Regards,
>
> Michel
>
>
>
>

[Max]

A formula way might be to use SUMPRODUCT ..

Assume this table below is
in Sheet1, in A1:C8, data from row2 down

Group SubGroup Turnover
1 3 100
1 3 100
1 2 50
1 2 50
2 1 100
2 1 100
3 2 100

In Sheet2
-------------
You have setup the table below in cols A to D,
data from row2 down

Group SubGroup Occurences Turnover
1 3
1 2
2 1
3 2

For Occurrences
Put in C1:
=SUMPRODUCT((Sheet1!$A$2:$A$8=$A2)*(Sheet1!$B$2:$B$8=$B2))

For Turnover
Put in D1:
=SUMPRODUCT((Sheet1!$A$2:$A$8=$A2)*(Sheet1!$B$2:$B$8=$B2),Sheet1!$C$2:$C$8)

Select C1:D1, fill down to populate the table

For the sample table in Sheet1, you'll get the results:

Group SubGroup Occurrences Turnover
1 3 2 200
1 2 2 100
2 1 2 200
3 2 1 100

Adapt the ranges to suit
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Michel Khennafi" <mkhennafi@catalystinternational.com> wrote in message
news:O$k$SJ39EHA.1084@tk2msftngp13.phx.gbl...
> Good evening to all of you...
>
> Is it a challenge? Check this out
>
> I would like to perform some calculations in a spreadsheet where I have a
> matrix, for instance (column A= group, column b= subgroup, column
C=turnover
> and the values in A1=1, B1=3...)
>
>
> A B C D E
> 1 1 3 100
> 2 1 3 100
> 3 1 2 50
> 4 1 2 50
> 5 2 1 100
> 6 2 1 100
> 7 3 2 100
>
> I would like to create a summary table using a matrix and have the
following
>
> Group Sub Group Occurences Turnover
> 1 3 2 200 -->
(C1=100
> + D1= 100)
> 1 2 2 100 --> (C3=50
+
> C4= 50)
> 2 1 2 200
> 3 2 1 100
>
> Is there a way using a matrix to calculate the turnover for a given
> combination group / Sub/group.... I know how to calculate the occurence
> using a matrix, I would like some help on summing the turnovers
>
> Regards,
>
> Michel
>
>
>
>

[Dana DeLouis]

This might get you started...

PivotTable reports 101
http://office.microsoft.com/en-us/as...346321033.aspx

--
Dana DeLouis
Win XP & Office 2003


"Michel Khennafi" <mkhennafi@catalystinternational.com> wrote in message
news:O$k$SJ39EHA.1084@tk2msftngp13.phx.gbl...
> Good evening to all of you...
>
> Is it a challenge? Check this out
>
> I would like to perform some calculations in a spreadsheet where I have a
> matrix, for instance (column A= group, column b= subgroup, column
> C=turnover
> and the values in A1=1, B1=3...)
>
>
> A B C D E
> 1 1 3 100
> 2 1 3 100
> 3 1 2 50
> 4 1 2 50
> 5 2 1 100
> 6 2 1 100
> 7 3 2 100
>
> I would like to create a summary table using a matrix and have the
> following
>
> Group Sub Group Occurences Turnover
> 1 3 2 200 -->
> (C1=100
> + D1= 100)
> 1 2 2 100 --> (C3=50
> +
> C4= 50)
> 2 1 2 200
> 3 2 1 100
>
> Is there a way using a matrix to calculate the turnover for a given
> combination group / Sub/group.... I know how to calculate the occurence
> using a matrix, I would like some help on summing the turnovers
>
> Regards,
>
> Michel
>
>
>
>

[Michel Khennafi]

Is there a way to get the results without having a pivot table?

Michel
"Michel Khennafi" <mkhennafi@catalystinternational.com> wrote in message
news:O$k$SJ39EHA.1084@tk2msftngp13.phx.gbl...
> Good evening to all of you...
>
> Is it a challenge? Check this out
>
> I would like to perform some calculations in a spreadsheet where I have a
> matrix, for instance (column A= group, column b= subgroup, column
C=turnover
> and the values in A1=1, B1=3...)
>
>
> A B C D E
> 1 1 3 100
> 2 1 3 100
> 3 1 2 50
> 4 1 2 50
> 5 2 1 100
> 6 2 1 100
> 7 3 2 100
>
> I would like to create a summary table using a matrix and have the
following
>
> Group Sub Group Occurences Turnover
> 1 3 2 200 -->
(C1=100
> + D1= 100)
> 1 2 2 100 --> (C3=50
+
> C4= 50)
> 2 1 2 200
> 3 2 1 100
>
> Is there a way using a matrix to calculate the turnover for a given
> combination group / Sub/group.... I know how to calculate the occurence
> using a matrix, I would like some help on summing the turnovers
>
> Regards,
>
> Michel
>
>
>
>

[Max]

"Michel Khennafi" <mkhennafi@catalystinternational.com> wrote
> Is there a way to get the results without having a pivot table?

The below was suggested in response earlier ??

A formula way might be to use SUMPRODUCT ..

Assume this table below is
in Sheet1, in A1:C8, data from row2 down

Group SubGroup Turnover
1 3 100
1 3 100
1 2 50
1 2 50
2 1 100
2 1 100
3 2 100

In Sheet2
-------------
You have setup the table below in cols A to D,
data from row2 down

Group SubGroup Occurences Turnover
1 3
1 2
2 1
3 2

For Occurrences
Put in C1:
=SUMPRODUCT((Sheet1!$A$2:$A$8=$A2)*(Sheet1!$B$2:$B$8=$B2))

For Turnover
Put in D1:
=SUMPRODUCT((Sheet1!$A$2:$A$8=$A2)*(Sheet1!B$2:$B$8=$B2),Sheet1!$C$2:$C$8)

Select C1:D1, fill down to populate the table

For the sample table in Sheet1, you'll get the results:

Group SubGroup Occurrences Turnover
1 3 2 200
1 2 2 100
2 1 2 200
3 2 1 100

Adapt the ranges to suit

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----