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

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.

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.

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.

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.

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.