 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008use a letter in a cell to act as a number
Criteria. We would like to use the letters instead of numbers in columns B & C example = if likihood was H (4) (highly Likely) and Severity was E (4) (Extreme) then the Eligibility Status would be Critical (8) what type of formula would you need to write to populate column D with the word CRITICAL from the data (LETTERS) entered into columns B and C can email a "work in progress" example thanks John Johnnyboy5 <intermediatecare@googlemail.com> wrote in news:ba5e7f2d-0baa- 4fac-83e3-bf9994dd48ff@s37g2000prg.googlegroups.com: > Criteria. > We would like to use the letters instead of numbers in columns B & C [quoted text clipped - 11 lines] > > John you would need to make a little 2 column table 1st column is your letters 2nd column is the numbers that correspond to the letters then you would use a vlookup()for column b using your likelihood table add that to a vlookup () for column c using your Severity table you would need a 3rd table to define your Elegilibility Status, but in this case... column 1 would be numbers column 2 would be letters my email is down Pub's answer would work or you can use LOOKUP() thus: =IF(ISNA(LOOKUP(B3,{"H","M","S","V"},{4,2,1,3})+LOOKUP(C3,{"E","N","T","E"},{4,2,3,1})),"",LOOKUP(B3,{"H","M","S","V"},{4,2,1,3})+LOOKUP(C3,{"E","N","T","E"},{4,2,3,1})) Note that unlike Pub's VLOOKUP() where you can have the letters in any order by making the 4th argument FALSE, with LOOKUP() the letters *MUST* be in assending, (ie alphabetical), order.  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Criteria. > We would like to use the letters instead of numbers in columns B & C [quoted text clipped - 11 lines] > > John Never quite seen "alphabetical" described as "assending." But I guess it fits. I'm guessing that means it's in descending order. SignatureRegards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- > Pub's answer would work or you can use LOOKUP() thus: > [quoted text clipped - 19 lines] >> >> John > Never quite seen "alphabetical" described as "assending." But I guess it > fits. I'm guessing that means it's in descending order. No I don't think so. From XL Help on LOOKUP() **************************************** Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent. ***************************************** What I was trying to say was that the letters *had* to be in alphabetical order with the coresponding values in matching positions regardless of if the values were then not in numerical order. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Never quite seen "alphabetical" described as "assending." But I guess it > fits. I'm guessing that means it's in descending order. [quoted text clipped - 21 lines] >>> >>> John
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.