Excel - VTRAC Conversion Formula - Pick 3

[Winalot2007]

Looking for a for Excel formula for Pick 3 Combos.

Digits To VTRAC Chart

0 and 5 = VTRAC = 1
1 and 6 = VTRAC = 2
2 and 7 = VTRAC = 3
3 and 8 = VTRAC = 4
4 and 9 = VTRAC = 5

In cell A1 I enter a Digit 7
In cell B1 I enter the formula =MOD(A1,5)+1
This would give me a VTRAC of 3 (1 Position or Number)
(See chart above) Digit 7 = VTRAC 3

If I enter 725 in cell A1, what formula could I use where cell B1 the result would be 331?
(Digit Combo 725 = VTRAC Combo 331)

Thanks!!

 

[Excellence]

=VALUE(CONCATENATE(MOD(VALUE(LEFT(A2;1));5)+1;MOD(VALUE(MID(A2;2;1));5)+1;MOD(VALUE(RIGHT(A2;1));5)+1))

It should work if I understand correct what you want.

 

[Winalot2007]

=VALUE(CONCATENATE(MOD(VALUE(LEFT(A2,1)),5)+1,MOD( VALUE(MID(A2,2,1)),5)+1,MOD(VALUE(RIGHT(A2,1)),5)+ 1))

Excellence this worked great.

The only problem is when you have a digit combos 000 through 099.

 

[Winalot2007]

OK,

Here is the corrected formula.

=VALUE(CONCATENATE(MOD(VALUE(LEFT(TEXT(A1,"000"),1)),5)+1,MOD( VALUE(MID(TEXT(A1,"000"),2,1)),5)+1,MOD(VALUE(RIGHT(A1,1)),5)+1))

 

[GillesD]

VTRAC formula

If you want to reduce the size of your file, especially if you have a lot of numbers for which you calculate the VTRAC value, you may want to use this shorter formula:

=MOD(INT(A9/100),5)*100+MOD(INT(A9/10),5)*10+MOD(A9,5)+111

It works since, in Excel, doing calculations on a string representing a number will usually result in a value. Even, it is not necessary to format the cell as "000" (three digits) since VTRAC values will range from 111 to 555.