Previous Topic Next Topic

Iso 27005 Risk Value

Solved/Closed
christossol Posts 15 Registration date Friday February 17, 2017 Status Member Last seen March 23, 2022 - Feb 17, 2017 at 08:22 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - 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?
Related:

3 responses

Answer 1 / 3
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Feb 17, 2017 at 09:12 AM
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
2
christossol Posts 15 Registration date Friday February 17, 2017 Status Member Last seen March 23, 2022
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
0
Answer 2 / 3
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Feb 20, 2017 at 08:32 AM
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.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-8251-369d5e3864cb?ui=en-us&rs=en-us&ad=us

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
2
christossol Posts 15 Registration date Friday February 17, 2017 Status Member Last seen March 23, 2022
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
0
Answer 3 / 3
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Feb 21, 2017 at 05:06 AM
Christos, Good morning.

Thanks for the feedback.
Glad to have helped you.

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