rank formula

Can you post some sample data (including a #VALUE! error or 2) and tell us
what results you expect?
 >> Stay informed about: rank formula 

P.S.

Post *real* data. Don't just make up some artificial data.
 >> Stay informed about: rank formula 

On Oct 16, 8:14 pm, "T. Valko" wrote:
> P.S.
>
> Post *real* data. Don't just make up some artificial data.
>
> --
> Biff
> Microsoft Excel MVP
>
> "T. Valko" wrote in message
>

>
>
>
> > Can you post some sample data (including a #VALUE! error or 2) and tell us
> > what results you expect?
>
> > --
> > Biff
> > Microsoft Excel MVP
>
> > "Matthew Dyer" wrote in message
>
> > On Oct 16, 4:17 pm, Matthew Dyer wrote:
> >> I'm having some trouble with using the rank(x,x) formula. The cells
> >> I'm 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'm trying
> >> 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!'s in it. Anyone
> > know a work-around for this problem? is there like a rangeif function
> > or something?- Hide quoted text -
>
> - Show quoted text -

Here's an example range I'm working with. These values are the results
of a sum formula.

#VALUE!
#VALUE!
0.7500
0.6500
0.3500
0.1625

What I need to do is be able to rank these values based only on the
values that actually have numbers. The basic formula I'm using is =
((RANK(q2,q2:q8, 1)) -1)/(COUNTIF(q2:q8, ">=0")-1) . I know the reason
that this is not working is because the range I'm 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?
 >> Stay informed about: rank formula 

On Oct 17, 8:37 am, Matthew Dyer wrote:
> On Oct 16, 8:14 pm, "T. Valko" wrote:
>
>
>
>
>
> > P.S.
>
> > Post *real* data. Don't just make up some artificial data.
>
> > --
> > Biff
> > Microsoft Excel MVP
>
> > "T. Valko" wrote in message
>
>
>
> > > Can you post some sample data (including a #VALUE! error or 2) and tell us
> > > what results you expect?
>
> > > --
> > > Biff
> > > Microsoft Excel MVP
>
> > > "Matthew Dyer" wrote in message
> >
> > > On Oct 16, 4:17 pm, Matthew Dyer wrote:
> > >> I'm having some trouble with using the rank(x,x) formula. The cells
> > >> I'm 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'm trying
> > >> 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!'s in it. Anyone
> > > know a work-around for this problem? is there like a rangeif function
> > > or something?- Hide quoted text -
>
> > - Show quoted text -
>
> Here's an example range I'm working with. These values are the results
> of a sum formula.
>
> #VALUE!
> #VALUE!
> 0.7500
> 0.6500
> 0.3500
> 0.1625
>
> What I need to do is be able to rank these values based only on the
> values that actually have numbers. The basic formula I'm using is =
> ((RANK(q2,q2:q8, 1)) -1)/(COUNTIF(q2:q8, ">=0")-1) . I know the reason
> that this is not working is because the range I'm 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?- Hide quoted text -
>
> - Show quoted text -

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'm
going to do is use the if function to fix that. the new if functions
are going to read =if(cell<>"N/A", do what I want, "N/A").
 >> Stay informed about: rank formula 

On Oct 17, 10:30 am, "T. Valko" wrote:
> Ok, well, here's how to do it with the errors in the range.
>
> #VALUE! = blank
> #VALUE! = blank
> 0.7500 = rank 1
> 0.6500 = rank 2
> 0.3500 = rank 3
> 0.1625 = rank 4
>
> With the data in the range A2:A7
>
> 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 of
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
> key then hit ENTER.
>
> --
> Biff
> Microsoft Excel MVP
>

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 didn't think there was a way to do
this, but obviously someone much smarter than myself thought of it .
Thanks for all your help.
 >> Stay informed about: rank formula 

Ok, well, here's how to do it with the errors in the range.

#VALUE! = blank
#VALUE! = blank
0.7500 = rank 1
0.6500 = rank 2
0.3500 = rank 3
0.1625 = rank 4

With the data in the range A2:A7

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 of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 >> Stay informed about: rank formula 

Good deal. Thanks for the feedback!
 >> Stay informed about: rank formula 

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) formula. The cells
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 trying
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:

On Friday, October 16, 2009 10:09 PM
Matthew Dyer wrote:

rank formula
I am having some trouble with using the rank(x,x) formula. The cells
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 trying
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!!

On Friday, October 16, 2009 10:09 PM
Matthew Dyer wrote:

Never mind. the range i was referancing had #VALUE!
Never mind. the range i was referancing had #VALUE!'s in it. Anyone
know a work-around for this problem? is there like a rangeif function
or something?

On Friday, October 16, 2009 11:06 PM
T. Valko wrote:

Can you post some sample data (including a #VALUE!
Can you post some sample data (including a #VALUE! error or 2) and tell us
what results you expect?
 >> Stay informed about: rank formula 

[URL="http://www.cpearson.com/excel/Rank.aspx"]http://www.cpearson.com/excel/Rank.aspx[/URL]
 >> Stay informed about: rank formula