Preparation and Summarisation of data

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
 
F

Frank Kabel

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
 
D

Debra Dalgleish

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
 
M

marston_gould

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
http://www.excelforum.com/showthread.php?threadid=263081
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top