• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to use formulas auto count which one is the high score

Oscarr

Member
Hi,

how to use formulas auto count which one is the high score?

example:

score Result
Kent8Jack8 Draw
Kent0Jack5 Jack
Kent4Jack0 Kent
Kent0Jack7 Jack
Kent3Jack8 Jack

Thanks
 

Attachments

  • sample1.xlsx
    9.5 KB · Views: 15
copy this formula in G5 and fill down.
=IF(VALUE(MID(F5,5,1)-VALUE(RIGHT(F5,1)))=0,"Draw",IF(VALUE(MID(F5,5,1)-VALUE(RIGHT(F5,1)))<0,MID(F5,6,4),IF(VALUE(MID(F5,5,1)-VALUE(RIGHT(F5,1)))>0,LEFT(F5,4))))
 
copy this formula in G5 and fill down.
=IF(VALUE(MID(F5,5,1)-VALUE(RIGHT(F5,1)))=0,"Draw",IF(VALUE(MID(F5,5,1)-VALUE(RIGHT(F5,1)))<0,MID(F5,6,4),IF(VALUE(MID(F5,5,1)-VALUE(RIGHT(F5,1)))>0,LEFT(F5,4))))

hi @Juniad

may i know this is for which one? target which one?

this MID(F5,6,4) and this LEFT(F5,4))))

Thanks
 

Attachments

  • PIC.PNG
    PIC.PNG
    2 KB · Views: 4
Last edited:
Mid function is taking first Participant score and Right function is taking second participant score.. so after calculating the both score's whatever result occurring based on that Mid and Left functions extracting the participant names.
 
Hi Juniad, your formula would not be working if other longer/shorter names are used and if scores can be more then 1 digit on either side.

I needed helper columns to make it work, those can he hidden in the name manager. Formulae made with some "google" help.
 

Attachments

  • Copy of sample1.xlsx
    10.8 KB · Views: 3
Hi Juniad, your formula would not be working if other longer/shorter names are used and if scores can be more then 1 digit on either side.

I needed helper columns to make it work, those can he hidden in the name manager. Formulae made with some "google" help.

Hi @GraH - Guido

can direct count at result Column there? if make helper columns need do one more step and make a lot columns.. a bit trouble
 

Attachments

  • P.PNG
    P.PNG
    13.7 KB · Views: 5
Mid function is taking first Participant score and Right function is taking second participant score.. so after calculating the both score's whatever result occurring based on that Mid and Left functions extracting the participant names.

Hi @Juniad

alright, thanks;)
 
Hi @GraH - Guido

can direct count at result Column there? if make helper columns need do one more step and make a lot columns.. a bit trouble
Hi Oscarr, not sure I get your point. But I don't think you got mine neither.
Look at this alternative, using the name manager to "hide" the helper columns.
 

Attachments

  • Copy of sample1.xlsx
    24.8 KB · Views: 8
Another option,

One formula solution without helpers and ranged names, and the testing data with different length of name and score number.

61326

In G5, copied down :

=CHOOSE(TEXT(LOOKUP(1,-RIGHT(F5,ROW($1:$99)))-LOOKUP(1,-MID(F5,MIN(FIND({0,1,2,3,4}+{0;5},F5&1/19)),ROW($1:$99))),"1;2;3"),LEFT(F5,MIN(FIND({0,1,2,3,4}+{0;5},F5&1/19))-1),SUBSTITUTE(MID(F5,AGGREGATE(15,6,FIND(CHAR(ROW(INDIRECT("65:90"))),F5),2),99),-LOOKUP(1,-RIGHT(F5,ROW($1:$99))),),"draw")

Regards
Bosco
 

Attachments

  • ScoreResult(BY).xlsx
    11.8 KB · Views: 6
Last edited:
Another option,

One formula solution without helpers and ranged names, and the testing data with different length of name and score number.

View attachment 61326

In G5, copied down :

=CHOOSE(TEXT(LOOKUP(1,-RIGHT(F5,ROW($1:$99)))-LOOKUP(1,-MID(F5,MIN(FIND({0,1,2,3,4}+{0;5},F5&1/19)),ROW($1:$99))),"1;2;3"),LEFT(F5,MIN(FIND({0,1,2,3,4}+{0;5},F5&1/19))-1),SUBSTITUTE(MID(F5,AGGREGATE(15,6,FIND(CHAR(ROW(INDIRECT("65:90"))),F5),2),99),-LOOKUP(1,-RIGHT(F5,ROW($1:$99))),),"draw")

Regards
Bosco

hi @bosco_yip

may i know this is target which one and for what?

Thanks
 

Attachments

  • P.jpg
    P.jpg
    74.2 KB · Views: 6
hi @bosco_yip

may i know this is target which one and for what?

Thanks
It is difficult to explain the formula in full details…...as GraH-Guido said, "That's just full of tricks…......"

However, the logic of the formula structure as show in brief :

=CHOOSE(TEXT("Different between 1st and 2nd numbers","1;2;3"),"1st Person name","2nd Person name","draw")

Then, press F9 to evaluate the formula in step by step for detailing

Regards
Bosco
 
Back
Top