Rank function returns '0'

  • Thread starter Thread starter Rico
  • Start date Start date
R

Rico

Having a problem with the seemingly oh so simple rank function.
It keeps returning 0 in the cell on the sheet in which I have th
formula, yet when I look in the Function Arguments the Formula resul
is correct???
But it just shows '0' in the cell.
Tried lots of filtering with different functions and the iserror tric
to rid the column of errors but it will not work.
=if(iserror(j2:j10),"",rank(j3,j2:j10)) produces a blank cell, but th
Formula result is correct in the Function Arguments box.
Baffled (but it's not the first time)
 
Hi
what data is in your J2:J10 data area?
Normaly a simple
=RANK(J3,J2:J10)
would be sufficient
 
J2:j46 contain this formula
=VLOOKUP(INT(lastinrow(K2:IV2)),Averages!$A$10:$V$30,MATCH(I2,Averages!$A$9:$V$9,0),FALSE)

and the result for j2 is 100.444
The standard Rank works if all cells in column J have a result and th
Rank function and iserror produces a result when I look into th
Function arguments dialog box. It just doesn't show the result on th
screen.
Everything else seems to work.
Is there something I need to set/reset in options or tools perhaps
 
Hi
try changing your formula in J2:J46 to
=IF(ISNA(VLOOKUP(INT(lastinrow(K2:IV2)),Averages!$A$10:$V$30,MATCH(I2,A
verages!$A$9:$V$9,0),FALSE)),"",VLOOKUP(INT(lastinrow(K2:IV2)),Averages
!$A$10:$V$30,MATCH(I2,Averages!$A$9:$V$9,0),FALSE))
 
Try:

=IF(ISNUMBER(J2),"",RANK(J2,$J$2:$J$10))

instead of:

=if(iserror(j2:j10),"",rank(j3,j2:j10))
 
Hi Frank
Thank you for that and it seemed to work for a few minutes, but now
am back where I started. When I say it worked for a few minutes I mea
it woked on J2 but not on the few other populated cells I have in m
sheet for testing purposes.
I have learned a few things though. I was getting a circular referenc
message but that also seems to have gone.
I am getting a lot of #name? errors.

Aha. (some time has passed) This seems to be the problem.
Getting rid of these error messages which are produced because all th
cells are not populated. The problem for this exercise is that ther
will always be some cells that have no entries. This workbook is fo
sport club calculations and there must be allowance for members of th
club who cannot compete regularly.
I think the problem is that Excel does not just look at the number in
cell but looks at the formula that produced that number. Am I right?
This is where the circular ref and error messages come from?
Should I be looking at a redesign and pivot tables or even Access?
Thank you for your patience if you have got this far.
Richar
 
Hi
not sure why you got a circular reference error.
You may explain your current spreadsheet layout with some more detail:
- there's your data stored
- is this date manual entered or created by formulas
- there do you want to store your results
- etc.
 
Thanks Frank, and Aladin. But still no joy.
This problem arose after I thought that the help I received in thi
earlier thread had finally fixed my project,
http://www.excelforum.com/showthread.php?s=&threadid=213653

I thought I had it all worked out but adding the rank function doe
seem to reveal a circular reference, or so Excel tells me when I ope
the workbook.

Briefly
Accross row 1 are headings for name competitor number and some simpl
calcs from their scores, then the dificult 3,

Column H is Rank
Column I is Average with this formula
=IF(COUNTIF(K2:IV2,"<>")<6,"Can'
calculate!",INT(AVERAGE(LARGE(INDIRECT(ADDRESS(ROW(),LARGE(ISNUMBER(K2:IV2)*COLUMN(K2:IV2),{2,3,4,5,6}))),{1,2,3}))))
Column J has Franks formula or will return the correct value with th
one I was sooo proud of. It uses this Function I found
Function LASTINROW(rngInput As Range) As Variant
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Rows(1).EntireRow
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINROW = WorkRange(i).Value
Exit Function
End If
Next i
End Function
It obviously references a sheet I painstakingly created calle
Averages.

Following these columns ...k,l,m,n,... the scorer enters the date o
competition (usually a sunday but there is no need to lock it in a
this may not always be so) and then runs down the column entering th
scores for those who competed.

I am producing a tally sheet which will include other information abou
the competitors. Would it be better to perform all these calculation
on another sheet.

Sorry about the late reply but I had to prepare for a meeting I ha
today which went all day.

Richar
 
Just a message to say, thank for everyones help and I have managed t
work this out.
Combining what I learned from the posts taught me quite a lot, but the
there is a lot to learn.
Aladins formula was nearly there so after some contemplation I modifie
it to
IF(ISNUMBER($J$2:$J$46),RANK(J2,$J$2:$J$46)

Works if there are no error messages in the columns.

Got rid of the error messages by using
=IF(COUNTIF(K2:IV2,"<>")<6,"",(formula)
That can be modified to =IF(COUNTIF(K2:IV2,"<>")<1,"", for Col J

Happy campe
 
Rico,

That should be:

=IF(ISNUMBER(J2),RANK(J2,$J$2:$J$46),"")

In my original post, the last 2 args were incorrectly exchanged...
 
Back
Top