# 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

• 9.5 KB Views: 15

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

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

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

Thanks

#### Attachments

• 2 KB Views: 4
Last edited:

##### 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

• 10.8 KB Views: 3

#### 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

• 13.7 KB Views: 5

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

alright, thanks

#### GraH - Guido

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

• 24.8 KB Views: 8

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

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

• 11.8 KB Views: 6
Last edited:

#### GraH - Guido

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

#### 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

• 74.2 KB Views: 6

#### 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