Rank within Categories

[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")

 

[Billy Liddel]

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

 

[calebfda]

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

 

[calebfda]

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 -

 

[calebfda]

if i use
=IF(OR(ISBLANK($I5),ISTEXT($I5)),NA(),SUMPRODUCT(--(Group=$E5),--
(Score>=$I5)))-SUMPRODUCT(--(Group=$E5),--(ISTEXT(Score)))

i get ?NAME error

 

[Billy Liddel]

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

 

[calebfda]

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)

 

[Billy Liddel]

Glad you worked it out yourself, nice formula!

Peter