Report

Iso 27005 Risk Value [Solved]

Ask a question christossol 4Posts Friday February 17, 2017Registration date March 16, 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?
See more 
Helpful
+2
plus moins
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
Was this answer helpful?  
christossol 4Posts Friday February 17, 2017Registration date March 16, 2017 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
Reply
Leave a comment
Helpful
+2
plus moins
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
Was this answer helpful?  
christossol 4Posts Friday February 17, 2017Registration date March 16, 2017 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
Reply
Leave a comment
Helpful
+1
plus moins
Christos, Good morning.

Thanks for the feedback.
Glad to have helped you.

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

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!