Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some Positive, Some Negative
I have a table of letters which correspond to single it numbers. The letters
a thru i correspond to 1 thru 9, and j thru r correspond to 1 thru 9 as well. I get a table of multi digit alphnumerics in the form 4567C or 398g or 28754n, etc. I place the letters and corresponding digits in a lookup table. I replace the letters at the end with the corresponding number as defined above with the formula =SUBSTITUTE(D2,RIGHT(D2,1),VLOOKUP(RIGHT(D2,1),$A$ 1:$B$19,2)) Works fine. What I cant figure out is if the letter is between j thru r, I need to must convert the formula result to a negative number. For example, 458b becomes 4582, while 458k becomes -4582. Have tried IF, but cant seem to get the syntax correct. Possibly some sort of array formula might help, but cant get that either. Any suggestions??? Thanks, Marsh |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some Positive, Some Negative
One way:
=SUBSTITUTE(D2,RIGHT(D2),VLOOKUP(RIGHT(D2),$A$1:$B $19,2))*IF(ISNUMBER(MATCH(RIGHT(D2),$A$10:$A$19,0) ),-1,1) "Marsh" wrote: I have a table of letters which correspond to single it numbers. The letters a thru i correspond to 1 thru 9, and j thru r correspond to 1 thru 9 as well. I get a table of multi digit alphnumerics in the form 4567C or 398g or 28754n, etc. I place the letters and corresponding digits in a lookup table. I replace the letters at the end with the corresponding number as defined above with the formula =SUBSTITUTE(D2,RIGHT(D2,1),VLOOKUP(RIGHT(D2,1),$A$ 1:$B$19,2)) Works fine. What I cant figure out is if the letter is between j thru r, I need to must convert the formula result to a negative number. For example, 458b becomes 4582, while 458k becomes -4582. Have tried IF, but cant seem to get the syntax correct. Possibly some sort of array formula might help, but cant get that either. Any suggestions??? Thanks, Marsh |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some Positive, Some Negative
Another idea:
Add a 3rd column to your lookup table that is the multiplier: A:I = 1, J:R = -1 =REPLACE(D2,LEN(D2),1,VLOOKUP(RIGHT(D2),A$1:C$19,2 ))*VLOOKUP(RIGHT(D2),A$1:C$19,3) Biff "Marsh" wrote in message ... I have a table of letters which correspond to single it numbers. The letters a thru i correspond to 1 thru 9, and j thru r correspond to 1 thru 9 as well. I get a table of multi digit alphnumerics in the form 4567C or 398g or 28754n, etc. I place the letters and corresponding digits in a lookup table. I replace the letters at the end with the corresponding number as defined above with the formula =SUBSTITUTE(D2,RIGHT(D2,1),VLOOKUP(RIGHT(D2,1),$A$ 1:$B$19,2)) Works fine. What I cant figure out is if the letter is between j thru r, I need to must convert the formula result to a negative number. For example, 458b becomes 4582, while 458k becomes -4582. Have tried IF, but cant seem to get the syntax correct. Possibly some sort of array formula might help, but cant get that either. Any suggestions??? Thanks, Marsh |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some Positive, Some Negative
Thank you both for showing me two differenct approaches to a solution to my
problem. Glad you were able to help and that you allowed me to pick your brains. Marsh "Marsh" wrote: I have a table of letters which correspond to single it numbers. The letters a thru i correspond to 1 thru 9, and j thru r correspond to 1 thru 9 as well. I get a table of multi digit alphnumerics in the form 4567C or 398g or 28754n, etc. I place the letters and corresponding digits in a lookup table. I replace the letters at the end with the corresponding number as defined above with the formula =SUBSTITUTE(D2,RIGHT(D2,1),VLOOKUP(RIGHT(D2,1),$A$ 1:$B$19,2)) Works fine. What I cant figure out is if the letter is between j thru r, I need to must convert the formula result to a negative number. For example, 458b becomes 4582, while 458k becomes -4582. Have tried IF, but cant seem to get the syntax correct. Possibly some sort of array formula might help, but cant get that either. Any suggestions??? Thanks, Marsh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add sequences of positive then negative numbers | Excel Discussion (Misc queries) | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
change cell from negative to positive | Excel Discussion (Misc queries) | |||
How can I change positive numbers to negative, i.e. change 50 to - | Excel Discussion (Misc queries) |