### Tags: based, condition, corresponding, excel, function, item, microsoft, msdn, position, range, rank, revenue, software

# Rank with condition

On Microsoft » Microsoft Excel

37,237 words with 12 Comments; publish: Thu, 22 May 2008 18:40:00 GMT; (306234.38, « »)

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

*http://excel.questionfor.info/q_microsoft-excel_375917.html*

All Comments

Leave a comment...

- 12 Comments
- 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

>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

>

>

#1; Thu, 22 May 2008 18:41:00 GMT

- Hi!
- 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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message news:uq$Ajw9ZGHA.5088.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

>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

>

>

#2; Thu, 22 May 2008 18:42:00 GMT

- 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
- 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

news:OMPSWI%23ZGHA.5108.excel.questionfor.info.TK2MSFTNGP05.phx.gbl...

>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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

> news:uq$Ajw9ZGHA.5088.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

> news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

>>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

>>

>

>

#3; Thu, 22 May 2008 18:43:00 GMT

- I don't know what to tell ya!
- This is a multi-part message in MIME format.
--=_NextPart_000_0034_01C66857.6E5B2BB0

Content-Type: multipart/alternative;

boundary="--=_NextPart_001_0035_01C66857.6E5B2BB0"

--=_NextPart_001_0035_01C66857.6E5B2BB0

Content-Type: text/plain;

charset="Windows-1252"

Content-Transfer-Encoding: quoted-printable

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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message =news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

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 <=3D 1. Any ideas'

Thx in advance !

-- RMC,CPA

--=_NextPart_001_0035_01C66857.6E5B2BB0

Content-Type: text/html;

charset="Windows-1252"

Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

&

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 news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...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 <=3D 1. Any =ideas'Thx in advance !-- RMC,CPA --=_NextPart_001_0035_01C66857.6E5B2BB0--

--=_NextPart_000_0034_01C66857.6E5B2BB0

Content-Type: image/gif;

name="Data sample.gif"

Content-Transfer-Encoding: base64

Content-ID: <002f01c66881$573133b0$8201a8c0.excel.questionfor.info.Oahu>

R0lGODlhhgC5APf/AAAAAE1NTWhoaHx8fIyMjJqamqenp7Kysr29vcDAwMfHx9DQ0NnZ2eHh4enp

6fDw8P///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEBAP8ALAAAAACGALkA

QAj/ACEIHEiwoMGDCBMqXMiwocOHDBNAnEixosWLGCVi3Mixo8eIH0OKHFlRI8mNAQA8SJhy5UmR

Jl/KnBkyJs2bOCnazMmzJ8KdPnUG3QiU4wMDAJIugEmSAAAGEAokLfCx6FCHVq+C1FqSa1evE7OC

PSh2bMGyZgdKTMC2rdu3cOPKnUu3rt27ePPe7QigYV8IfwUGFjzw72AIaCsePlyYMGHGaj8mXWyQ

8WSEhxNbvFx4MufGnTOnxTq6oWazp8emBrvaa2uur7XGxkzZMdHSW0MOrp0R98LZvoMLH/4QOHHV

x5MrNH5SKgKCDgg0EOgcunTczF8uGJA0gAGXA7d3///uO7vyoebP+0yvnid7CCkZPAUggCnHpIL/

LgBA1X7IBp4BoIB/HT0QQAADSdUAgP1xJFGAEEYo4YQUVmjhhRhmqCGE79HU4UwfyhTiSyOeVCJJ

J46UIoEX8XbQbgVZdtZJLtpWGWCgCbRiTe3tWFWPQKrno0dDdlSkg0HqRhB+Nl505G1CJtneeU9O

2ZuVVGKZpZbJVcllWMEhFWAADgwkpmdkYucbk7Qp56VCAAooUAMCAEDAnEkNCAGddqr5JXFv/mma

oMMFSuhyhwZnaKJkMeqnSAikJMBzLF4kFX8EAThApRxxJ5WJHSnIIAQIAEAppyj1pSpkV4ZU50pM

soAJJUmsIhnlrVvm6qaUXfJ63KIQAVucr4ASW6ixwglLGq4bBVYrYLxZJhqNOeJom7NNRnYftgpJ

y+q0NMoa45KxJsRcjeNWa+2SM9KabrbsrjtYic9apOygzPaa76/IKtpvef8+uuu+xUap18EIJ6zw

wgwHXJp5hsXLI8HNSgwvmP8UY+QtkQ6Pdm9ujqIWsscjp/UxoycnmvKhKxPasqAv/xnzlzNzWbOW

Ky/gFADegSeQzuP5bLJvcVLaAHeb7pmU0UgLnNarCUHNL27ixljv0LhVN1AD10VlaqZdk+zbmZOl

KRDZ3ZX5cMkis82a22/DDZvcc9N91c1Y4m2l3lPy3bHdKAKOnuBBrcXw4YgnrrhdL11t798PlerZ

qRxzJCqmAjk13cS6qQrqtvkJdKDQRo4Up52bc77RpVQ9QN/ntCKFoOoeAXjn5Q3O+pFUCgDgwOu0

c1TqAaFDW7lIC9QpgJ7BY4TUUglO9WPG/lIP8IbYZ6/99txrCDly1js9ta7Z+pI//sDmF5z+seGv

3b7Y6yf7fdzxV18/wO9jfb/46qO/7dUbe1G7wuWt3cgKMud617hgZDHPEeRDtYkgaFiVwM8kJFoC

fGDj1BUvbNUqgRa7kcQgA66RcEuEIrQgYviiQBSuK1slVFIIaxUxG2XnhJh5FwkHaMJqPeuE85qJ

4zC2P/dlaXFITKISEzY/1zSxbkWEn//KN8XzUfGKVsxi/7C4RS2yL4r6q2IXx/hFMZaRi2f0ovzy

95AA2gqMfnkhuoTCRofASIVOemK3ZvhGM2qMj7pD4x9HWBK6OtLPj/bb1RIXychGtgUCAQEAOw==--=_NextPart_000_0034_01C66857.6E5B2BB0--

#4; Thu, 22 May 2008 18:44:00 GMT

- This is a multi-part message in MIME format.
- 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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message news:O5TcEY%23ZGHA.3304.excel.questionfor.info.TK2MSFTNGP04.phx.gbl...

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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

news:OMPSWI%23ZGHA.5108.excel.questionfor.info.TK2MSFTNGP05.phx.gbl...

>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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

> news:uq$Ajw9ZGHA.5088.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

> news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

>>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

>>

>

>

#5; Thu, 22 May 2008 18:45:00 GMT

- Hi,
- 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

news:%23OrbiUIaGHA.3328.excel.questionfor.info.TK2MSFTNGP02.phx.gbl...

> 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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

> news:O5TcEY%23ZGHA.3304.excel.questionfor.info.TK2MSFTNGP04.phx.gbl...

> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

> news:OMPSWI%23ZGHA.5108.excel.questionfor.info.TK2MSFTNGP05.phx.gbl...

>>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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

>> news:uq$Ajw9ZGHA.5088.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

>> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

>> news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

>>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

>>

>>

>>

>

>

#6; Thu, 22 May 2008 18:46:00 GMT

- Hi
- 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" <roger.excel.questionfor.info.technologyNOSPAM4u.co.uk> wrote in message news:eqyGTmIaGHA.3832.excel.questionfor.info.TK2MSFTNGP04.phx.gbl...

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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

news:%23OrbiUIaGHA.3328.excel.questionfor.info.TK2MSFTNGP02.phx.gbl...

> 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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

> news:O5TcEY%23ZGHA.3304.excel.questionfor.info.TK2MSFTNGP04.phx.gbl...

> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

> news:OMPSWI%23ZGHA.5108.excel.questionfor.info.TK2MSFTNGP05.phx.gbl...

>>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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

>> news:uq$Ajw9ZGHA.5088.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

>> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

>> news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

>>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

>>

>>

>>

>

>

#7; Thu, 22 May 2008 18:47:00 GMT

- If I could send you my pared-down worksheet which contains the actual formulas and actual list, you could better understand why it
- 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

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

#8; Thu, 22 May 2008 18:48:00 GMT

- 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
- Send me your file:
xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"R. Choate" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

news:%23wR7nxJaGHA.4780.excel.questionfor.info.TK2MSFTNGP02.phx.gbl...

> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

> news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

> 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

>

>

#9; Thu, 22 May 2008 18:49:00 GMT

- Send me your file:
- 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

news:%23zrDVxIaGHA.4036.excel.questionfor.info.TK2MSFTNGP04.phx.gbl...

> 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" <roger.excel.questionfor.info.technologyNOSPAM4u.co.uk> wrote in message

> news:eqyGTmIaGHA.3832.excel.questionfor.info.TK2MSFTNGP04.phx.gbl...

> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

> news:%23OrbiUIaGHA.3328.excel.questionfor.info.TK2MSFTNGP02.phx.gbl...

>> 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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

>> news:O5TcEY%23ZGHA.3304.excel.questionfor.info.TK2MSFTNGP04.phx.gbl...

>> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

>> news:OMPSWI%23ZGHA.5108.excel.questionfor.info.TK2MSFTNGP05.phx.gbl...

>>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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

>> news:uq$Ajw9ZGHA.5088.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

>> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

>> news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

>>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

>>

>>

>>

>>

>

>

#10; Thu, 22 May 2008 18:50:00 GMT

- Hi
- 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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message news:%23wbGiTLaGHA.4416.excel.questionfor.info.TK2MSFTNGP04.phx.gbl...

Send me your file:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"R. Choate" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

news:%23wR7nxJaGHA.4780.excel.questionfor.info.TK2MSFTNGP02.phx.gbl...

> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

> news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

> 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

>

>

#11; Thu, 22 May 2008 18:51:00 GMT

- 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
- >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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

news:%23Q$2sqYaGHA.3880.excel.questionfor.info.TK2MSFTNGP04.phx.gbl...

>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" <biffinpitt.excel.questionfor.info.comcast.net> wrote in message

> news:%23wbGiTLaGHA.4416.excel.questionfor.info.TK2MSFTNGP04.phx.gbl...

> Send me your file:

> xl can help at comcast period net

> Remove "can" and change the obvious.

> Biff

> "R. Choate" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

> news:%23wR7nxJaGHA.4780.excel.questionfor.info.TK2MSFTNGP02.phx.gbl...

>> 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" <rchoatecpa.excel.questionfor.info.NoSpam.com> wrote in message

>> news:%23XL8Ak9ZGHA.4564.excel.questionfor.info.TK2MSFTNGP03.phx.gbl...

>> 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

>>

>>

>

>

#12; Thu, 22 May 2008 18:52:00 GMT

- >they will be flexible enough to move the extra garbage