Assistance with AND functions (i think!)

[Talbo]

I am reasonably new at using functions, I have been okay with some basic functions but I am struggling with some more complex ones. I am only assuming that an AND function is the correct way to do what I am proposing.

I am trying to build a risk calculator. I have a Column A1 "Consequence" with 5 sub-options in a drop down list and Column B1 "Likelihood" also with 5 sub options in a drop down list. I want to put a formula in C1 to calcuate the risk rating based on the combination of options selected in A1 and B1. I have given some examples below:

A1 B1 C1
CONSEQUENCE LIKELIHOOD RISK
Minor Likely Low

I can do a simple AND function and it works

=IF(AND(A1="Likely",B1="Minor"),"Low")

But how do I expand this formula to include all possible combinations as there are 5 options in both Likelihood and Consequence?

A1 B1 C1
CONSEQUENCE LIKELIHOOD RISK
Major Likely High

if you can help me out that would great!!!

Cheers
Lucas

[WinteE]

Try this :

Please Login or Register  to view this content.

You have to nest the IF's for all variances.

You also could think of creating your own Function for this in VBA.

[BigBas]

Your best bet is probably to use a lookup table of sorts. Post a .zip sample of your files, with all relevant information, and I'm sure we can help you out.

[MrShorty]

Nested IF's might work, but you'll have to be careful. With 5 options for in two categories, there are something like 5^2=25 combinations. With the possible exception of the latest version, you are limited to 7 nested functions. Even if you can nest to a higher level, it's not necessarily going to be easy to read/debug the function.

A lookup table is probably a good approach.

If it were me, I would probably "mathematicize" the problem. Assign a value of 1 to 5 for each of the options, then simply add up the two values. This sum will have a possible value of 2-10. Then decide how to assign risk. Maybe sum<4 means low risk, 4<=sum<7 means moderate risk, sum>=7 means high risk (or however you want to do it).