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

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

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

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

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

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

Here is a snippet of my worksheet layout. I don't have the option of placing the percentages on a seperate row, which I am aware would eliminate the problem. I just have to take the cards I'm dealt and find an answer. I appreciate your assistance. Remember when looking at the graphic that the percentages represent unrelated data and have no bearing on the actual ranking of each company. The proper rank for this company should be 14, not 16. The percentages are screwing up the function. I think it probably needs to be either an array formula or a sumproduct formula. The company name has been changed to alpha.

RMC,CPA

--



"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

Another person came up with a formula that is close to working. I am writing because I neglected to mention a fact about my WS which
I didn't consider relevant. There is a blank column between each integer and associated %, then another blank column before the next
company to the right. In his solution, the blank columns made a big difference. Biff's solution still wouldn't work for me though,
even when I deleted the extra columns
--
RMC,CPA


"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

Send me your file:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"R. Choate" wrote in message
...
Another person came up with a formula that is close to working. I am
writing because I neglected to mention a fact about my WS which
I didn't consider relevant. There is a blank column between each integer
and associated %, then another blank column before the next
company to the right. In his solution, the blank columns made a big
difference. Biff's solution still wouldn't work for me though,
even when I deleted the extra columns
--
RMC,CPA


"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

I got a solution in another forum that was very simple but apparently not as simple as my brain. It has a flaw in that it will not
work correctly if 2 or more audit fees (the integers) are identical. However, I was doing the research on this for someone else who
doesn't have a clue, and who informed me that there will never be 2 identical audit fees on the worksheet. With that info, I used
the simple solution and called it a day. I appreciate your help very much. If I thought this situation was likely to come up again,
I would send you the answer they provided for me. However, most of the time, if a person needs a rank, they will be flexible enough
to move the extra garbage out of the way and let the function work as designed instead of making employees jump through
time-consuming hoops just to allow them to keep percentages on the same row as the data being ranked. In any case, I have moved on
and deleted the file from my PC (after sending it back to the person with the problem).

Thanks again !

Richard
--
RMC,CPA


"Biff" wrote in message ...
Send me your file:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"R. Choate" wrote in message
...
Another person came up with a formula that is close to working. I am
writing because I neglected to mention a fact about my WS which
I didn't consider relevant. There is a blank column between each integer
and associated %, then another blank column before the next
company to the right. In his solution, the blank columns made a big
difference. Biff's solution still wouldn't work for me though,
even when I deleted the extra columns
--
RMC,CPA


"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

they will be flexible enough to move the extra garbage
out of the way and let the function work as designed

Yep, a "good" design can save all kinds of headaches!

However, the fact is, Rank is not a very fexible function!

Biff

"R. Choate" wrote in message
...
I got a solution in another forum that was very simple but apparently not
as simple as my brain. It has a flaw in that it will not
work correctly if 2 or more audit fees (the integers) are identical.
However, I was doing the research on this for someone else who
doesn't have a clue, and who informed me that there will never be 2
identical audit fees on the worksheet. With that info, I used
the simple solution and called it a day. I appreciate your help very much.
If I thought this situation was likely to come up again,
I would send you the answer they provided for me. However, most of the
time, if a person needs a rank, they will be flexible enough
to move the extra garbage out of the way and let the function work as
designed instead of making employees jump through
time-consuming hoops just to allow them to keep percentages on the same
row as the data being ranked. In any case, I have moved on
and deleted the file from my PC (after sending it back to the person with
the problem).

Thanks again !

Richard
--
RMC,CPA


"Biff" wrote in message
...
Send me your file:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"R. Choate" wrote in message
...
Another person came up with a formula that is close to working. I am
writing because I neglected to mention a fact about my WS which
I didn't consider relevant. There is a blank column between each integer
and associated %, then another blank column before the next
company to the right. In his solution, the blank columns made a big
difference. Biff's solution still wouldn't work for me though,
even when I deleted the extra columns
--
RMC,CPA


"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