Using Conversion Formulas

If you want to see the actual values in Excel before they are sent to Autotrader, you can use the following equations:

• Assume A1 has the original price (in 32nds) and B1 has the number of offset ticks:

=INT(A1/100)*100+INT((B1+MOD(A1, 100))/32)*100+MOD(B1+MOD(A1,100), 32)

A1B1Result
1103103111010
111020-3110310
• Assume A2 has the original price (in ½ 32nds) and B2 has the number of offset ticks:

=INT(A2/1000)*1000+INT((B2/0.2+MOD(A2, 1000))/320)*1000+MOD(B2/0.2+MOD(A2,1000), 320)

A2B2Result
1103103111005
110310-3110295
• Assume A3 has the original price in ¼ 32nds and B3 has the number of offset ticks:

=(INT(A3/1000)+INT((INT(MOD(A3,1000)/10)+

INT((ROUNDUP(MOD(A3,10)*0.4,0)+B3)/4))/32))*1000+

MOD(INT(MOD(A3,1000)/10)+

INT((ROUNDUP(MOD(A3,10)*0.4,0)+B3)/4),32)*10+

INT(MOD(ROUNDUP(MOD(A3,10)*0.4,0)+B3,4)/0.4)

A3B3Result
1103123111000
110312-2110307
• Assume that C32 and D32 contain prices in 32nds, 1/2 32nds, or 1/4 32nds (can be positive or negative), then the result of adding the two can be expressed as:

=(INT(ABS(K34)/128)*1000+INT((ABS(K34)-(INT(ABS(K34)/128)*128))/4)*10+INT((ABS(K34)-INT(ABS(K34)/128)*128-INT((ABS(K34)-(INT(ABS(K34)/128)*128))/4)*4)/0.4))*INT(ABS(K34+0.1)/(K34+0.1))

Where K34 equals:

INT(ABS(C32)/1000)*INT(ABS(C32)/C32)*128+INT(MOD(ABS(C32),1000)/10) *INT(ABS(C32)/C32)*4+ROUNDUP(ABS(MOD(ABS(C32),10) *INT(ABS(C32)/C32))*0.4,0)*INT(ABS(C32)/C32)+INT(ABS(D32)/1000) *INT(ABS(D32)/D32)*128+MOD(ABS(TRUNC(D32/10)),100) *INT(ABS(D32)/D32)*4+ROUNDUP(ABS(MOD(ABS(D32),10) *INT(ABS(D32)/D32))*0.4,0)*INT(ABS(D32)/D32)

You cannot substitute K34 into the first formula because the resulting expression is too long for Excel to handle.

C32D32Result
99152-20598267
9915299155-2

Converting Floating Point to a Price

Another set of equations converts a floating point number to a price. If you are using a decimal price feed or a decimal theoretical, use one of these equations to convert it.

Assume A6 has the original price in decimal format for a product that ticks in 32nds. Multiply the result by 10 if you want a zero on the end.

• Round down for the bid.

=FLOOR((A6-INT(A6))*32+INT(A6)*100,1)

• Round up for the ask.

=CEILING((A6-INT(A6))*32+INT(A6)*100,1)

The formula would be modified for products that trade in ½ or ¼ 32nds.