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.
|