Formula for picking out ALL, or FIRST and LAST, or ALL BUT ONE from a list?

**Gunther Maplethorpe** · 07-27-2011, 11:40 AM

I got a block of data, for which I need to do several algebraic things. I've attached a sample worksheet; if I can figure out how to do what's in there, then I can do the rest.

Essentially, I've got one column with a bunch of sample names, some of which are "STANDARD". I'd like to figure out how to do some math operations on three sets of data:

1) All "STANDARDS"
2) The first two and last STANDARD.
3) Only the first three STANDARDS.

The first one I can do...it's picking out "all but one" of them that is throwing me.

**Gunther Maplethorpe** · 08-01-2011, 01:31 PM

Bump....no one's got any ideas?

**ChemistB** · 08-01-2011, 01:47 PM

Here's some ideas. I used a dummy column though.

**zbor** · 08-01-2011, 01:52 PM

Nice solution.

I just wanted to add that when you using -- you don't need extra + (-- is + already).
So,
SUMPRODUCT(--($C$2:$C$27<3)+--($C$2:$C$27=4);$B$2:$B$27)/3
become
SUMPRODUCT(--($C$2:$C$27<3)--($C$2:$C$27=4);$B$2:$B$27)/3

and furthermore, since you have two ranges adding them up together:

SUMPRODUCT(($C$2:$C$27<3)+($C$2:$C$27=4);$B$2:$B$27)/3

**ChemistB** · 08-01-2011, 01:57 PM

Thanks Zbor!

**Gunther Maplethorpe** · 08-01-2011, 03:22 PM

Excellent work, lads...and much easier than the rather convoluted use of SMALL to coutn specific rows that I had half-bodged together in the interim...muchisimas gracias!