Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Iso 27005 Risk Value [Solved/Closed]

Report
Posts
13
Registration date
Friday February 17, 2017
Status
Member
Last seen
February 24, 2019
 -
Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020
 -
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

3 replies

Answer 1 / 3
Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020
 136
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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report
Posts
13
Registration date
Friday February 17, 2017
Status
Member
Last seen
February 24, 2019
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
Answer 2 / 3
Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020
 136
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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report
Posts
13
Registration date
Friday February 17, 2017
Status
Member
Last seen
February 24, 2019
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
Answer 3 / 3
Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020
 136
Christos, Good morning.

Thanks for the feedback.
Glad to have helped you.

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