Hi there. I have a series of data from an experiment that I'm trying to
"trim" extreme results from. I have calcuated the 0.97 perecntile, but I'm
wondering if there's a way to filter out allthe results above that
percentile. The help sheets are pretty confusing and not very well
constructed!

Hi mutie,

Say your data is in column A1 to A100 and your 0.97 percentile figure
is in cell F1.
in B1 put =IF(A1=F1,1,0) and copy down to B100
You can then use autofilter on column B to select all the zeroes (or all the
ones)
Depending on your setup you may want to use a temporary column
to do this and delete it later.

HTH
Martin

datafilterautofiltercustomleft window greater thanright window your
figure.

--
Don Guillett
SalesAid Software

"mutie" wrote in message
...
Hi there. I have a series of data from an experiment that I'm trying to
"trim" extreme results from. I have calcuated the 0.97 perecntile, but I'm
wondering if there's a way to filter out allthe results above that
percentile. The help sheets are pretty confusing and not very well
constructed!

seems to work ok up to a point. However, taking your example, when I try to
fill or copy down to B100, the formula in each cell changes, F1 becoming
F2,F3....and so on. Is there any way of stopping this from happening?

"MartinW" wrote:

Hi mutie,

Say your data is in column A1 to A100 and your 0.97 percentile figure
is in cell F1.
in B1 put =IF(A1=F1,1,0) and copy down to B100
You can then use autofilter on column B to select all the zeroes (or all the
ones)
Depending on your setup you may want to use a temporary column
to do this and delete it later.

HTH
Martin

=IF(A1=$F$1,1,0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mutie" wrote in message
...
seems to work ok up to a point. However, taking your example, when I try
to
fill or copy down to B100, the formula in each cell changes, F1 becoming
F2,F3....and so on. Is there any way of stopping this from happening?

"MartinW" wrote:

Hi mutie,

Say your data is in column A1 to A100 and your 0.97 percentile figure
is in cell F1.
in B1 put =IF(A1=F1,1,0) and copy down to B100
You can then use autofilter on column B to select all the zeroes (or all
the
ones)
Depending on your setup you may want to use a temporary column
to do this and delete it later.

HTH
Martin

thats another good suggestion and seems to work. Unfortunately I have to do a
similar operation about 8 times with different sets of data in the same
worksheet and then do the same with another 20 sets of worksheets. I'm trying
to work out how to do it automatically, so that I can then create a macro.
The weird thing is, I can get the program to filter using
=D2<PERCENTILE(D2:D36,0.97)
So, D2 is the start of my data, D36 the end. I've put the formula in J2, but
when I filter using this, it doesn't filter correctly!

"Don Guillett" wrote:

datafilterautofiltercustomleft window greater thanright window your
figure.

--
Don Guillett
SalesAid Software

"mutie" wrote in message
...
Hi there. I have a series of data from an experiment that I'm trying to
"trim" extreme results from. I have calcuated the 0.97 perecntile, but I'm
wondering if there's a way to filter out allthe results above that
percentile. The help sheets are pretty confusing and not very well
constructed!

YEEEESSSS! it works! Just out of interest, not being too technically-minded,
how do the $ signs affect the formula?
many thanks btw :)))))

"Bob Phillips" wrote:

=IF(A1=$F$1,1,0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mutie" wrote in message
...
seems to work ok up to a point. However, taking your example, when I try
to
fill or copy down to B100, the formula in each cell changes, F1 becoming
F2,F3....and so on. Is there any way of stopping this from happening?

"MartinW" wrote:

Hi mutie,

Say your data is in column A1 to A100 and your 0.97 percentile figure
is in cell F1.
in B1 put =IF(A1=F1,1,0) and copy down to B100
You can then use autofilter on column B to select all the zeroes (or all
the
ones)
Depending on your setup you may want to use a temporary column
to do this and delete it later.

HTH
Martin

Mutie

It stops the references from updating when moved. In the case of $A$1 it
will always refer to that cell even if dragged to say C1, whereas a formula
with A1 would read B1 if dragged one cell right

Check out help for absolute and relative references. There are four settings

=A1 (Relative row and column reference)
=$A1 (Relative row, absolute column)
=A$1 (Absolute row, relative column)
=$A$1(Absolute row and column)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"mutie" wrote in message
...
YEEEESSSS! it works! Just out of interest, not being too
technically-minded,
how do the $ signs affect the formula?
many thanks btw :)))))

"Bob Phillips" wrote:

=IF(A1=$F$1,1,0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mutie" wrote in message
...
seems to work ok up to a point. However, taking your example, when I
try
to
fill or copy down to B100, the formula in each cell changes, F1
becoming
F2,F3....and so on. Is there any way of stopping this from happening?

"MartinW" wrote:

Hi mutie,

Say your data is in column A1 to A100 and your 0.97 percentile figure
is in cell F1.
in B1 put =IF(A1=F1,1,0) and copy down to B100
You can then use autofilter on column B to select all the zeroes (or
all
the
ones)
Depending on your setup you may want to use a temporary column
to do this and delete it later.

HTH
Martin

the macro recorder is your friend. Recorded this. See clean up below
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 7/2/2006 by Don Guillett
'

'
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="2", Operator:=xlAnd
Selection.AutoFilter
End Sub

Sub Macro4cleande()
Range("A1").autoFilter Field:=1, Criteria1:="2", Operator:=xlAnd
do your stuff with the filtered cells see vba help index for special cells
and then visible.
range("a1").AutoFilter
End Sub


--
Don Guillett
SalesAid Software

"mutie" wrote in message
...
thats another good suggestion and seems to work. Unfortunately I have to
do a
similar operation about 8 times with different sets of data in the same
worksheet and then do the same with another 20 sets of worksheets. I'm
trying
to work out how to do it automatically, so that I can then create a macro.
The weird thing is, I can get the program to filter using
=D2<PERCENTILE(D2:D36,0.97)
So, D2 is the start of my data, D36 the end. I've put the formula in J2,
but
when I filter using this, it doesn't filter correctly!

"Don Guillett" wrote:

datafilterautofiltercustomleft window greater thanright window your
figure.

--
Don Guillett
SalesAid Software

"mutie" wrote in message
...
Hi there. I have a series of data from an experiment that I'm trying to
"trim" extreme results from. I have calcuated the 0.97 perecntile, but
I'm
wondering if there's a way to filter out allthe results above that
percentile. The help sheets are pretty confusing and not very well
constructed!