use a letter in a cell to act as a number

  • Thread starter Thread starter Johnnyboy5
  • Start date Start date
J

Johnnyboy5

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
 
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
Click to expand...

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.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
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.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
 
Never quite seen "alphabetical" described as "assending." But I guess it
fits. I'm guessing that means it's in descending order.
Click to expand...

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.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top