Formula Help Price Code
 

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

Formula Help Price Code
 

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