Rank a column but not include some cells
 

I need to rank a column but ignore some cells which do not meets a criteria
of having another columns cell filled by data.

Rank a column but not include some cells
 

Do you mean something like this:

=IF(F26="","",RANK(F26,$D26:$M26,1))

This formula is in cell F27 of a sheet which has a series of totals in
D26 to M26, some of which may be blank - the formula checks if the cell
above is blank and only returns the RANK order if the total cell is not
blank. The formula is copied across from D27 to M27.

Hope this helps.

Pete

Pillar wrote:
I need to rank a column but ignore some cells which do not meets a criteria
of having another columns cell filled by data.

Rank a column but not include some cells
 

Not exactly. I have some cells that have time data (hh:mm:ss), and some cells
are blank. I want to rank the times in ascending order BUT I want to ignore
cells whether they are blank or not depending on if a cell in ANOTHER column
is blank or not. This is my current formula which is very simial to what you
suggested.
=IF(ISBLANK(O7)," ",RANK(Q7,Q$4:Q$103,1))

What seems to be hapenning is that the cells, that have time data AND where
(O7) is blank, are showing blank as per the formula BUT they are still being
ranked as my rankings for the cells I DO want ranked are not correct. (as if
the cells which are to be blank are placeholding their "would be" rank),

I hope that is clear. Reread it slowly. hahahaha


"Pete_UK" wrote:

Do you mean something like this:

=IF(F26="","",RANK(F26,$D26:$M26,1))

This formula is in cell F27 of a sheet which has a series of totals in
D26 to M26, some of which may be blank - the formula checks if the cell
above is blank and only returns the RANK order if the total cell is not
blank. The formula is copied across from D27 to M27.

Hope this helps.

Pete

Pillar wrote:
I need to rank a column but ignore some cells which do not meets a criteria
of having another columns cell filled by data.

Rank a column but not include some cells
 

Try this:

=IF(OR(Q7="",O7=""),"",SUMPRODUCT(--(O$7:O$103<""),--(ISNUMBER(Q$7:Q$103)),--(Q7Q$7:Q$103))+1)

Copy down as needed.

Biff

"Pillar" wrote in message
...
Not exactly. I have some cells that have time data (hh:mm:ss), and some
cells
are blank. I want to rank the times in ascending order BUT I want to
ignore
cells whether they are blank or not depending on if a cell in ANOTHER
column
is blank or not. This is my current formula which is very simial to what
you
suggested.
=IF(ISBLANK(O7)," ",RANK(Q7,Q$4:Q$103,1))

What seems to be hapenning is that the cells, that have time data AND
where
(O7) is blank, are showing blank as per the formula BUT they are still
being
ranked as my rankings for the cells I DO want ranked are not correct. (as
if
the cells which are to be blank are placeholding their "would be" rank),

I hope that is clear. Reread it slowly. hahahaha


"Pete_UK" wrote:

Do you mean something like this:

=IF(F26="","",RANK(F26,$D26:$M26,1))

This formula is in cell F27 of a sheet which has a series of totals in
D26 to M26, some of which may be blank - the formula checks if the cell
above is blank and only returns the RANK order if the total cell is not
blank. The formula is copied across from D27 to M27.

Hope this helps.

Pete

Pillar wrote:
I need to rank a column but ignore some cells which do not meets a
criteria
of having another columns cell filled by data.

Rank a column but not include some cells
 

Thanks Biff, But that did not work. Maybe I'm not explaining what I am trying
to accomplish well enough. let me try again.

1) I have a column (Q) where the cells have time data (hh:mm:ss), and some
cells
are blank.
2) I want to rank the times in ascending order.
3) I want to rank againt Q4:Q103
4) There are some cells included within Q4:Q103 that I do not what to rank
against if a criteria is not met for those cells.

So example:
Rank Q5 against Q4:Q103 BUT only against the cells within Q4:Q103 that
"quaify to be ranked" by having a cell within their row filled with data.
Return the value in R5.

Is that clearer. I hope so. Thanks



"Biff" wrote:

Try this:

=IF(OR(Q7="",O7=""),"",SUMPRODUCT(--(O$7:O$103<""),--(ISNUMBER(Q$7:Q$103)),--(Q7Q$7:Q$103))+1)

Copy down as needed.

Biff

"Pillar" wrote in message
...
Not exactly. I have some cells that have time data (hh:mm:ss), and some
cells
are blank. I want to rank the times in ascending order BUT I want to
ignore
cells whether they are blank or not depending on if a cell in ANOTHER
column
is blank or not. This is my current formula which is very simial to what
you
suggested.
=IF(ISBLANK(O7)," ",RANK(Q7,Q$4:Q$103,1))

What seems to be hapenning is that the cells, that have time data AND
where
(O7) is blank, are showing blank as per the formula BUT they are still
being
ranked as my rankings for the cells I DO want ranked are not correct. (as
if
the cells which are to be blank are placeholding their "would be" rank),

I hope that is clear. Reread it slowly. hahahaha


"Pete_UK" wrote:

Do you mean something like this:

=IF(F26="","",RANK(F26,$D26:$M26,1))

This formula is in cell F27 of a sheet which has a series of totals in
D26 to M26, some of which may be blank - the formula checks if the cell
above is blank and only returns the RANK order if the total cell is not
blank. The formula is copied across from D27 to M27.

Hope this helps.

Pete

Pillar wrote:
I need to rank a column but ignore some cells which do not meets a
criteria
of having another columns cell filled by data.

Rank a column but not include some cells
 

Maybe if you post a small sample it would help.

Biff

"Pillar" wrote in message
...
Thanks Biff, But that did not work. Maybe I'm not explaining what I am
trying
to accomplish well enough. let me try again.

1) I have a column (Q) where the cells have time data (hh:mm:ss), and some
cells
are blank.
2) I want to rank the times in ascending order.
3) I want to rank againt Q4:Q103
4) There are some cells included within Q4:Q103 that I do not what to rank
against if a criteria is not met for those cells.

So example:
Rank Q5 against Q4:Q103 BUT only against the cells within Q4:Q103 that
"quaify to be ranked" by having a cell within their row filled with data.
Return the value in R5.

Is that clearer. I hope so. Thanks



"Biff" wrote:

Try this:

=IF(OR(Q7="",O7=""),"",SUMPRODUCT(--(O$7:O$103<""),--(ISNUMBER(Q$7:Q$103)),--(Q7Q$7:Q$103))+1)

Copy down as needed.

Biff

"Pillar" wrote in message
...
Not exactly. I have some cells that have time data (hh:mm:ss), and some
cells
are blank. I want to rank the times in ascending order BUT I want to
ignore
cells whether they are blank or not depending on if a cell in ANOTHER
column
is blank or not. This is my current formula which is very simial to
what
you
suggested.
=IF(ISBLANK(O7)," ",RANK(Q7,Q$4:Q$103,1))

What seems to be hapenning is that the cells, that have time data AND
where
(O7) is blank, are showing blank as per the formula BUT they are still
being
ranked as my rankings for the cells I DO want ranked are not correct.
(as
if
the cells which are to be blank are placeholding their "would be"
rank),

I hope that is clear. Reread it slowly. hahahaha


"Pete_UK" wrote:

Do you mean something like this:

=IF(F26="","",RANK(F26,$D26:$M26,1))

This formula is in cell F27 of a sheet which has a series of totals in
D26 to M26, some of which may be blank - the formula checks if the
cell
above is blank and only returns the RANK order if the total cell is
not
blank. The formula is copied across from D27 to M27.

Hope this helps.

Pete

Pillar wrote:
I need to rank a column but ignore some cells which do not meets a
criteria
of having another columns cell filled by data.

Rank a column but not include some cells
 

Ok. This is how it looks now.

lap 1 ET (D)...lap2 ET (G)...lap 3 ET (K)...lap 4 ET (O) ... Total ET (Q)
Final Rank (R)
2:16:32 2:04:26 1:33:30 1:08:46 7:03:32
3
1:32:11 1:27:39
2:59:50
1:13:46 1:08:51 1:10:08 1:09:56 4:42:41
2

So let me try to explain. This is an abridged version. This spread sheet is
for offroad desert race cars. The race is 4 43 mile laps. The first car
finishes all four laps. Since the first car ans the third car finished all 4
laps I want to rank them 1 and 2. The second car broke down and did not
finish all 4 laps, therefore I do not want to include it's total ET in the
ranking. So in essence when car 1 gets ranks it needs to be ranks only
against other cars which have completed all 4 laps.

This is how I want it to look:

lap 1 ET (D)...lap2 ET (G)...lap 3 ET (K)...lap 4 ET (O) ... Total ET (Q)
Final Rank (R)
2:16:32 2:04:26 1:33:30 1:08:46 7:03:32
2
1:32:11 1:27:39
2:59:50
1:13:46 1:08:51 1:10:08 1:09:56 4:42:41
1

Does that help? Thanks for the efforts.


"Biff" wrote:

Maybe if you post a small sample it would help.

Biff

"Pillar" wrote in message
...
Thanks Biff, But that did not work. Maybe I'm not explaining what I am
trying
to accomplish well enough. let me try again.

1) I have a column (Q) where the cells have time data (hh:mm:ss), and some
cells
are blank.
2) I want to rank the times in ascending order.
3) I want to rank againt Q4:Q103
4) There are some cells included within Q4:Q103 that I do not what to rank
against if a criteria is not met for those cells.

So example:
Rank Q5 against Q4:Q103 BUT only against the cells within Q4:Q103 that
"quaify to be ranked" by having a cell within their row filled with data.
Return the value in R5.

Is that clearer. I hope so. Thanks



"Biff" wrote:

Try this:

=IF(OR(Q7="",O7=""),"",SUMPRODUCT(--(O$7:O$103<""),--(ISNUMBER(Q$7:Q$103)),--(Q7Q$7:Q$103))+1)

Copy down as needed.

Biff

"Pillar" wrote in message
...
Not exactly. I have some cells that have time data (hh:mm:ss), and some
cells
are blank. I want to rank the times in ascending order BUT I want to
ignore
cells whether they are blank or not depending on if a cell in ANOTHER
column
is blank or not. This is my current formula which is very simial to
what
you
suggested.
=IF(ISBLANK(O7)," ",RANK(Q7,Q$4:Q$103,1))

What seems to be hapenning is that the cells, that have time data AND
where
(O7) is blank, are showing blank as per the formula BUT they are still
being
ranked as my rankings for the cells I DO want ranked are not correct.
(as
if
the cells which are to be blank are placeholding their "would be"
rank),

I hope that is clear. Reread it slowly. hahahaha


"Pete_UK" wrote:

Do you mean something like this:

=IF(F26="","",RANK(F26,$D26:$M26,1))

This formula is in cell F27 of a sheet which has a series of totals in
D26 to M26, some of which may be blank - the formula checks if the
cell
above is blank and only returns the RANK order if the total cell is
not
blank. The formula is copied across from D27 to M27.

Hope this helps.

Pete

Pillar wrote:
I need to rank a column but ignore some cells which do not meets a
criteria
of having another columns cell filled by data.

Rank a column but not include some cells
 

If you use a helper column to count the number of laps this is fairly easy.
It can probably be done without the helper column but would be much more
complicated.

Here's a sample file:

Rank_lap_times.xls 13.5kb

http://cjoint.com/?khxiJOB80F

Biff

"Pillar" wrote in message
...
Ok. This is how it looks now.

lap 1 ET (D)...lap2 ET (G)...lap 3 ET (K)...lap 4 ET (O) ... Total ET (Q)
Final Rank (R)
2:16:32 2:04:26 1:33:30 1:08:46 7:03:32
3
1:32:11 1:27:39
2:59:50
1:13:46 1:08:51 1:10:08 1:09:56 4:42:41
2

So let me try to explain. This is an abridged version. This spread sheet
is
for offroad desert race cars. The race is 4 43 mile laps. The first car
finishes all four laps. Since the first car ans the third car finished all
4
laps I want to rank them 1 and 2. The second car broke down and did not
finish all 4 laps, therefore I do not want to include it's total ET in the
ranking. So in essence when car 1 gets ranks it needs to be ranks only
against other cars which have completed all 4 laps.

This is how I want it to look:

lap 1 ET (D)...lap2 ET (G)...lap 3 ET (K)...lap 4 ET (O) ... Total ET (Q)
Final Rank (R)
2:16:32 2:04:26 1:33:30 1:08:46 7:03:32
2
1:32:11 1:27:39
2:59:50
1:13:46 1:08:51 1:10:08 1:09:56 4:42:41
1

Does that help? Thanks for the efforts.


"Biff" wrote:

Maybe if you post a small sample it would help.

Biff

"Pillar" wrote in message
...
Thanks Biff, But that did not work. Maybe I'm not explaining what I am
trying
to accomplish well enough. let me try again.

1) I have a column (Q) where the cells have time data (hh:mm:ss), and
some
cells
are blank.
2) I want to rank the times in ascending order.
3) I want to rank againt Q4:Q103
4) There are some cells included within Q4:Q103 that I do not what to
rank
against if a criteria is not met for those cells.

So example:
Rank Q5 against Q4:Q103 BUT only against the cells within Q4:Q103 that
"quaify to be ranked" by having a cell within their row filled with
data.
Return the value in R5.

Is that clearer. I hope so. Thanks



"Biff" wrote:

Try this:

=IF(OR(Q7="",O7=""),"",SUMPRODUCT(--(O$7:O$103<""),--(ISNUMBER(Q$7:Q$103)),--(Q7Q$7:Q$103))+1)

Copy down as needed.

Biff

"Pillar" wrote in message
...
Not exactly. I have some cells that have time data (hh:mm:ss), and
some
cells
are blank. I want to rank the times in ascending order BUT I want to
ignore
cells whether they are blank or not depending on if a cell in
ANOTHER
column
is blank or not. This is my current formula which is very simial to
what
you
suggested.
=IF(ISBLANK(O7)," ",RANK(Q7,Q$4:Q$103,1))

What seems to be hapenning is that the cells, that have time data
AND
where
(O7) is blank, are showing blank as per the formula BUT they are
still
being
ranked as my rankings for the cells I DO want ranked are not
correct.
(as
if
the cells which are to be blank are placeholding their "would be"
rank),

I hope that is clear. Reread it slowly. hahahaha


"Pete_UK" wrote:

Do you mean something like this:

=IF(F26="","",RANK(F26,$D26:$M26,1))

This formula is in cell F27 of a sheet which has a series of totals
in
D26 to M26, some of which may be blank - the formula checks if the
cell
above is blank and only returns the RANK order if the total cell is
not
blank. The formula is copied across from D27 to M27.

Hope this helps.

Pete

Pillar wrote:
I need to rank a column but ignore some cells which do not meets
a
criteria
of having another columns cell filled by data.

Rank a column but not include some cells
 

Let me try to make that look better:

Ok. This is how it looks now.
col (D) col(G) col (K) col (O) col (Q) col (R)
lap 1 ET lap2 ET lap 3 ET lap 4 ET Total ET Rank
2:16:32 2:04:26 1:33:30 1:08:46 7:03:32 3
1:32:11 1:27:39 2:59:50
1:13:46 1:08:51 1:10:08 1:09:56 4:42:41 2

So let me try to explain. This is an abridged version. This spread sheet is
for offroad desert race cars. The race is 4 43 mile laps. The first car
finishes all four laps. Since the first car and the third car finished all 4
laps I want to rank them 1 and 2. The second car broke down and did not
finish all 4 laps, therefore I do not want to include it's total ET in the
ranking. So in essence when car 1 gets ranks it needs to be ranked only
against other cars which have completed all 4 laps.

This is how I want it to look:

col (D) col(G) col (K) col (O) col (Q) col (R)
lap 1 ET lap2 ET lap 3 ET lap 4 ET Total ET Rank
2:16:32 2:04:26 1:33:30 1:08:46 7:03:32 2
1:32:11 1:27:39 2:59:50
1:13:46 1:08:51 1:10:08 1:09:56 4:42:41 1

Does that help? Thanks for the efforts.


"Biff" wrote:

Maybe if you post a small sample it would help.

Biff

"Pillar" wrote in message
...
Thanks Biff, But that did not work. Maybe I'm not explaining what I am
trying
to accomplish well enough. let me try again.

1) I have a column (Q) where the cells have time data (hh:mm:ss), and some
cells
are blank.
2) I want to rank the times in ascending order.
3) I want to rank againt Q4:Q103
4) There are some cells included within Q4:Q103 that I do not what to rank
against if a criteria is not met for those cells.

So example:
Rank Q5 against Q4:Q103 BUT only against the cells within Q4:Q103 that
"quaify to be ranked" by having a cell within their row filled with data.
Return the value in R5.

Is that clearer. I hope so. Thanks



"Biff" wrote:

Try this:

=IF(OR(Q7="",O7=""),"",SUMPRODUCT(--(O$7:O$103<""),--(ISNUMBER(Q$7:Q$103)),--(Q7Q$7:Q$103))+1)

Copy down as needed.

Biff

"Pillar" wrote in message
...
Not exactly. I have some cells that have time data (hh:mm:ss), and some
cells
are blank. I want to rank the times in ascending order BUT I want to
ignore
cells whether they are blank or not depending on if a cell in ANOTHER
column
is blank or not. This is my current formula which is very simial to
what
you
suggested.
=IF(ISBLANK(O7)," ",RANK(Q7,Q$4:Q$103,1))

What seems to be hapenning is that the cells, that have time data AND
where
(O7) is blank, are showing blank as per the formula BUT they are still
being
ranked as my rankings for the cells I DO want ranked are not correct.
(as
if
the cells which are to be blank are placeholding their "would be"
rank),

I hope that is clear. Reread it slowly. hahahaha


"Pete_UK" wrote:

Do you mean something like this:

=IF(F26="","",RANK(F26,$D26:$M26,1))

This formula is in cell F27 of a sheet which has a series of totals in
D26 to M26, some of which may be blank - the formula checks if the
cell
above is blank and only returns the RANK order if the total cell is
not
blank. The formula is copied across from D27 to M27.

Hope this helps.

Pete

Pillar wrote:
I need to rank a column but ignore some cells which do not meets a
criteria
of having another columns cell filled by data.

Rank a column but not include some cells
 

That did it! Thank you for sticking with me. I didn't think about using a
helper column. I would be interested in how to make it work without it but
the helper coulumn is actually usefull information that helps the users.

Thanks again,
Pillar

"Biff" wrote:

If you use a helper column to count the number of laps this is fairly easy.
It can probably be done without the helper column but would be much more
complicated.

Here's a sample file:

Rank_lap_times.xls 13.5kb

http://cjoint.com/?khxiJOB80F

Biff

"Pillar" wrote in message
...
Ok. This is how it looks now.

lap 1 ET (D)...lap2 ET (G)...lap 3 ET (K)...lap 4 ET (O) ... Total ET (Q)
Final Rank (R)
2:16:32 2:04:26 1:33:30 1:08:46 7:03:32
3
1:32:11 1:27:39
2:59:50
1:13:46 1:08:51 1:10:08 1:09:56 4:42:41
2

So let me try to explain. This is an abridged version. This spread sheet
is
for offroad desert race cars. The race is 4 43 mile laps. The first car
finishes all four laps. Since the first car ans the third car finished all
4
laps I want to rank them 1 and 2. The second car broke down and did not
finish all 4 laps, therefore I do not want to include it's total ET in the
ranking. So in essence when car 1 gets ranks it needs to be ranks only
against other cars which have completed all 4 laps.

This is how I want it to look:

lap 1 ET (D)...lap2 ET (G)...lap 3 ET (K)...lap 4 ET (O) ... Total ET (Q)
Final Rank (R)
2:16:32 2:04:26 1:33:30 1:08:46 7:03:32
2
1:32:11 1:27:39
2:59:50
1:13:46 1:08:51 1:10:08 1:09:56 4:42:41
1

Does that help? Thanks for the efforts.


"Biff" wrote:

Maybe if you post a small sample it would help.

Biff

"Pillar" wrote in message
...
Thanks Biff, But that did not work. Maybe I'm not explaining what I am
trying
to accomplish well enough. let me try again.

1) I have a column (Q) where the cells have time data (hh:mm:ss), and
some
cells
are blank.
2) I want to rank the times in ascending order.
3) I want to rank againt Q4:Q103
4) There are some cells included within Q4:Q103 that I do not what to
rank
against if a criteria is not met for those cells.

So example:
Rank Q5 against Q4:Q103 BUT only against the cells within Q4:Q103 that
"quaify to be ranked" by having a cell within their row filled with
data.
Return the value in R5.

Is that clearer. I hope so. Thanks



"Biff" wrote:

Try this:

=IF(OR(Q7="",O7=""),"",SUMPRODUCT(--(O$7:O$103<""),--(ISNUMBER(Q$7:Q$103)),--(Q7Q$7:Q$103))+1)

Copy down as needed.

Biff

"Pillar" wrote in message
...
Not exactly. I have some cells that have time data (hh:mm:ss), and
some
cells
are blank. I want to rank the times in ascending order BUT I want to
ignore
cells whether they are blank or not depending on if a cell in
ANOTHER
column
is blank or not. This is my current formula which is very simial to
what
you
suggested.
=IF(ISBLANK(O7)," ",RANK(Q7,Q$4:Q$103,1))

What seems to be hapenning is that the cells, that have time data
AND
where
(O7) is blank, are showing blank as per the formula BUT they are
still
being
ranked as my rankings for the cells I DO want ranked are not
correct.
(as
if
the cells which are to be blank are placeholding their "would be"
rank),

I hope that is clear. Reread it slowly. hahahaha


"Pete_UK" wrote:

Do you mean something like this:

=IF(F26="","",RANK(F26,$D26:$M26,1))

This formula is in cell F27 of a sheet which has a series of totals
in
D26 to M26, some of which may be blank - the formula checks if the
cell
above is blank and only returns the RANK order if the total cell is
not
blank. The formula is copied across from D27 to M27.

Hope this helps.

Pete

Pillar wrote:
I need to rank a column but ignore some cells which do not meets
a
criteria
of having another columns cell filled by data.

Rank a column but not include some cells
 

Without the helper column, based on the layout of the sample file:

=IF(COUNT(B2:E2)<4,"",SUMPRODUCT(--(SUBTOTAL(2,OFFSET(B$2:E$10,ROW(B$2:E$10)-ROW(B$2),,1))=4),--(G2G$2:G$10))+1)

Copied down

Biff

"Pillar" wrote in message
...
That did it! Thank you for sticking with me. I didn't think about using a
helper column. I would be interested in how to make it work without it but
the helper coulumn is actually usefull information that helps the users.

Thanks again,
Pillar

"Biff" wrote:

If you use a helper column to count the number of laps this is fairly
easy.
It can probably be done without the helper column but would be much more
complicated.

Here's a sample file:

Rank_lap_times.xls 13.5kb

http://cjoint.com/?khxiJOB80F

Biff

"Pillar" wrote in message
...
Ok. This is how it looks now.

lap 1 ET (D)...lap2 ET (G)...lap 3 ET (K)...lap 4 ET (O) ... Total ET
(Q)
Final Rank (R)
2:16:32 2:04:26 1:33:30 1:08:46
7:03:32
3
1:32:11 1:27:39
2:59:50
1:13:46 1:08:51 1:10:08 1:09:56
4:42:41
2

So let me try to explain. This is an abridged version. This spread
sheet
is
for offroad desert race cars. The race is 4 43 mile laps. The first car
finishes all four laps. Since the first car ans the third car finished
all
4
laps I want to rank them 1 and 2. The second car broke down and did not
finish all 4 laps, therefore I do not want to include it's total ET in
the
ranking. So in essence when car 1 gets ranks it needs to be ranks only
against other cars which have completed all 4 laps.

This is how I want it to look:

lap 1 ET (D)...lap2 ET (G)...lap 3 ET (K)...lap 4 ET (O) ... Total ET
(Q)
Final Rank (R)
2:16:32 2:04:26 1:33:30 1:08:46
7:03:32
2
1:32:11 1:27:39
2:59:50
1:13:46 1:08:51 1:10:08 1:09:56
4:42:41
1

Does that help? Thanks for the efforts.


"Biff" wrote:

Maybe if you post a small sample it would help.

Biff

"Pillar" wrote in message
...
Thanks Biff, But that did not work. Maybe I'm not explaining what I
am
trying
to accomplish well enough. let me try again.

1) I have a column (Q) where the cells have time data (hh:mm:ss),
and
some
cells
are blank.
2) I want to rank the times in ascending order.
3) I want to rank againt Q4:Q103
4) There are some cells included within Q4:Q103 that I do not what
to
rank
against if a criteria is not met for those cells.

So example:
Rank Q5 against Q4:Q103 BUT only against the cells within Q4:Q103
that
"quaify to be ranked" by having a cell within their row filled with
data.
Return the value in R5.

Is that clearer. I hope so. Thanks



"Biff" wrote:

Try this:

=IF(OR(Q7="",O7=""),"",SUMPRODUCT(--(O$7:O$103<""),--(ISNUMBER(Q$7:Q$103)),--(Q7Q$7:Q$103))+1)

Copy down as needed.

Biff

"Pillar" wrote in message
...
Not exactly. I have some cells that have time data (hh:mm:ss),
and
some
cells
are blank. I want to rank the times in ascending order BUT I want
to
ignore
cells whether they are blank or not depending on if a cell in
ANOTHER
column
is blank or not. This is my current formula which is very simial
to
what
you
suggested.
=IF(ISBLANK(O7)," ",RANK(Q7,Q$4:Q$103,1))

What seems to be hapenning is that the cells, that have time data
AND
where
(O7) is blank, are showing blank as per the formula BUT they are
still
being
ranked as my rankings for the cells I DO want ranked are not
correct.
(as
if
the cells which are to be blank are placeholding their "would be"
rank),

I hope that is clear. Reread it slowly. hahahaha


"Pete_UK" wrote:

Do you mean something like this:

=IF(F26="","",RANK(F26,$D26:$M26,1))

This formula is in cell F27 of a sheet which has a series of
totals
in
D26 to M26, some of which may be blank - the formula checks if
the
cell
above is blank and only returns the RANK order if the total cell
is
not
blank. The formula is copied across from D27 to M27.

Hope this helps.

Pete

Pillar wrote:
I need to rank a column but ignore some cells which do not
meets
a
criteria
of having another columns cell filled by data.