Rank with condition

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

Biff

"R. Choate" <[email protected]> wrote in message
news:%[email protected]...
>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" <[email protected]> wrote in message news:[email protected]...
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" <[email protected]> wrote in message
news:%[email protected]...
>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" <[email protected]> wrote in message
news:OMPSWI%[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>>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" <[email protected]> wrote in message news:%[email protected]...
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" <[email protected]> wrote in message news:O5TcEY%[email protected]...
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" <[email protected]> wrote in message
news:OMPSWI%[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>>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(A1>1,SUMPRODUCT(--($A$1:$IV$1>1),--(A1<$A$1:$IV$1))+1,"")

--
Regards

Roger Govier


"R. Choate" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:O5TcEY%[email protected]...
> 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" <[email protected]> wrote in message
> news:OMPSWI%[email protected]...
>>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" <[email protected]> wrote in message
>> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>>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
>>>
>>>
>>>
>>
>>
>>
>
>
>

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


"Roger Govier" <[email protected]> wrote in message news:[email protected]...
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,"")

--
Regards

Roger Govier


"R. Choate" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:O5TcEY%[email protected]...
> 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" <[email protected]> wrote in message
> news:OMPSWI%[email protected]...
>>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" <[email protected]> wrote in message
>> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>>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" <[email protected]> wrote in message news:%[email protected]...
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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> 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 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.

--
Regards

Roger Govier


"R. Choate" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>
> "Roger Govier" <[email protected]> wrote in message
> news:[email protected]...
> 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,"")
>
> --
> Regards
>
> Roger Govier
>
>
> "R. Choate" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:O5TcEY%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:OMPSWI%[email protected]...
>>>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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>>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" <[email protected]> wrote in message news:%[email protected]...
Send me your file:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"R. Choate" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
>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" <[email protected]> wrote in message
> news:%[email protected]...
> Send me your file:
>
> xl can help at comcast period net
>
> Remove "can" and change the obvious.
>
> Biff
>
> "R. Choate" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> 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
>>
>>
>>
>>
>
>
>