software

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 lt;= 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(A1gt;1,SUMPRODUCT(--($A$1:$A$20gt;1),--(A1lt;$A$1:$A$20)) 1,quot;quot;)

Biff

quot;R. Choatequot; gt; wrote in message
...
gt;I need to use the rank function to get the position of each item in a list
gt;based on its corresponding Revenue. However, the range
gt; that has the revenue numbers also contains a percentage related to that
gt; item. Basically, I need to use RANK based on a list that
gt; ignores any elements which are lt;= 1. Any ideas??
gt;
gt; Thx in advance !
gt; --
gt; RMC,CPA
gt;
gt;
gt;
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 quot;A1quot;).
--
RMC,CPAquot;Biffquot; gt; wrote in message ...
Hi!

Try this:

Range to rank is A1:A20

Enter this formula in B1 and copy down to B20:

=IF(A1gt;1,SUMPRODUCT(--($A$1:$A$20gt;1),--(A1lt;$A$1:$A$20)) 1,quot;quot;)

Biff

quot;R. Choatequot; gt; wrote in message
...
gt;I need to use the rank function to get the position of each item in a list
gt;based on its corresponding Revenue. However, the range
gt; that has the revenue numbers also contains a percentage related to that
gt; item. Basically, I need to use RANK based on a list that
gt; ignores any elements which are lt;= 1. Any ideas??
gt;
gt; Thx in advance !
gt; --
gt; RMC,CPA
gt;
gt;
gt;

I don't know what to tell ya!

It works for me either vertically or horizonatally.

Cells formatted as Percentage are lt;1 unless the percentage is gt;=100%.

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

Biff

quot;R. Choatequot; gt; wrote in message
...
gt;I couldn't get that to work in my spreadsheet. My list is in row 6 and is
gt;horizontal. The list contains elements which are both
gt; percentages and integers. I need to rank the integers only and ignore the
gt; percentages. The sumproduct example gave a couple of close
gt; answers but none were correct (I adjusted for the horizontal list and for
gt; using the correct element instead of quot;A1quot;).
gt; --
gt; RMC,CPA
gt;
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt; Hi!
gt;
gt; Try this:
gt;
gt; Range to rank is A1:A20
gt;
gt; Enter this formula in B1 and copy down to B20:
gt;
gt; =IF(A1gt;1,SUMPRODUCT(--($A$1:$A$20gt;1),--(A1lt;$A$1:$A$20)) 1,quot;quot;)
gt;
gt; Biff
gt;
gt; quot;R. Choatequot; gt; wrote in message
gt; ...
gt;gt;I need to use the rank function to get the position of each item in a list
gt;gt;based on its corresponding Revenue. However, the range
gt;gt; that has the revenue numbers also contains a percentage related to that
gt;gt; item. Basically, I need to use RANK based on a list that
gt;gt; ignores any elements which are lt;= 1. Any ideas??
gt;gt;
gt;gt; Thx in advance !
gt;gt; --
gt;gt; RMC,CPA
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
gt;
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

--
quot;R. Choatequot; gt; 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 lt;= 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 lt;= 1 is always going to be greater than 1. The formula needs to test the list as a whole and not include the values lt;=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 lt;=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,CPAquot;Biffquot; gt; wrote in message ...
I don't know what to tell ya!

It works for me either vertically or horizonatally.

Cells formatted as Percentage are lt;1 unless the percentage is gt;=100%.

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

Biff

quot;R. Choatequot; gt; wrote in message
...
gt;I couldn't get that to work in my spreadsheet. My list is in row 6 and is
gt;horizontal. The list contains elements which are both
gt; percentages and integers. I need to rank the integers only and ignore the
gt; percentages. The sumproduct example gave a couple of close
gt; answers but none were correct (I adjusted for the horizontal list and for
gt; using the correct element instead of quot;A1quot;).
gt; --
gt; RMC,CPA
gt;
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt; Hi!
gt;
gt; Try this:
gt;
gt; Range to rank is A1:A20
gt;
gt; Enter this formula in B1 and copy down to B20:
gt;
gt; =IF(A1gt;1,SUMPRODUCT(--($A$1:$A$20gt;1),--(A1lt;$A$1:$A$20)) 1,quot;quot;)
gt;
gt; Biff
gt;
gt; quot;R. Choatequot; gt; wrote in message
gt; ...
gt;gt;I need to use the rank function to get the position of each item in a list
gt;gt;based on its corresponding Revenue. However, the range
gt;gt; that has the revenue numbers also contains a percentage related to that
gt;gt; item. Basically, I need to use RANK based on a list that
gt;gt; ignores any elements which are lt;= 1. Any ideas??
gt;gt;
gt;gt; Thx in advance !
gt;gt; --
gt;gt; RMC,CPA
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
gt;

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(A1gt;1,SUMPRODUCT(--($A$1:$IV$1gt;1),--(A1lt;$A$1:$IV$1)) 1,quot;quot;)

--
Regards

Roger Govierquot;R. Choatequot; gt; wrote in message
...
gt; Hi,
gt;
gt; The reason your formula works for you is that I have not done an
gt; adequate job of articulating the problem, therefore you are not
gt; testing your formula on a sample that is representative of my
gt; worksheet.
gt;
gt; With regard to your formula, some of the percentages are 100%, but
gt; nevertheless, I tinkered with it and I tried it on various items
gt; in the list and got erratic and incorrect results. The formula is
gt; below the integer, not the percentage, so the value being tested
gt; for lt;= 1 is always going to be greater than 1. The formula needs to
gt; test the list as a whole and not include the values lt;=1 in the
gt; rank. The list row includes 2 adjacent cells for each company to be
gt; ranked, and the companies are listed accross the worksheet from
gt; left to right. The left cell for each company is the integer (audit
gt; fee) and the right cell is the percentage (an unrelated value
gt; for ranking purposes). The formula to yield the rank is directly below
gt; the integer for each company. I need for the values lt;=1 to be
gt; ignored, but I don't want that to cause the integer to the left of it
gt; to be ignored along with it. Every integer has a percentage
gt; associated with it in the cell next to it. I hope this helps to
gt; explain better.
gt;
gt; Thanks to anybody who attempts to solve this delimma. It seems simple
gt; but is harder than it looks (or I would not be writing).!
gt; --
gt; RMC,CPA
gt;
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt; I don't know what to tell ya!
gt;
gt; It works for me either vertically or horizonatally.
gt;
gt; Cells formatted as Percentage are lt;1 unless the percentage is gt;=100%.
gt;
gt; Can you post some examples and point out which values you want ranked
gt; and
gt; which you want excluded?
gt;
gt; Biff
gt;
gt; quot;R. Choatequot; gt; wrote in message
gt; ...
gt;gt;I couldn't get that to work in my spreadsheet. My list is in row 6 and
gt;gt;is
gt;gt;horizontal. The list contains elements which are both
gt;gt; percentages and integers. I need to rank the integers only and ignore
gt;gt; the
gt;gt; percentages. The sumproduct example gave a couple of close
gt;gt; answers but none were correct (I adjusted for the horizontal list and
gt;gt; for
gt;gt; using the correct element instead of quot;A1quot;).
gt;gt; --
gt;gt; RMC,CPA
gt;gt;
gt;gt;
gt;gt; quot;Biffquot; gt; wrote in message
gt;gt; ...
gt;gt; Hi!
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; Range to rank is A1:A20
gt;gt;
gt;gt; Enter this formula in B1 and copy down to B20:
gt;gt;
gt;gt; =IF(A1gt;1,SUMPRODUCT(--($A$1:$A$20gt;1),--(A1lt;$A$1:$A$20)) 1,quot;quot;)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;R. Choatequot; gt; wrote in message
gt;gt; ...
gt;gt;gt;I need to use the rank function to get the position of each item in a
gt;gt;gt;list
gt;gt;gt;based on its corresponding Revenue. However, the range
gt;gt;gt; that has the revenue numbers also contains a percentage related to
gt;gt;gt; that
gt;gt;gt; item. Basically, I need to use RANK based on a list that
gt;gt;gt; ignores any elements which are lt;= 1. Any ideas??
gt;gt;gt;
gt;gt;gt; Thx in advance !
gt;gt;gt; --
gt;gt;gt; RMC,CPA
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
gt;
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
quot;NOSPAMquot; portion?

--
RMC,CPAquot;Roger Govierquot; gt; wrote in message ...
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(A1gt;1,SUMPRODUCT(--($A$1:$IV$1gt;1),--(A1lt;$A$1:$IV$1)) 1,quot;quot;)

--
Regards

Roger Govierquot;R. Choatequot; gt; wrote in message
...
gt; Hi,
gt;
gt; The reason your formula works for you is that I have not done an
gt; adequate job of articulating the problem, therefore you are not
gt; testing your formula on a sample that is representative of my
gt; worksheet.
gt;
gt; With regard to your formula, some of the percentages are 100%, but
gt; nevertheless, I tinkered with it and I tried it on various items
gt; in the list and got erratic and incorrect results. The formula is
gt; below the integer, not the percentage, so the value being tested
gt; for lt;= 1 is always going to be greater than 1. The formula needs to
gt; test the list as a whole and not include the values lt;=1 in the
gt; rank. The list row includes 2 adjacent cells for each company to be
gt; ranked, and the companies are listed accross the worksheet from
gt; left to right. The left cell for each company is the integer (audit
gt; fee) and the right cell is the percentage (an unrelated value
gt; for ranking purposes). The formula to yield the rank is directly below
gt; the integer for each company. I need for the values lt;=1 to be
gt; ignored, but I don't want that to cause the integer to the left of it
gt; to be ignored along with it. Every integer has a percentage
gt; associated with it in the cell next to it. I hope this helps to
gt; explain better.
gt;
gt; Thanks to anybody who attempts to solve this delimma. It seems simple
gt; but is harder than it looks (or I would not be writing).!
gt; --
gt; RMC,CPA
gt;
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt; I don't know what to tell ya!
gt;
gt; It works for me either vertically or horizonatally.
gt;
gt; Cells formatted as Percentage are lt;1 unless the percentage is gt;=100%.
gt;
gt; Can you post some examples and point out which values you want ranked
gt; and
gt; which you want excluded?
gt;
gt; Biff
gt;
gt; quot;R. Choatequot; gt; wrote in message
gt; ...
gt;gt;I couldn't get that to work in my spreadsheet. My list is in row 6 and
gt;gt;is
gt;gt;horizontal. The list contains elements which are both
gt;gt; percentages and integers. I need to rank the integers only and ignore
gt;gt; the
gt;gt; percentages. The sumproduct example gave a couple of close
gt;gt; answers but none were correct (I adjusted for the horizontal list and
gt;gt; for
gt;gt; using the correct element instead of quot;A1quot;).
gt;gt; --
gt;gt; RMC,CPA
gt;gt;
gt;gt;
gt;gt; quot;Biffquot; gt; wrote in message
gt;gt; ...
gt;gt; Hi!
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; Range to rank is A1:A20
gt;gt;
gt;gt; Enter this formula in B1 and copy down to B20:
gt;gt;
gt;gt; =IF(A1gt;1,SUMPRODUCT(--($A$1:$A$20gt;1),--(A1lt;$A$1:$A$20)) 1,quot;quot;)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;R. Choatequot; gt; wrote in message
gt;gt; ...
gt;gt;gt;I need to use the rank function to get the position of each item in a
gt;gt;gt;list
gt;gt;gt;based on its corresponding Revenue. However, the range
gt;gt;gt; that has the revenue numbers also contains a percentage related to
gt;gt;gt; that
gt;gt;gt; item. Basically, I need to use RANK based on a list that
gt;gt;gt; ignores any elements which are lt;= 1. Any ideas??
gt;gt;gt;
gt;gt;gt; Thx in advance !
gt;gt;gt; --
gt;gt;gt; RMC,CPA
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
gt;

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,CPAquot;R. Choatequot; gt; 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 lt;= 1. Any ideas??

Thx in advance !
--
RMC,CPASend me your file:

xl can help at comcast period net

Remove quot;canquot; and change the obvious.

Biff

quot;R. Choatequot; gt; wrote in message
...
gt; Another person came up with a formula that is close to working. I am
gt; writing because I neglected to mention a fact about my WS which
gt; I didn't consider relevant. There is a blank column between each integer
gt; and associated %, then another blank column before the next
gt; company to the right. In his solution, the blank columns made a big
gt; difference. Biff's solution still wouldn't work for me though,
gt; even when I deleted the extra columns
gt; --
gt; RMC,CPA
gt;
gt;
gt; quot;R. Choatequot; gt; wrote in message
gt; ...
gt; I need to use the rank function to get the position of each item in a list
gt; based on its corresponding Revenue. However, the range
gt; that has the revenue numbers also contains a percentage related to that
gt; item. Basically, I need to use RANK based on a list that
gt; ignores any elements which are lt;= 1. Any ideas??
gt;
gt; Thx in advance !
gt; --
gt; RMC,CPA
gt;
gt;
gt;
gt;