tiebreaker formula not working as expected

[FreakyGirl]

hello

i run a monthly contest that has ties that need to be broken down further.

i am down to the last column & having trouble getting it to work properly.

column AC: rank 1 sum of 6 high scores
column AD: rank 2 the indiv. scores that make the sum of 6
column AE: rank 3 columns AC & AD concentated
column AF: rank 4 check for num of games played. if same number then put total score at end of number. else put a 00

column AG: rank 5 add a players raw score to end of prev column number.
column AH: rank 6 check the raw score for any ties. if tied put the number shown in AL. else put a 00

column AI: rank 7 add a players lowest score to end of prev column number.

to this point everything seems to be working correctly.

column AJ: rank 8 add a players 2nd lowest score to end of prev column number

the problem with this column is its not picking up the number in column AN for some reason.

its a very simple formula

=VALUE(AI22*10+AN22)

& works correctly in the prev column.

as always any help would be appreciated.

thanks,
FreakyGirl

 

[David Evans]

You've run up against an Excel Limitation
See https://support.microsoft.com/en-us...to-zeroes-when-you-type-long-numbers-in-cells

in Col AJ you have 16 characters, so the situation described above comes into play.

There's a workaround described in the article

 

[bosco_yip]

In AJ11, try to change this :

=VALUE(AI11*10+AN11)

Into this, and copied down :

=AI11&AN11

p.s. the above result will become Text value, this is a workaround way for Excel 16 digits limitation

Regards
Bosco

 

[FreakyGirl]

hello

thank you both for the quick replies & possible solution.

however the problem with making it text is that it wont rank.

instead i used REPLACE() on the last 2 numbers so it didnt go over the 15 digits.

its working in column AI but not in AJ.

column AI

=IF(AND(SUMPRODUCT(--($AH$11:$AH$75=AH11),--($AL$11:$AL$75=AL11))=COUNTIF($AH$11:$AH$75,AH11),COUNTIF($AH$11:$AH$75,AH11)>1),REPLACE(AH11,RIGHT(AH11,2),2,0&AM11),AH11)

column AJ

=VALUE(IF(AND(SUMPRODUCT(--($AI$11:$AI$75=AI11),--($AM$11:$AM$75=AM11))=COUNTIF($AI$11:$AI$75,AI11),COUNTIF($AI$11:$AI$75,AI11)>1),REPLACE(AI11,RIGHT(AI11,1),1,AN11),AI11))

the slight diff in the 2 formulas is that i have to replace the last 2 numbers in AI but only the last number in AJ

additionally the ranking isnt working correctly for AK22 & AK23. what should be ranked 10 & 11 is now ranked 30 & 31. if i take out the VALUE() in the formula i get #NUM in those 2 rows.

any suggestions on what to do now?

i have attached the updated file.

thanks,
FreakyGirl

 

[NARAYANK991]

Hi ,

I think you are making your application too complicated ; just as an example , you are using the top 6 scores in column AD to add a 6 digit number to the original score in column AC to get a resulting score (we cannot call this a rank) of 8 digits in column AE.

Can you not do the following instead ?

From the top 6 scores , create a rank from 1 to 9 ; as far as I can see you need only one digit , but if you think you might need 2 digits , allocate 2 digits to this rank ; now add this rank to the existing 2 digit score in column AC ; straightaway , you save 4 digits.

Logically speaking , if you can start with a rank from 01 to 99 or from 001 to 999 , at every stage , if you resolve ties and re-rank , you will need exactly the same 2 digits 01 through 99 or 3 digits 001 through 999.

Narayan

 

[FreakyGirl]

hello

thanks for your reply but im not sure i completely understand what your saying.

without the initial 2 digit number before the top 6 scores, some ranking would be wrong.

for example:
AD24 7,7,7,6,5,5 sum is 37
AD25 8,8,7,5,4,4 sum is 36

AD24 is correctly ranked higher than AD25, but if only the TOP 6 are ranked AD25 will incorrectly show as higher.

its the same with AD34 & AD35. AD16 & AD17. AD20 & AD21

perhaps you can show me what you mean but i dont think you can leave those first 2 numbers out.

Thanks,
FreakyGirl

 

[NARAYANK991]

Hi ,

As an example , suppose the scores of 5 players H , M , A , L , B , are 44 , 44 , 42 , 41 , 41.

Now suppose we take the initial rankings based on these total scores ; the rankings would be 1 , 1 , 2 , 3 , 3 ; I am assuming that a higher score means a higher ranking , but even if it were different , the principle would still remain the same.

Now if we rank the players based on this score , the rankings would be :

H - 1 ,
M - 1 ,
A - 2 ,
L - 3 ,
B - 3

Now suppose the top 6 scores for these 5 players were as follows :

H - 28 , M - 27 , A - 31 , L - 25 , B - 31

Thus , the rankings based on the top 6 scores alone would be :

B - 1 ,
A - 1 ,
H - 2 ,
M - 3 ,
L - 4

Now , taking both criteria into account , and giving a greater weightage to the total score , we can multiply the first rank by 10 and add the second rank to get a composite rank of :

H - 1 * 10 + 2 = 12 ,
M - 1 * 10 + 3 = 13 ,
A - 2 * 10 + 1 = 21 ,
L - 3 * 10 + 4 = 34 ,
B - 3 * 10 + 1 = 31

which means the composite rank , considering both the scores together , would be :

H - 1 ,
M - 2 ,
A - 3 ,
B - 4 ,
L - 5

You can easily extend this principle to any number of players and any number of criteria.

Narayan

 

[shrivallabha]

How about applying following formula in cell AE11 as on right hand side as the finer tie-breaker points come in the multipliers seem to increase their weights.
=G11+SUBSTITUTE(AD11,",","")/1000000

See if it solves issue for you or not. Attached file for your reference.

Edit: Adjusted some terrible incoherent parts written. Hope it makes sense now.

 

[FreakyGirl]

thanks for trying Narayan but I wasn't able to get it working the way you described. with the scoring the way it is somethings just wont work right.

Shrivallabha, your file seemed to have fixed the problem! This is great!

thanks to both of you for your help,
FreakyGirl