conditional sums

[Peter Nelson]

I am trying to calculate some diversity indices from a fairly large database
(nearly 47000 rows). The data are set up as follows

A B C
sample species # ind
1 a 83
1 b 2
1 e 1
2 b 12
2 c 4
3 a 1200
3 c 16
3 d 1
3 e 5

where "# ind" refers to the number of individuals for each species.

I need to calculate (among other things) the total sum of all individuals
for each sample (e.g. sample 1: 83+2+1=86).

How can I sum only those cells in the '# ind' column belonging to a given
sample? As far as I can determine, I can't use the conditional sum features.
It's easy enough to identify the start of each sample (=(A1=A2) is FALSE for
the first row of each sample and true for the subsequent rows of a given
sample, but coming up with a way of 'halting' the summation in col D with
each new FALSE has me stumped.

Thanks in advance for your help. Pete

 

[Bob Greenblatt]

I am trying to calculate some diversity indices from a fairly large database
(nearly 47000 rows). The data are set up as follows

A B C
sample species # ind
1 a 83
1 b 2
1 e 1
2 b 12
2 c 4
3 a 1200
3 c 16
3 d 1
3 e 5

where "# ind" refers to the number of individuals for each species.

I need to calculate (among other things) the total sum of all individuals
for each sample (e.g. sample 1: 83+2+1=86).

How can I sum only those cells in the '# ind' column belonging to a given
sample? As far as I can determine, I can't use the conditional sum features.
It's easy enough to identify the start of each sample (=(A1=A2) is FALSE for
the first row of each sample and true for the subsequent rows of a given
sample, but coming up with a way of 'halting' the summation in col D with
each new FALSE has me stumped.

Thanks in advance for your help. Pete

Have you tried subtotals? Seems as though this will do exactly what you
want.

 

[Peter Nelson]

in BBCC39CF.44C23%[email protected], Bob Greenblatt wrote on 11/3/03 1:58 PM:

Have you tried subtotals? Seems as though this will do exactly what you
want.

I don't think that subtotals will do b/c the number of rows for each sample
varies...is there something I'm missing?

 

[Michael R Middleton]

Peter Nelson -

Peter Nelson wrote: How can I sum only those cells in the '# ind' column
belonging to a given sample?

Bob Greenblatt wrote: Have you tried subtotals?

Peter Nelson wrote: I don't think that subtotals will do b/c the number of
rows for each sample varies...is there something I'm missing?

Yes, you seem to be not trying Data Subtotals.

You may need to sort first so that all rows for each sample are adjacent.

- Mike Middleton, www.usfca.edu/~middleton

 

[Bernard Rey]

Peter Nelson wrote :

I don't think that subtotals will do b/c the number of rows for each sample
varies...is there something I'm missing?

Just try Subtotals (from the "Data" menu). I agree with Bob: this should
really be what you're looking for.

 

[Peter Nelson]

Wow! Cool!! You guys are the best...thanks for your help. Didn't know about
this feature before. Thanks again, Pete