Rank within Categories

  • Thread starter Thread starter calebfda
  • Start date Start date
C

calebfda

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","P","SW"}),IF($E$5:$E$40= E6,RANK(I6,$I$5:$I
$40,0),"N/A"), "not one of those")
 
This is one I used earlier that you should be able to adapt.

=IF(OR(ISBLANK($J12),ISTEXT($J12)),NA(),SUMPRODUCT(--(Group=H12),--(Score>=$J12)))-SUMPRODUCT(--(Group=$H12),--(ISTEXT(Score)))

Try and minimise the number of IFs and just the group ref cell into the
formula.

HTH
Peter Atherton
 
Peter

I will give that a try. What is your J12, H12? Is that the same as
my A, B?
Where do you set the ref of cells for it to look at?
tks
 
correction...i mean is it the same as my E and I

Peter

I will give that a try.  What is your J12, H12?  Is that the same as
my A, B?
Where do you set the ref of cells for it to look at?
tks






- Show quoted text -
Click to expand...
 
if i use
=IF(OR(ISBLANK($I5),ISTEXT($I5)),NA(),SUMPRODUCT(--(Group=$E5),--
(Score>=$I5)))-SUMPRODUCT(--(Group=$E5),--(ISTEXT(Score)))

i get ?NAME error
 
Sorry, I tend to use Range names, see my other post for range names. Range
names replace the value of the Range Address.

Debra Dalgliesh has great articles on range names including dynamic ranges
that expand as more data is added below the last entry. www.contextures.com

HTH
Peter
 
Peter

Got it solved with an arrary forumula!!

=IF(OR(E47={"C","SE","NE","P","SW"}),IF(F47>0,SUMPRODUCT(--($E$47:$E
$58=E47),--(F47<$F$47:$F$58))+1,"N/A")) &" of "& IF(OR(E5=
{"C","SE","NE","P","SW"}),COUNTIF($F$47:$F$58, ">0"),0)
 
Back
Top