Sorting&Computing results

[Guest]

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.


For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:


Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G

 

[Dave Peterson]

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380

 

[Guest]

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by

drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters --> Tab, semicolon, comma, space,
other.

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380

 

[Dave Peterson]

First, a stupid typo on my part!
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by

drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters --> Tab, semicolon, comma, space,
other.

 

[Guest]

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G
--------------------------------------------------------
9 Total
--------------------------------------------------------

First, a stupid typo on my part!
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

 

[Dave Peterson]

What don't you get and what did you try that you had trouble with?

You could get the sum of all the markers for 2000 in class A:
=sumproduct(--(year(b1:b100)=2000),--(a1:a100="A"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G

 

[Guest]

i trying to use this:
=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)),


but i have many date from 01-aug-00, 02-sep-00. 10-jan-00 to year 2006.
therefore i would like to finalise by year, maybe 00. But how do i set it
inside the date as "year2000" or "00"?
Please teach me. thanks.


=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)), change the "June"
Meaning the "June" is change to year. But cant set to "year00" or "01-01-00".

__________________________________________________________________

 

[Dave Peterson]

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0))

You may want to try the =sumproduct() suggestion if you're going to eschew the
pivottable.

i trying to use this:
=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)),

but i have many date from 01-aug-00, 02-sep-00. 10-jan-00 to year 2006.
therefore i would like to finalise by year, maybe 00. But how do i set it
inside the date as "year2000" or "00"?
Please teach me. thanks.

=SUM(IF(A1:A10="Ford",IF(B1:B10="June",1,0),0)), change the "June"
Meaning the "June" is change to year. But cant set to "year00" or "01-01-00".

__________________________________________________________________

 

[Guest]

i trying to add up the total marks. but how come i dont need to use the
"Mark" for the forumla?

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0))

i trying to solve this first b4 i using pivottable, thereafter see which is
better.
thanks.

 

[Dave Peterson]

You're right:

=sumproduct(--(year(b1:b100)=2000),--(a1:a100="ford"),c1:c100)

i trying to add up the total marks. but how come i dont need to use the
"Mark" for the forumla?

=SUM(IF(A1:A10="Ford",IF(year(B1:B10)=2000,1,0),0))

i trying to solve this first b4 i using pivottable, thereafter see which is
better.
thanks.

 

[Guest]

using this: =sumproduct(--(year(b1:b100)=2000),--(a1:a100="ford"),c1:c100)

Adjust the ranges to match--but you can't use whole columns

How do i adjust the range to match?
What do i need to match?
Between the 2000 represent year 2000 or year itself will be able to justify
whatever years?

 

[Dave Peterson]

If you want to use rows 1:10, then you'd make it b1:b10, a1:a10, c1:c10.

And yes, you'll have to change the formula.

Or point at a cell that contains the year.

using this: =sumproduct(--(year(b1:b100)=2000),--(a1:a100="ford"),c1:c100)

Adjust the ranges to match--but you can't use whole columns

How do i adjust the range to match?
What do i need to match?
Between the 2000 represent year 2000 or year itself will be able to justify
whatever years?