I am having some trouble with using the rank formula. The cells I am referencing have a sum formula in them, and some of the values are 0.
When I enter my rank formula it does not return the right rank.
The formula i am using is; =IF(L2=0,1,RANK(L2,$L$2:$L21,1))
I would also like to link the rank with a name in another column.
Matthew Dyer wrote:
rank formula
16-Oct-09
I am having some trouble with using the rank(x,x:x) formula. The cell
I am referencing have a sum formula in them, and the values are < 0
When I enter my rank formula it returns #VALUE! instead of the rank
Is this because the values are less than 0? Is this because I am tryin
to referance a cell that has a formula in it? Is there a workaround
Will Batman make it to Gotham in time to stop the Joker? Help!!
Previous Posts In This Thread:
rank formula
I am having some trouble with using the rank(x,x:x) formula. The cell
I am referencing have a sum formula in them, and the values are < 0
When I enter my rank formula it returns #VALUE! instead of the rank
Is this because the values are less than 0? Is this because I am tryin
to referance a cell that has a formula in it? Is there a workaround
Will Batman make it to Gotham in time to stop the Joker? Help!!
Never mind. the range i was referancing had #VALUE!
Never mind. the range i was referancing had #VALUE!'s in it. Anyon
know a work-around for this problem? is there like a rangeif functio
or something?
Can you post some sample data (including a #VALUE!
Can you post some sample data (including a #VALUE! error or 2) and tell u
what results you expect
-
Bif
Microsoft Excel MV
Never mind. the range i was referancing had #VALUE!'s in it. Anyon
know a work-around for this problem? is there like a rangeif functio
or something?
P.S.Post *real* data. Don't just make up some artificial data.
P.S
Post *real* data. Don't just make up some artificial data
-
Bif
Microsoft Excel MVP
Ok, well, here is how to do it with the errors in the range.0.7500 = rank 10.
Ok, well, here is how to do it with the errors in the range
0.7500 = rank
0.6500 = rank
0.3500 = rank
0.1625 = rank
With the data in the range A2:A
Array entered** in B2 and copied down to B7
=IF(ISERROR(A2),"",SUM(IF(ISNUMBER(A$2:A$7),IF(A2<A$2:A$7,1)))+1
** array formulas need to be entered using the key combination o
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIF
key then hit ENTER
-
Bif
Microsoft Excel MV
Ok. I figured it out
Like i said before, the reason my functions werent working is becaus
my range had null values in it. The null values were a result of a su
funciton trying to add cells that had null values in them. What I a
going to do is use the if function to fix that. the new if function
are going to read =if(cell<>"N/A", do what I want, "N/A").
Good deal. Thanks for the feedback!
Good deal. Thanks for the feedback
-
Bif
Microsoft Excel MV
Biff, you really have no idea how much help the stupid "$" thing was
I was getting so frustrated with trying to freeze my array while re
sorting data that I could scream. I did not think there was a way to d
this, but obviously someone much smarter than myself thought of it
Thanks for all your help.
usHere is an example range I am working with.
u
Here is an example range I am working with. These values are the result
of a sum formula
0.750
0.650
0.350
0.162
What I need to do is be able to rank these values based only on th
values that actually have numbers. The basic formula I am using is =3
((RANK(q2,q2:q8, 1)) -1)/(COUNTIF(q2:q8, ">=3D0")-1) . I know the reaso
that this is not working is because the range I am using has null
values in it. Is there any way to modify this code to ignore the null
values or am I going to have to create a macro that will modify the
sheet to return "N/A" if a null value is returned?
ll us..sngOk. I figured it out.
ll us
...
s
ng
Ok. I figured it out.
Like i said before, the reason my functions werent working is because
my range had null values in it. The null values were a result of a sum
funciton trying to add cells that had null values in them. What I am
going to do is use the if function to fix that. the new if functions
are going to read =3Dif(cell<>"N/A", do what I want, "N/A").
IFTBiff, you really have no idea how much help the stupid "$" thing was.
IFT
Biff, you really have no idea how much help the stupid "$" thing was.
I was getting so frustrated with trying to freeze my array while re-
sorting data that I could scream. I did not think there was a way to do
this, but obviously someone much smarter than myself thought of it
.
Thanks for all your help.
EggHeadCafe - Software Developer Portal of Choice
Encryption On Mobile Devices in C# / .NET
http://www.eggheadcafe.com/tutorial...0-da5581fdf86b/encryption-on-mobile-devi.aspx