Formula Help Price Code

[natei6]

Hi all,
I have a list of prices, and I would like to convert them into code. BLACK HORSE: B=1 L=2 and so forth. I only need even dollar amounts, no cents. Someone gave me this formula, but it is for dollars and cents, and it makes 0=B instead of 1=B. This is what I am working with so far. Q is just a dummy letter in front of each code.
="Q"&IF(ISNUMBER(A1),MID($G$1,A1/1000+1,1)&MID($G$1,MOD(A1,1000)/100+1,1)&MID($G$1,MOD(A1,100)/10+1,1)&MID($G$1,MOD(A1,10)+1,1)&MID($G$1,(A1-INT(A1))*10+1,1)&MID($G$1,ROUND((A1*10-INT(A1*10)),0)*10+1,1),"")



Your help would be greatly appreciated.
Thanks again,
Nathan Sargeant

[Kassie]

Hi Nate

Excel is calculating on a 0 - 9 sequence, while you want a 1 - 0 sequence.
The easiest way of solving the problem is to comply, and in stead of using
*BLACKHORSE:*, rather use *:BLACKHORSE*. You will then get the desired
effect.
hth

"natei6" wrote:

>
> Hi all,
> I have a list of prices, and I would like to convert them into code.
> *BLACK HORSE:* B=1 L=2 and so forth. I only need even dollar amounts,
> no cents. Someone gave me this formula, but it is for dollars and
> cents, and it makes 0=B instead of 1=B. This is what I am working with
> so far. Q is just a dummy letter in front of each code.
>
> ="Q"&IF(ISNUMBER(A1),MID($G$1,A1/1000+1,1)&MID($G$1,MOD(A1,1000)/100+1,1)&MID($G$1,MOD(A1,100)/10+1,1)&MID($G$1,MOD(A1,10)+1,1)&MID($G$1,(A1-INT(A1))*10+1,1)&MID($G$1,ROUND((A1*10-INT(A1*10)),0)*10+1,1),"")
>
>
>
> Your help would be greatly appreciated.
> Thanks again,
> Nathan Sargeant
>
>
> --
> natei6
> ------------------------------------------------------------------------
> natei6's Profile: http://www.excelforum.com/member.php...fo&userid=7185
> View this thread: http://www.excelforum.com/showthread...hreadid=518949
>
>