Ranking Only Visible Cells

[Jim]

I have a spreasheet with store numbers in column A and the store's sales in
column B. Example:

1 $500.00
2 $497.38
3 $557.15

etc.

There are 70 stores but only 64 of them are OFFICIALLY generating sales. I
have hidden the stores that are generating sales UNOFFICIALLY and want to
rank the visible stores only.

Is there a way to rank only the visible cells? The hidden stores are
scattered throughout and will become "official" at different times, so
"hard-coding" the formula would be a pain (there are many of these
spreadsheets with different information).

Is this even possible?

Thanks!

 

[Test]

If you've hidden the relevant store cells/rows it won't matter whether
they're ranked or not because they're hidden.

 

[Jim]

Normally that would be true, but some of the open stores rank lower than the
"closed" stores and therefore the rankings are going from 32 to 38 with the
hidden stores making up 32-37.

I'm guessing it's not possible.

 

[Lewis Clark]

Hi Jim,

This is not exactly what you are asking for, but a forumula like this may
work for you.

=IF(A1="c"), "",SUMPRODUCT(--($A$1:$A$40<>"c"), --($B1<$B$1:$B$40))+1)

This assumes column A will contain the letter C if a store is closed, and
your data is in rows 1 - 40. Put this formula in row 1 of your "rank"
column and copy down. Change the ranges to suit your data.

If a store is closed (column A = "C") it will leave the rank colum blank.
If the store is not closed, it will count the number of cells in the column
B range whose value is less than the value in that row. For example, in row
1, it will count the number of cells in the range B1:B40 that are larger
than B1. If B1 is the largest value this will return a count of zero, and
then add 1 to give us a rank of 1. If B1 is the second largest value, it
will return a count of 1, and then add 1 to give a rank of 2. And so on
down the line.

Hope this helps.

 

[Jim]

That was perfect! Thank you so much! I'm so not "up" on SUMPRODUCT

 

[Lewis Clark]

Glad it worked for you. These newsgroups are a fantastic resource!