Hi

Having looked at the sample data you posted, then Biff's formula
transposed to deal with a row rather than a column, works absolutely
fine for me too.

=IF(A11,SUMPRODUCT(--($A$1:$IV$11),--(A1<$A$1:$IV$1))+1,"")

--
Regards

Roger Govier


"R. Choate" wrote in message
...
Hi,

The reason your formula works for you is that I have not done an
adequate job of articulating the problem, therefore you are not
testing your formula on a sample that is representative of my
worksheet.

With regard to your formula, some of the percentages are 100%, but
nevertheless, I tinkered with it and I tried it on various items
in the list and got erratic and incorrect results. The formula is
below the integer, not the percentage, so the value being tested
for <= 1 is always going to be greater than 1. The formula needs to
test the list as a whole and not include the values <=1 in the
rank. The list row includes 2 adjacent cells for each company to be
ranked, and the companies are listed accross the worksheet from
left to right. The left cell for each company is the integer (audit
fee) and the right cell is the percentage (an unrelated value
for ranking purposes). The formula to yield the rank is directly below
the integer for each company. I need for the values <=1 to be
ignored, but I don't want that to cause the integer to the left of it
to be ignored along with it. Every integer has a percentage
associated with it in the cell next to it. I hope this helps to
explain better.

Thanks to anybody who attempts to solve this delimma. It seems simple
but is harder than it looks (or I would not be writing).!
--
RMC,CPA


"Biff" wrote in message
...
I don't know what to tell ya!

It works for me either vertically or horizonatally.

Cells formatted as Percentage are <1 unless the percentage is =100%.

Can you post some examples and point out which values you want ranked
and
which you want excluded?

Biff

"R. Choate" wrote in message
...
I couldn't get that to work in my spreadsheet. My list is in row 6 and
is
horizontal. The list contains elements which are both
percentages and integers. I need to rank the integers only and ignore
the
percentages. The sumproduct example gave a couple of close
answers but none were correct (I adjusted for the horizontal list and
for
using the correct element instead of "A1").
--
RMC,CPA


"Biff" wrote in message
...
Hi!

Try this:

Range to rank is A1:A20

Enter this formula in B1 and copy down to B20:

=IF(A11,SUMPRODUCT(--($A$1:$A$201),--(A1<$A$1:$A$20))+1,"")

Biff

"R. Choate" wrote in message
...
I need to use the rank function to get the position of each item in a
list
based on its corresponding Revenue. However, the range
that has the revenue numbers also contains a percentage related to
that
item. Basically, I need to use RANK based on a list that
ignores any elements which are <= 1. Any ideas??

Thx in advance !
--
RMC,CPA