# Iso 27005 Risk Value [Solved]

Ask a question christossol 6Posts Friday February 17, 2017Registration date April 10, 2017 Last seen - Last answered on Feb 21, 2017 at 05:06 AM by Mazzaropi
Hi All,

I have the following problem:

I have two cells that have a text value which can either X or M or H

X value is 0, M value is 1 and H value is 2

I need to check each cell and add the two cells.

Can anyone help?
+2
christossol, Good morning.

Scenario:

a) There are 2 separated cells: A1 and C4

b) A range of A1:B1

Try to use:

Case A

=LOOKUP(A1, {"h","m","x"}, {2, 1, 0}) + LOOKUP(C4, {"h","m","x"}, {2, 1, 0})

Case B

=SUM( COUNTIF(A1:B1, {"x", "m", "h"}) * {0, 1, 2})

Is that what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
christossol 6Posts Friday February 17, 2017Registration date April 10, 2017 Last seen -
Hi Mazzaropi,

I really thank you for your reply but the formula does not work 100%. It seems that it doesn't read one of the variables.

See explanation below

SITE Severity 3 (Cell B3)

Prop vuln
L M
Risk / Threat 4

LOOKUP(C3, {"H","M","L"}, {2,1,0}) + LOOKUP(D3, {"H","M","L"}, {2,1,0})+(\$B\$3)

and i added my constant row value which is the Site Severity (\$B\$3)!

I've changed the variables to "H"=high,"M"=Medium,"L"=Low.

For some reason though the formula does not see the entry for "L" nor in the first part or in the second part of the formula. If I enter the letter "G" it produces error. If I enter the letter "L" it gives wrong result ....

In the example above

L+M+3 should be equal to 4 but it returns the result 6!

H = 2
M = 1
L = 0

Again i really appreciated your feedback.
The aim is to calculate the risk value for a building according to ISO 27005. The above calculation is based on a standard matrix.

Regards

Christoss
+2
Christos, Good morning.

The error appears because you changed the result option letters but did not alphabetize them.

The values in LOOKUP_VECTOR must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value.
https://support.office.com/en-US/article/LOOKUP-function-446D94AF-663B-451D-8251-369D5E3864CB

Try to use:

=LOOKUP(C3, {"H","L","M"}, {2,0,1}) + LOOKUP(D3, {"H","L","M"}, {2,0,1}) + (\$B\$3)

Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
christossol 6Posts Friday February 17, 2017Registration date April 10, 2017 Last seen -
Good morning Mazzaropi,

You were right....It worked!!!! I really thank you support but mostly for your prompt response. These tables will help me in the organization to give them to department managers to help us with this ongoing project.
Your solution was spot on. Thank you again.

Christos
Cyprus
+1
Christos, Good morning.

Thanks for the feedback.

Have a nice day!
--
Belo Horizonte, Brasil.
Marcílio Lobão