Excel 97/2000 - Autofilter - Top...... or how will I get the right date !

[Ronald]

Hi,

Im trying to find my perfect date using Excel (haha).
I have all my friends in a database, with Autofilter on.
Since I'm male, I first filter on the column sex=f, than second filter is on
the column married=n.
This works ok, all my non-married female friends are showing. Next I filter
on salary=top 3 and the result is empty!
I know that my non-married, female friends are not in the salary top-3 of my
database, but that is not what I'm looking for. Im looking for the top-3
salary unmarried girls!
What do I do wrong, this way I always stay single !

Regards, Ronald

Ps. Yes I know, using Excel for a dating match isnt althogether a good idea
;-)

 

[Andy Wiggins]

You sound as though you are trying to get a life. I believe that you have
taken the first correct step in trying to use your computer to analyse the
available data and make a decision based on the results. The second step is
to turn off the computer and meet real people, or you'll end up like those
of us who ..... I just figured out where I went wrong

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Ronald]

You sound as though you are trying to get a life. I believe that you
have taken the first correct step in trying to use your computer to
analyse the available data and make a decision based on the results.
The second step is to turn off the computer and meet real people, or
you'll end up like those of us who ..... I just figured out where I
went wrong

Thx Andy, but it is an genuin Excel question, not a request for social
counceling.

Regards Ronald

 

[Debra Dalgleish]

Add a column (SalCalc) to the table, and enter a SUBTOTAL formula, that
refers to the salary column. For example, if salary is in column K,
enter the following formula in row 2 of the SalCalc column:

=SUBTOTAL(9,K2)

After you apply the filters, filter the SalCalc column for Top 3.

Subtotal calculates visible rows only, if a filter has been applied.

 

[Ronald]

Add a column (SalCalc) to the table, and enter a SUBTOTAL formula,
that refers to the salary column. For example, if salary is in column
K,
enter the following formula in row 2 of the SalCalc column:

=SUBTOTAL(9,K2)

After you apply the filters, filter the SalCalc column for Top 3.

Subtotal calculates visible rows only, if a filter has been applied.

Ronald wrote:

Hi debra, this seems to work, a bit. I know have 3 unmarried females as
output of my filter but also a married one.....help, her husband is a mean
one ;-)

Regards Ronald

 

[Ronald]

Debra, pls ignore previuos reply, found the mistake in the sheet.

Thx Ronald

 

[Debra Dalgleish]

Because of all the subtotals, it may include the last row of the table.
After the last record, add a grand total, e.g. =SUBTOTAL(9,F2:F36)
and the filter should return the top 3, and the grand total row.

 

[Dave Peterson]

That worked ok, but why didn't this:

=IF(ISERROR(MATCH("me","richsinglewoman",0)),MATCH("Me","singlewoman",0),
"rent another video")
(all one cell, just like a prison cell.)

<gd&r vvf>

 

[Debra Dalgleish]

"Eat your heart out" Clarice Starling <g>

That worked ok, but why didn't this:

=IF(ISERROR(MATCH("me","richsinglewoman",0)),MATCH("Me","singlewoman",0),
"rent another video")
(all one cell, just like a prison cell.)

<gd&r vvf>

 

[Dave Peterson]

Or your liver with some nice fava beans! (yewwww!)