|
Author |
Message |
Sriman
|
Posted: Sun Jul 02 06:27:34 CDT 2006 |
Top |
worksheet functions >> Advanced filtering
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!
Excel270
|
|
|
|
|
MartinW
|
Posted: Sun Jul 02 06:27:34 CDT 2006 |
Top |
worksheet functions >> Advanced filtering
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
|
|
|
|
|
Don
|
Posted: Sun Jul 02 07:31:24 CDT 2006 |
Top |
worksheet functions >> Advanced filtering
data>filter>autofilter>custom>left window greater than>right window your
figure.
--
Don Guillett
SalesAid Software
> 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!
>
|
|
|
|
|
mutie
|
Posted: Sun Jul 02 07:46:01 CDT 2006 |
Top |
worksheet functions >> Advanced filtering
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?
> 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
>
>
>
|
|
|
|
|
Bob
|
Posted: Sun Jul 02 08:05:59 CDT 2006 |
Top |
worksheet functions >> Advanced filtering
=IF(A1>=$F$1,1,0)
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
> 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?
>
>
> > 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
|
Posted: Sun Jul 02 08:22:01 CDT 2006 |
Top |
worksheet functions >> Advanced filtering
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!
> data>filter>autofilter>custom>left window greater than>right window your
> figure.
>
> --
> Don Guillett
> SalesAid Software
> > 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!
> >
>
>
>
|
|
|
|
|
mutie
|
Posted: Sun Jul 02 08:25:01 CDT 2006 |
Top |
worksheet functions >> Advanced filtering
YEEEESSSS! it works! Just out of interest, not being too technically-minded,
how do the $ signs affect the formula?
many thanks btw :)))))
> =IF(A1>=$F$1,1,0)
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> > 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?
> >
> >
> > > 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
> > >
> > >
> > >
>
>
>
|
|
|
|
|
Nick
|
Posted: Sun Jul 02 08:44:21 CDT 2006 |
Top |
worksheet functions >> Advanced filtering
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
> YEEEESSSS! it works! Just out of interest, not being too
> technically-minded,
> how do the $ signs affect the formula?
> many thanks btw :)))))
>
>
>> =IF(A1>=$F$1,1,0)
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> > 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?
>> >
>> >
>> > > 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
>> > >
>> > >
>> > >
>>
>>
>>
|
|
|
|
|
Don
|
Posted: Sun Jul 02 09:28:43 CDT 2006 |
Top |
worksheet functions >> Advanced filtering
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
> 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!
>
>
>> data>filter>autofilter>custom>left window greater than>right window your
>> figure.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> > 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!
>> >
>>
>>
>>
|
|
|
|
|
|
|