Rank a column but not include some cells

[Guest]

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

 

[Pete_UK]

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

 

[Guest]

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

 

[Biff]

Try this:

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

Copy down as needed.

Biff

 

[Guest]

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]

Maybe if you post a small sample it would help.

Biff

 

[Guest]

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]

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

 

[Guest]

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.

 

[Guest]

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]

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),--(G2>G$2:G$10))+1)

Copied down

Biff