How do I make a rank equation that omits particular cells?

K

KSL

I have sales figures for certain reps in column E, and am trying to rank them
all in column H. The problem is that they are grouped by region and column E
also includes the regional totals. How do I specify the range while omitting
the four cells in colum E that have the regional fiigures? I need to rank
the reps only and the celss that contain the regional figures throws the
ranking off. Right now I have the formula as =RANK(E2,E:E). This includes
cells that I would like to omit in the calculation. Can someone please help?
Desperate. Thanks!
 
G

Gary''s Student

The solution is easy if the regional totals are always larger than any
individual rep. If this is true, then the regional total cells will always
rank 1,2,3,4

Thus to compensate for this, use:

=RANK(E2,E:E)-4

I will admit that this approach is a little cheesy, but what the heck?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

trouble ranking data... 1
How can I rank values in multiple subsets using a single formula? 4
how do i rank based on other rankings? 10
How do I exclude cells from a formula? 3
how do I average a range which also contains empty cells 2
How do I select numbers for a formula from a cell including text? 1
How do I prevent data from being entered into a particular cell? 1
How do I rank, by a calculated field, in an access report? 3

Top