Iso 27005 Risk Value [Solved/Closed]

christossol 11 Posts Friday February 17, 2017Registration date February 22, 2018 Last seen - Feb 17, 2017 at 08:22 AM - Latest reply: Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen
- Feb 21, 2017 at 05:06 AM
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?
See more 

5 replies

Best answer
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Feb 17, 2017 at 09:12 AM
2
Thank you
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

Thank you, Mazzaropi 2

Something to say? Add comment

CCM has helped 1692 users this month

christossol 11 Posts Friday February 17, 2017Registration date February 22, 2018 Last seen - Feb 20, 2017 at 08:05 AM
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



Your formula:

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
Best answer
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Feb 20, 2017 at 08:32 AM
2
Thank you
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

Thank you, Mazzaropi 2

Something to say? Add comment

CCM has helped 1692 users this month

christossol 11 Posts Friday February 17, 2017Registration date February 22, 2018 Last seen - Feb 21, 2017 at 03:30 AM
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
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Feb 21, 2017 at 05:06 AM
1
Thank you
Christos, Good morning.

Thanks for the feedback.
Glad to have helped you.

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