Preparation and Summarisation of data

  • Thread starter Thread starter inspir3d
  • Start date Start date
I

inspir3d

Hi thanks a lot. The pivot table is great. I got another proble
though.
What do I do if I want to summarise my data like so:

I have
Time C D
930 423 231
930 312 233
930 314 245
931 224 432
931 234 435
931 244 431

And I want to summarise it as
Time, C', D'
930 423 245
931 224 431
...
XYZ, (First value of C where Time is 930), (Last value of D where Tim
is XYZ)

Thanks again
 
Hi
as an idea:
- in E1 enter the formula:
=COUNTIF($A$1:$A1,A1)
and copy this down.

Now filter for '1' in this column
 
You could add two new columns to the source data, and calculate the
values. For example, with your values in cells A2:C20 --

In cell D2, enter: =IF(A2=A1,"",B2)
In cell E2, enter:
=IF(A2=A3,"",INDEX($C$2:$C$20,MATCH(A2,$A$2:$A$20,0)
+COUNTIF($A$2:$A$20,A2)-1))

Copy the formulas down to the last row of data
Include the new columns in the pivot table source range
Refresh the pivot table, and add the new fields, summarized by Sum
Hi thanks a lot. The pivot table is great. I got another problem
though.
What do I do if I want to summarise my data like so:

I have
Time C D
930 423 231
930 312 233
930 314 245
931 224 432
931 234 435
931 244 431

And I want to summarise it as
Time, C', D'
930 423 245
931 224 431
..
XYZ, (First value of C where Time is 930), (Last value of D where Time
is XYZ)

Thanks again
Click to expand...
 
If I assume that you have your time list in the range A2 through A50
and your other two columns in B2 through B50 and C2 through C50
and that you have a unique list of the items in A2 through A50 in
column D (e.g 930, 931, etc.)

Then in E1 put = max(IF(a$2:a$50=d1,b$2:b$50,0)) would give you the
max of all the 930s in column D
or conversely you could use min to get the min...

This probably uses a lot less memory than a pivot table.

Hi thanks a lot. The pivot table is great. I got another problem
though.
What do I do if I want to summarise my data like so:

I have
Time C D
930 423 231
930 312 233
930 314 245
931 224 432
931 234 435
931 244 431

And I want to summarise it as
Time, C', D'
930 423 245
931 224 431
..
XYZ, (First value of C where Time is 930), (Last value of D where Time
is XYZ)

Thanks again
Click to expand...
http://www.excelforum.com/showthread.php?threadid=263081
 
Back
Top