Rank with condition

[R. Choate]

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 !

 

[Biff]

Hi!

Try this:

Range to rank is A1:A20

Enter this formula in B1 and copy down to B20:

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

Biff

 

[R. Choate]

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


Hi!

Try this:

Range to rank is A1:A20

Enter this formula in B1 and copy down to B20:

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

Biff

 

[Biff]

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]

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

--



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 !

 

[R. Choate]

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


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

 

[Roger Govier]

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(A1>1,SUMPRODUCT(--($A$1:$IV$1>1),--(A1<$A$1:$IV$1))+1,"")

 

[R. Choate]

If I could send you my pared-down worksheet which contains the actual formulas and actual list, you could better understand why it
doesn't work. I am not doing a good enough job expressing the situation. Biff's formula, assuming Biff intends for A1 to be changed
to reflect the cell immediately above the formula, will test the integer to see if it is greater than 1. All of the integers are
greater than 1 in my rank list. Unfortunately, my rank list also includes percentages which need to be excluded from consideration
in the ranking process. It is just a pain in the butt that the percentages are on the same row with the data to be ranked. If the
percentages were removed, then a simple RANK function would do the job. In the actual worksheet, I cannot exclude any of my integers
just because there is a percentage next to it. I need to rank my integers one thru twenty-nine (that is how many companies there
are). Each company occupies 2 adjacent, horizontal cells that are integer on the left and percentage on the right. The formulas are
directly under the integer cells and therefore have a blank cell between each ranking formula (left to right). My worksheet contains
no code or viruses. If you want to see it, I will be more than happy to send it. I'm guessing your email is as shown but without the
"NOSPAM" portion?

--
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(A1>1,SUMPRODUCT(--($A$1:$IV$1>1),--(A1<$A$1:$IV$1))+1,"")

 

[R. Choate]

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


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 !

 

[Biff]

Send me your file:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

 

[Roger Govier]

Hi

Having logged on today, I can see that Biff sent you a subsequent
message offering for you to send the file to him.
No doubt you have it resolved now. If not, by all means send me the
file.

 

[R. Choate]

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


Send me your file:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

 

[Biff]

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