Excluding high value from ranges

[Johnmus]

Hi,

I am ranking 6 different number ranges f.e W4:W26, W30:W52, W56:W78 etc. with this formula
=RANK(W4,(W$4:W$26),0) I am attaching a sample.

The entire range of numbers is (W4:W156) and I would like to setup a formula that determines the ranking in each segment minus the highest score of the entire range. The highest score received the overall award and should not be part of the ranking, but in the division it occurs the placements would change - 2nd ranked would now get 1st..

So for example if the highest score of the entire range would be in the W4:W26 range the formula would be =RANK(W4,(W$4:W$26),0)-1
In the other ranges the absence of the high score would result in =RANK(W4,(W$4:W$26),0)

As we don't know in which division the highest score willI occur, I wonder if it would be possible to state that IF the max value of the entire range AND the max value in specified range are the same, ranking would be rank(range)-1

Is it possible to setup a formula to accomplish this?

I appreciate any help in this area.

[NBVC]

Possibly (and assuming T160 can be used to determine which is the max combination you want).

Please Login or Register  to view this content.

adjusting W$4:W$26 for each range.

[Johnmus]

NBVC,

Thanks for your help, but I get a zero in the place column using your code. The placements should be by division (W4:W26) being the first division to perform etc.
I am using =RANK(W4,(W$4,W$6,W$8,W$10,W$12,W$14,W$16,W$18,W$20,W$22,W$24,W$26),0)
and changing it in the PLace Column to Rank(range)-1 in the division, once the high overall score has been determinded. Since other people are completing the form I am trying to make it as easy as possible.

Would it be possible to state that if the max value of the entire range (W4:W156) AND the same max value would also be in a division (W4:W26) (W30:W56)etc the value would be true, thus rank(range),0)-1, if not true rank(range),0)

Thanks for your efforts, perhaps this is a strech.

[NBVC]

See column AA in the attached... I only did the first section.

Where is the problem?

[Johnmus]

I changed the formula in AA somewhatto
=IF(W4="","",RANK(W4,(W$4:W$26),0)-1)-COUNTIFS($W$4:$W$26,LEFT($Z160,FIND(" ",$Z160))+0,$B$4:$B$26,MID($TZ160,FIND(" ",$Z160)+1,255))

I referenced the high score in Z 160 and added -1 to the rank formula. This works for the first W4:W26 range, as the max value is ranked 0 and the other 1 through 12. Perfect!

However in the next divisions I need the ranking to be 1 throuogh 12 as the high score award is already in the first division W4:W26. So I need to add an IF argument regarding the ranking.

Does that make sense?

Thanks!

[NBVC]

This part:

COUNTIFS($W$4:$W$26,LEFT($Z160,FIND(" ",$Z160))+0,$B$4:$B$26,MID($TZ160,FIND(" ",$Z160)+1,255))


will result in 0 or 1 based on if the subsection contains the Max value and associated name from column B... this function was meant to replace the 1 in RANK(W4,(W$4:W$26),0)-1)

so that if the COUNTIF() found the match the 1 would be subtracted from the Rank(), else 0 would be subtracted....

[Johnmus]

Thank you. This formula when located in the AA column works:
=IF(W4="","",RANK(W4,(W$4:W$26),0)-COUNTIFS($W$4:$W$26,LEFT($Z$160,FIND(" ",$Z$160))+0,$B$4:$B$26,MID($Z$160,FIND(" ",$Z$160)+1,255)))

However when I cut and paste to the PLACE (X) column I get a circular reference warning and if I continue the rankings do not change.

Any ideas? I probably do something wrong.

Thanks

[NBVC]

That is because the formula in T160 references column X and so if you put the new formula in column X, you get circular referencing...

Also the fact that column X has merged cells, you won't be able to use array formulas and copy them down... generally, merged cells is not recommended, especially if using them with formulas...

You can alter the formula in T160 to:

=MAX(IF(Y4:Y159<>"",T4:T159))&" "&INDEX(B4:B159,MATCH(1,((Y4:Y159<>"")*(T4:T159=MAX(IF(Y4:Y159<>"",T4:T159)))),0))

confirmed with CSE keys,.. then you won't get the circular reference, but you will have a hard time with the merged cells...


Note: For testing purposes, I had created the 198 West Corvina to match up in first subrange... i.e. I altered row 12 for testing purposes only! Please change back to original, if necessary.
.
.
.

[Johnmus]

Thank you for all your patience. I removed all references to column X and it is now working perfectly!

Yes, I understand that merging cells causes problem with array formulas. I seems possible to do the merging after the formulas have been entered.

I changed the list of scores/schools in column Z to this array formula:

=LARGE(IF($X$4:$X$26<>0,$W$4:$W$26),1)&" "&INDEX($B$4:$B$26,MATCH(1,(($X$4:$X$26<>0)*($W$4:$W$26=LARGE(IF($X$4:$X$26<>0,$W$4:$W$26),1))),0))

this will account for occurrance of 0 or not in the range. It seems cumbersome. Is there an easier way?

Also, final question. Is it possible in the above formula to have the option of 0 or SS? I would like to change the 0 to SS (Sweepstakes) prior to printing and release to competitors.

Again, thank you so much for all your help.

[NBVC]

I am not exactly sure where you are putting that formula and what it is supposed to do. When you copy it down, nothing changes in the formula, so it will return same result in all cells....

[Johnmus]

Sorry, I was not more clear in my previous post. The previous formula in Column Z was to rank score and school from 1st through 12th, like this:

=(LARGE($W$4:$W$26,1)&" "&INDEX($B$4:$B$26,MATCH(LARGE($W$4:$W$26,1),$W$4:$W$26,0)))

But your much appreciated solution how to deal with the possible inclusion of a 0 in the placements, necessitated a change in that particular formula, so this is what I came up with. It works but seems cumbersome to me.

=LARGE(IF($X$4:$X$26<>0,$W$4:$W$26),1)&" "&INDEX($B$4:$B$26,MATCH(1,(($X$4:$X$26<>0)*($W$4:$W$26=LARGE(IF($X$4:$X$26<>0,$W$4:$W$26),1))),0))

After I copy down the column, I then change the needed rank, ,1,2,3,4 through 12.

I figured out how to change the 0 to SS - creating a formula below the placement. That cell was previously merged.

Thanks again for your patience . Your help is much appreciated!

[NBVC]

I am not sure it would be less cumbersome... you do have two formulas in there combining two results with a concatenation...because of the <>0 condition you need to check it for the LARGE() function too to match things up properly.

you can change the hard coded k factor for the Large Function to something like ROWS($A$1:$A1) which will act as a counter as you copied down...