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

[Oscarr]

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

 

[Juniad]

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]

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

 

[Juniad]

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]

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.

 

[Oscarr]

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

 

[Oscarr]

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

 

[GraH - Guido]

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.

 

[bosco_yip]

Another option,

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



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

 

[GraH - Guido]

That's just full of tricks... Will take a while to demystify for sure. Pressing F9 like hell .

 

[Oscarr]

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

 

[bosco_yip]

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