Ranking range of cell with value only

I am ranking set of numbers (# of days worked) in a 10 cell column but
the numbers of persons I rank differs from day to day. 
If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
I have to enter Zero because that is the number of days worked.
How can I enter the value of zero that does not activate the other
blank cells?

Here is my formula:
=IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B$14,1))

Thanks.


-- 
cardingtr
------------------------------------------------------------------------
cardingtr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27027
View this thread: http://www.excelforum.com/showthread.php?threadid=468118

0
9/16/2005 4:36:57 AM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
771 Views

Similar Articles

[PageSpeed] 47

Try:

=IF(AND(ISNUMBER(B5),B5<>0),RANK(B5,$B$5:$B$14,1),"")

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cardingtr" <cardingtr.1vfr6b_1126847106.1449@excelforum-nospam.com> wrote
in message news:cardingtr.1vfr6b_1126847106.1449@excelforum-nospam.com...
>
> I am ranking set of numbers (# of days worked) in a 10 cell column but
> the numbers of persons I rank differs from day to day.
> If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
> I have to enter Zero because that is the number of days worked.
> How can I enter the value of zero that does not activate the other
> blank cells?
>
> Here is my formula:
> =IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B$14,1))
>
> Thanks.
>
>
> --
> cardingtr
> ------------------------------------------------------------------------
> cardingtr's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=27027
> View this thread: http://www.excelforum.com/showthread.php?threadid=468118
>


0
demechanik (4694)
9/16/2005 5:45:54 AM
That still doesn't exclude 0's.

Rank is not a very flexible function!

Maybe use a helper column. Enter this in C5 and copy down to C14:

=IF(B5=0,"",B5)

Then:

=IF(C5="","",RANK(C5,C$5:C$14,1))

Copy down.

OR, maybe something *CRAZY*

If the numbers entered *AREN'T* used in any other calculations (other than 
the RANK) instead of entering a zero, enter an uppercase letter O.

Biff

"Max" <demechanik@yahoo.com> wrote in message 
news:%23jVnGIouFHA.2076@TK2MSFTNGP14.phx.gbl...
> Try:
>
> =IF(AND(ISNUMBER(B5),B5<>0),RANK(B5,$B$5:$B$14,1),"")
>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "cardingtr" <cardingtr.1vfr6b_1126847106.1449@excelforum-nospam.com> wrote
> in message news:cardingtr.1vfr6b_1126847106.1449@excelforum-nospam.com...
>>
>> I am ranking set of numbers (# of days worked) in a 10 cell column but
>> the numbers of persons I rank differs from day to day.
>> If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
>> I have to enter Zero because that is the number of days worked.
>> How can I enter the value of zero that does not activate the other
>> blank cells?
>>
>> Here is my formula:
>> =IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B$14,1))
>>
>> Thanks.
>>
>>
>> --
>> cardingtr
>> ------------------------------------------------------------------------
>> cardingtr's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=27027
>> View this thread: 
>> http://www.excelforum.com/showthread.php?threadid=468118
>>
>
> 


0
biffinpitt (3171)
9/16/2005 6:08:46 AM
Hit send before I was done:

> OR, maybe something *CRAZY*
>
> If the numbers entered *AREN'T* used in any other calculations (other than 
> the RANK) instead of entering a zero, enter an uppercase letter O.

Then use this formula:

=IF(COUNT(B5),RANK(B5,B$5:B$14,1),"")

Biff

"Biff" <biffinpitt@comcast.net> wrote in message 
news:OXMNbUouFHA.3756@tk2msftngp13.phx.gbl...
> That still doesn't exclude 0's.
>
> Rank is not a very flexible function!
>
> Maybe use a helper column. Enter this in C5 and copy down to C14:
>
> =IF(B5=0,"",B5)
>
> Then:
>
> =IF(C5="","",RANK(C5,C$5:C$14,1))
>
> Copy down.
>
> OR, maybe something *CRAZY*
>
> If the numbers entered *AREN'T* used in any other calculations (other than 
> the RANK) instead of entering a zero, enter an uppercase letter O.
>
> Biff
>
> "Max" <demechanik@yahoo.com> wrote in message 
> news:%23jVnGIouFHA.2076@TK2MSFTNGP14.phx.gbl...
>> Try:
>>
>> =IF(AND(ISNUMBER(B5),B5<>0),RANK(B5,$B$5:$B$14,1),"")
>>
>> --
>> Rgds
>> Max
>> xl 97
>> ---
>> Singapore, GMT+8
>> xdemechanik
>> http://savefile.com/projects/236895
>> --
>> "cardingtr" <cardingtr.1vfr6b_1126847106.1449@excelforum-nospam.com> 
>> wrote
>> in message news:cardingtr.1vfr6b_1126847106.1449@excelforum-nospam.com...
>>>
>>> I am ranking set of numbers (# of days worked) in a 10 cell column but
>>> the numbers of persons I rank differs from day to day.
>>> If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
>>> I have to enter Zero because that is the number of days worked.
>>> How can I enter the value of zero that does not activate the other
>>> blank cells?
>>>
>>> Here is my formula:
>>> =IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B$14,1))
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> cardingtr
>>> ------------------------------------------------------------------------
>>> cardingtr's Profile:
>> http://www.excelforum.com/member.php?action=getinfo&userid=27027
>>> View this thread: 
>>> http://www.excelforum.com/showthread.php?threadid=468118
>>>
>>
>>
>
> 


0
biffinpitt (3171)
9/16/2005 6:13:21 AM
That didn't work. It did not rank the cell. If zero is entered is should
rank the cell as either 1 or the highest number. But instead it leaves
it blank. 


Max Wrote: 
> Try:
> 
> =IF(AND(ISNUMBER(B5),B5<>0),RANK(B5,$B$5:$B$14,1),"")
> 
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "cardingtr" <cardingtr.1vfr6b_1126847106.1449@excelforum-nospam.com>
> wrote
> in message
> news:cardingtr.1vfr6b_1126847106.1449@excelforum-nospam.com...
> >
> > I am ranking set of numbers (# of days worked) in a 10 cell column
> but
> > the numbers of persons I rank differs from day to day.
> > If I enter "0"(zero) on one of the cell, it ranks the whole ten
> cells.
> > I have to enter Zero because that is the number of days worked.
> > How can I enter the value of zero that does not activate the other
> > blank cells?
> >
> > Here is my formula:
> > =IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B$14,1))
> >
> > Thanks.
> >
> >
> > --
> > cardingtr
> >
> ------------------------------------------------------------------------
> > cardingtr's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=27027
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=468118
> > :(  :(


-- 
cardingtr
------------------------------------------------------------------------
cardingtr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27027
View this thread: http://www.excelforum.com/showthread.php?threadid=468118

0
9/16/2005 12:17:45 PM
Try...

=IF(B5<>"",SUMPRODUCT(--($B$5:$B$14<>""),--(B5>$B$5:$B$14))+1,"")

Hope this helps!

In article <cardingtr.1vfr6b_1126847106.1449@excelforum-nospam.com>,
 cardingtr <cardingtr.1vfr6b_1126847106.1449@excelforum-nospam.com> 
 wrote:

> I am ranking set of numbers (# of days worked) in a 10 cell column but
> the numbers of persons I rank differs from day to day. 
> If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
> I have to enter Zero because that is the number of days worked.
> How can I enter the value of zero that does not activate the other
> blank cells?
> 
> Here is my formula:
> =IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B$14,1))
> 
> Thanks.
0
domenic22 (716)
9/16/2005 1:56:20 PM
Sorry, I probably mis-interp'ed what you wanted.
See Biff's and Domenic's suggestions ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"cardingtr" <cardingtr.1vgded_1126875931.1545@excelforum-nospam.com> wrote
in message news:cardingtr.1vgded_1126875931.1545@excelforum-nospam.com...
>
> That didn't work. It did not rank the cell. If zero is entered is should
> rank the cell as either 1 or the highest number. But instead it leaves
> it blank.


0
demechanik (4694)
9/16/2005 5:22:46 PM
Don't pay any attention to my suggestion, then.

I thought the OP wanted to exclude ranking 0's.

Biff

"Max" <demechanik@yahoo.com> wrote in message 
news:eZHXFNuuFHA.3896@TK2MSFTNGP15.phx.gbl...
> Sorry, I probably mis-interp'ed what you wanted.
> See Biff's and Domenic's suggestions ..
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "cardingtr" <cardingtr.1vgded_1126875931.1545@excelforum-nospam.com> wrote
> in message news:cardingtr.1vgded_1126875931.1545@excelforum-nospam.com...
>>
>> That didn't work. It did not rank the cell. If zero is entered is should
>> rank the cell as either 1 or the highest number. But instead it leaves
>> it blank.
>
> 


0
biffinpitt (3171)
9/16/2005 6:01:28 PM
=IF(N(B5),RANK(B5,$B$5:$B$14),"")

cardingtr wrote:
> I am ranking set of numbers (# of days worked) in a 10 cell column but
> the numbers of persons I rank differs from day to day. 
> If I enter "0"(zero) on one of the cell, it ranks the whole ten cells.
> I have to enter Zero because that is the number of days worked.
> How can I enter the value of zero that does not activate the other
> blank cells?
> 
> Here is my formula:
> =IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B$14,1))
> 
> Thanks.
> 
> 
0
akyurek (248)
9/18/2005 4:27:44 PM
"Biff" wrote:
> I thought the OP wanted to exclude ranking 0's.

That's what I thought. But the converse is true,
as confirmed by the OP's response.
The OP wants to rank zeros, but not blanks

> Don't pay any attention to my suggestion, then.

And why not <g> ? Thought both your suggestions
and Domenic's worked for what the OP confirmed was wanted

And Aladin's suggestion
(with the rank order corrected to give an ascending sort):
=IF(N(B5),RANK(B5,$B$5:$B$14,1),"")

seems to yield the same returns as mine ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


0
demechanik (4694)
9/19/2005 12:54:39 AM
Actually, the following would suffice...

=IF(B5<>"",RANK(B5,$B$5:$B$14,1),"")

In article <domenic22-3EC155.09562016092005@msnews.microsoft.com>,
 Domenic <domenic22@sympatico.ca> wrote:

> Try...
> 
> =IF(B5<>"",SUMPRODUCT(--($B$5:$B$14<>""),--(B5>$B$5:$B$14))+1,"")
> 
> Hope this helps!
0
domenic22 (716)
9/19/2005 11:46:16 AM
Reply:

Similar Artilces:

Naming range
Does anyone know how to name a range, so that I can export from excel into outlook? Several ways, easiest being to highlight it and put a name in the name box (to the left of the formula box) -- Don Guillett SalesAid Software donaldb@281.com "Kimmie" <anonymous@discussions.microsoft.com> wrote in message news:2953201c465ce$2542ce20$a601280a@phx.gbl... > Does anyone know how to name a range, so that I can > export from excel into outlook? try range(mynamedrange).select -- Don Guillett SalesAid Software donaldb@281.com "BerHav" <BerHav@discussions.micr...

#Error and Zero Value Help
I have a query with the following equation AVG Hold Time: [Total Hold Time]/[Total ACD]. Some fields show #error or a zero value (0:00:00). On a report footer I would like to average the AVG Hold Time by month or even better take the sum of Total ACD and the sum of Total Hold Time on the report and divide those. Either way I keep getting an Overflow error. Also, in the query I tried Is Not Null criteria for AVG Hold Time, but get an overflow error. For a single record (detail section) you would normally use: =IIf(Nz([Total ACD],0) =0, Null, [Total Hold Time]/[Total ACD]) In a group/repo...

Cell formats, and time difference
Hi, some simple questions which I hope someone can help with... I am using excel on a computer with "danish locale". This means that numbers are displayed default like 1234,56. How do I change this to a format using a decimal point (instead of a comma)? How do I find the difference between to times? I have cells which just have times (not dates), and I want to find the difference between two times in hours. For example cells with 08:00 and 09:30 should give a difference of 1.5 Excel help gives an example like =TEXT(B2-A2,"h"), but this gives an error... Thanks, Pet...

Pivot table and value range
Hi, I have built a simple pivot table with 2 columns : Amounts and count of amounts eg: amount/Nb of amount 100 /1 150 /5 200 /6 250 /2 300 /1 450 /5 500 /9 Would it be possible to have a range of value in the pivot table to have a result like this: amount/Nb 0-200 /12 201-400 /3 >401 /14 Thank you for your help Thank you I would add a column to the raw data that categorized the amounts. if(a2<=200,200,if(a2<=400,400,"400+")) Then drag this down the data. And use it as the field in the pivottable. Nicawette wrote: > > Hi, ...

Calendar in a cell
Hi, Is it possible to insert a calendar similar to the ones used in MS Project, or Outlook, where you click a pull down arrow and you select a date to be inserted in that cell? Any help will be appreciated, Roberto Ron de Bruin has some samples at: http://www.rondebruin.nl/calendar.htm Ron has a link (at the bottom of that URL) where you can get a free version of a control. (It's not included in excel (but if you have it, you can use it there.) (I think it's installed with Access.) Roberto wrote: > > Hi, > Is it possible to insert a calendar similar to the ones used in...

List a ranking
I'm a newbie at this, so help is appreciated. I have an Excel sheet like this: Team A .600 =rank(b1,b1:b3) Team B .750 =rank(b2,b1:b3) Team C .300 =rank(b3,b1:b3) I want to get another cell to list how far they are from the #1 rank. So, cell d1 should be equal to .150 and d3 equal to .450. The percentages will change frequently, so it needs to figure out what is #1. I know I could just do =b2-b1 and b2-b3, but only because I can see the b2 is the highest percentage. Am I making sense? Is there a way to do this? TIA. BR =MAX($B$1:$B$3)-B1 etc. -- HTH RP ...

Find Date in range and scroll down to next row
I have a sheet(1) frozen in A4. Want a code to do the following: Sheet(1) Range(k:k) If date = today then select row font.color. index = 15 scroll down to next row (next row and below visible). How do I solve? Do you want to simply loop through all the occupied cells in column K and color the entire row if the date = today? I don't understand the "next row and below visible". What is visible and what is not visible? Post back and provide more info. HTH Otto "tomjoe" <tomjoe@discussions.microsoft.com> wrote in message news:DC40BBCF-E3...

Rounding a large range & a Missing Font
Hi, After recently installing Virtual PC on my iBook, primarily for Access (for work). I was struck by how much clearer the default font is in files exported from Access to Excel:mac. The iBook screen leaves a lot to be desired and MS Sans Serif is so much clearer than Verdana or Arial. ....But..it's not in my font list. Anyone any ideas on how I can get it. If an imported file uses it can't I make it available to new workbooks? Also, anyone know a quick way of applying a rounding (to nearest 25) to a large range? For a column or two I'd just use MROUND(range,25) in the adja...

assign formatted cell value to another cell
Hello everyone! If A1 has a floating point number for a date, e.g. 39647.25 for 2008-07-18 06:00:00 and I want to have that formatted string in a new cell - does anyone know a formula to apply to another column, so it puts the *formatted* value there, instead of the original number? I want the string "2008-07-18 06:00:00" as the value of my new cell, and I can't find a way to do that with a Range formula at the moment (I want to avoid looping over each row and doing it manually in VBA, for speed reasons). Thanks in advance! Lars =TEXT(A1,"yyyy-mm-dd hh:mm:ss&q...

How to select text, e.g. simulate the text copied with ctrl-a on a webbrowser control in C#? I tried this and it always returns null for range.Text private void Form1_Load(object sende
How to select text, e.g. simulate the text copied with ctrl-a on a webbrowser control in C#? I tried this and it always returns null for range.Text private void Form1_Load(object sender, EventArgs e) { webBrowser1.Navigate("http://www.google.com/"); } private void button1_Click(object sender, EventArgs e) { IHTMLDocument2 htmlDocument = webBrowser1.Document.DomDocument as IHTMLDocument2; IHTMLSelectionObject currentSelection = htmlDocument.selection; if (currentSelection != nul...

Rank within Categories
I am trying to work out a way to rank within categories. Column E has different categories. Column i has the data. Column am working on i want to give the ranking where each data point falls within its own category. i have tried to hash something out but am stuck...here is my feeble attempt =IF($E5="SE",IF($E$5:$E$40="SE",IF(I5>0,RANK(I5,$I$5:$I$40,0),"N/ A"), ...... ..... signfies a string of IFs. the Next one being IF($E5="C".... I have also tried an array.....that doesnt seem to work =IF(OR(E6={"C","SE","NE","...

Conditional formatting with cell styles
I like the cell styles and the fact that they can change if I change a workbook theme. I'd like to use these styles in conditional formats but I can only see manual settings for fonts, borders, fills etc in the conditional formatting options. Is there a way to specify that a conditional format should use a named cell style perhaps? Thanks for any clues you can give. Kevin ...

Formula Changes when data entered in referenced range
I have 5 formulas in a worksheet that refer to a range. When data is entered in the range, the formulas adjust to exclude the cells with data. Range B5:B211 When data is entered in B5, range in the formulas becomes B6:B212. The workbook has nearly 200 worksheets with the same formulas. I'm working in Excel 2003. Does anybody have any idea what's going on????? Might help if you include the formulae that you are using. Regards Trevor "mac849" <mac849@discussions.microsoft.com> wrote in message news:33BDD2CD-C071-4DAE-A821-4967E94C16A3@microsoft.com... >...

Matching cells by content then cell fill with color
Thanks to JEM, I am using this routine to color three consecutive cell a specific color, in this case red: Public Sub ThreeCellsRed() ActiveCell.Resize(1,3).Interior.ColorIndex = 3 End Sub What I need now is a way for the routine to continue to find all th similar cells, let's say for sake of disc they are people's names, s when I execute the above on my name, mrh, I want it to continue in th worksheet and find all exact matches and color those same cells red. Another thought, say my name (MRH) is in "A1" and it is also in "D1". But in "D1" I use "...

Automaticaly select range
Dear All, I have the following macro attached to a button. With regard to the select aspect, the range will change from time to time. So I either need to put the range ie A1:R54, in a predetermined cell, say A1 all the time. Otherwise I need some code as part of the macro, that will give me a box to enter the range, once the button is pressed. Range("B594:K601").Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("A:A").EntireColumn.AutoFit Columns(&q...

external link values
Hi, is there a way to stop excel saving external link values when saving a document? i know i can copy then paste as values but the doc needs naming and saving at the outset. thanks Rick I don't know if this will do what you want, but you can use Tools; Options and uncheck the save external link values on the Calculation tab. >-----Original Message----- >Hi, >is there a way to stop excel saving external link values when saving a >document? > >i know i can copy then paste as values but the doc needs naming and saving >at the outset. > >thanks >Rick >...

trim spaces in a cell
How do i trim empty spaces in a cell containing an invoice number but the length of the invoice number in column B is of dynamic length? For example, Column B 123 4567-------->1234567 987 3------------>9873 5 55------------->555 Thanks. Ringo Tan Ringo If there is only ever one space then the worksheet function below should work =REPLACE(A1,FIND(" ",A1,1),1,"") -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "ringo tan" <ringotan@discussions.microsoft.com> wrote in message news:F8741...

grouping cells to sort by only one column
Hi, New to this so please bear with me. I have a spread sheet where I am listing names in column A and othe info relating to that name in columns B and C in the two rows below th name. I then start a different name in the next row down with the info in t two rows below that, and so forth. I want to be able to add more names in the future and be able to sor all of this by column A only. The problem I am having is the info i the other columns moves. I need it to somehow attatch and stay put wit it's corisponding name without moving. In other words how do I make everything from A1 to ...

Cell entry
Want to input data A1, B1 & C1 on sheet 1, which will be save / store on sheet 2 at A1,B1, and C1, again I want to input new data on sheet 1 at A1,B1 and C1 which will be automatically save / store on Sheet 2 at A2, B2, and C2, repeatedly I want to input another new data on sheet 1 at A1, B1, & C1, whcih will be again save on sheet 2 at A3, B3, & C3 and so on. Means input cell (A1) on sheet 1 will be same but output data on sheet 2 will be A1,A2,A3 and so on. Rdgss........Sudipta Press ALT+F11 to get to VBA in Project - VBAProject window double click on Sheet1 sel...

Combine multiple tables with different date ranges
Hi everyone, I really need help please. I apologise in advance if my description is difficult to follow. I am very new to Access and have the following problem: Table 1 consists of: Machine ID (from another table) Downtime (calculated in queries from DateDiff expression based on Start Date & Time and End Date & Time) this table also has a few other fields - descriptive mainly This table is designed to track the amount of downtime for each machine and does so quite well, and even when there are multiple incidences of downtime in a day. It is for Daily entries as requ...

COUNTIF function with date range
Is there anyway to use the COUNTIF function with a date range. I'm trying to count each date within a particular month. For example, =COUNTIF(G5:G100, "12/**/03" And this example with the wildcards is currently not working. Thanks. Amy Hi, Try, =SUMPRODUCT(--(MONTH(G5:G100)=12)) Hope this helps! In article <fd8501c43e96$22a9daf0$a401280a@phx.gbl>, "Amy" <anonymous@discussions.microsoft.com> wrote: > Is there anyway to use the COUNTIF function with a date > range. I'm trying to count each date within a particular > month. For ...

Net book value should be zero when an asset is retired
When an asset is retired and has not been fully depreciated, the net book value is calculated as cost basis less LTD depreciation. Our client believes that this is bad accounting practice. The net book value should be zero when an asset has been retired, even if it has not bee fully depreciated. I understand that when running FA reports it excludes assets with a status of retired, however, when using smartlist > fixed assets book, there is no field to filter out all assets with a status of retired. This makes it difficult for the client to correctly analyse their assets, especial...

Indirect Range Referencing
Hello, I work in a paper mill. I am building a spreadsheet with several charts. I have a wide sheet of paper coming off of a paper machine of varying widths. This paper is wound up on a "reel".I have a scanner that measures the weight of the paper on the reel, and each scan gives me a 600 point array. I can calculate where the edges of the paper lie within the array, no problem, and plot the whole thing very nicely. The problem is that the paper then goes into a re-winder, where the wide sheet is cut into smaller sheets, that make up "rolls". I can calculate the sta...

rank
I would like to know how to get around duplicate issues when using th rank functio -- Message posted from http://www.ExcelForum.com Hi see: http://www.cpearson.com/excel/rank.htm and http://www.xldynamic.com/source/xld.RANK.html -- Regards Frank Kabel Frankfurt, Germany > I would like to know how to get around duplicate issues when using the > rank function > > > --- > Message posted from http://www.ExcelForum.com/ ...

if statement to format cell
can anybody tell me how to fill a cell with color red if the value is negative and no fill if it is positive? thanks Hi Use Conditional formatting for this select the cell(s) you want this to happen in format / conditional formatting cell value is less than 0 click on the format button choose the patterns tab, choose red click OK & OK Regards JulieD "hovendick" <anonymous@discussions.microsoft.com> wrote in message news:0a5201c47afa$d97d0b80$a501280a@phx.gbl... > can anybody tell me how to fill a cell with color red if > the value is negative and no fill if it i...