Iso 27005 Risk Value [Solved/Closed]

Question

Ask a question Report Reply to this topic

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 1826 Posts Monday August 16, 2010Registration dateContributorStatus April 15, 2018 Last seen

- Feb 21, 2017 at 05:06 AM

Excel Ask a question Report Reply to this topic

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?

5 replies

Report Respond to Mazzaropi

Mazzaropi 1826 Posts Monday August 16, 2010Registration dateContributorStatus April 15, 2018 Last seen - Feb 21, 2017 at 05:06 AM

+1

Helpful

Christos, Good morning.

Thanks for the feedback.
Glad to have helped you.

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

Accepted Answer

Report Respond to Mazzaropi

Mazzaropi 1826 Posts Monday August 16, 2010Registration dateContributorStatus April 15, 2018 Last seen - Feb 17, 2017 at 09:12 AM

+2

Helpful

1

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? Yes No

Report

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

Accepted Answer

+2

Helpful

1

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? Yes No