LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 37
Default 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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add sequences of positive then negative numbers judoist Excel Discussion (Misc queries) 6 November 26th 05 05:51 AM
I Need a formula to evaluate a cell with + or - values Bob in Oklahoma Excel Worksheet Functions 6 October 31st 05 02:41 PM
Formula to make Negative Values Positive & Positive Values Negative? mustard Excel Discussion (Misc queries) 4 September 26th 05 10:05 PM
change cell from negative to positive Terry Excel Discussion (Misc queries) 1 July 15th 05 07:15 PM
How can I change positive numbers to negative, i.e. change 50 to - godwingi Excel Discussion (Misc queries) 5 February 28th 05 05:41 PM


All times are GMT +1. The time now is 07:49 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"