• 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

Juniad

Member
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))))
 

Oscarr

Member
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

Last edited:

Juniad

Member
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.
 

GraH - Guido

Well-Known Member
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

Oscarr

Member
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

Oscarr

Member
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;)
 

bosco_yip

Excel Ninja
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

Last edited:

Oscarr

Member
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

bosco_yip

Excel Ninja
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
 
Top