Multiple Conditionals in MS Excel 2007

[IanH]

I have been trying to work out how to go about calculating multiple
conditionals using the Wizard. In MS 2003 you can use the Add button.......in
2007 it seems to have disappeared???
I was hoping to be able to calculate the following for a Risk spreadsheet.
For each RED, AMBER, GREEN (RAG) cell (manually filled with appropriate
colour), I want to automatically work out the RAG status and fill with colour
(or use traffic ligts) based on the following.

Example:
the RAG cell is B8 (I wish to calculate this automatically)
I have a Severity cell I8 and Probability Cell J8
There are 3 levels, H (HIGH), M (MEDIUM), L (LOW). I manually populate these
values.

The conditions are:
If I8 is equal to H and J8 is equal to H then B8 should have a fill of RED
If I8 is equal to H and J8 is equal to M then B8 should have a fill of RED
If I8 is equal to M and J8 is equal to H then B8 should have a fill of RED

If I8 is equal to M and J8 is equal to M then B8 should have a fill of AMBER
If I8 is equal to M and J8 is equal to L then B8 should have a fill of AMBER
If I8 is equal to L and J8 is equal to M then B8 should have a fill of GREEN

If I8 is equal to L and J8 is equal to L then B8 should have a fill of GREEN
If I8 is equal to L and J8 is equal to M then B8 should have a fill of GREEN
If I8 is equal to M and J8 is equal to L then B8 should have a fill of GREEN

Is there a simple way to do this? Thanks in advance

 

[Fred Smith]

It depends on your definition of simple. In 2007, additional conditions are
created with the New Rule... menu item, and use Use a Formula.... For Red
you will want:
=or(and(i8="H",j8="H"),and(i8="H",j8="M"),and(i8="M",j8="H"))

Add a new rule for each of your other colors.

Regards,
Fred

 

[IanH]

Fred,

That worked a treat, thank you very much.

As part of the spreadsheet calculation I am also experiencing errors in the
following calculation:

=SUMPRODUCT(--(MONTH('Risk Log'!$L$8:$L$13)=MONTH('Risk Performance
Metrics'!F$2)),--(YEAR('Risk Log'!$L$8:$L$13)=YEAR('Risk Performance
Metrics'!F$2))+('Risk Log'!Q8:Q13="OPEN")+('Risk Log'!$I$8:$J$8="H"))

I am trying to retrieve the number of RISKS that are OPEN during the same
DATE cell as F2 (i.e. Mar-2010) that have a CELL value of H (HIGH) in both
Severity Column (values run from I8 to I13 cells) and Probability Column
(values run from J8 to J13 cells) but I am getting the #VALUE! error message??

Im laymans terms, I am trying to process the logic as:
--> List all OPEN Risks for Mar-2010 (or whatever month I list in F2) where
the Risk has an OPEN status AND a Severity and Probability value of H.

Can you steer me down the right track? Thanks

 

[Don Guillett]

Adapt this idea to suit

=SUMPRODUCT(--(TEXT($F$2:$F$12,"yyyymm")="201003")*G2:$G$12)

 

[Fred Smith]

Your addition should be multiplication. But for consistency, I would use:
=SUMPRODUCT(--(MONTH('Risk Log'!$L$8:$L$13)=MONTH('Risk Performance
Metrics'!F$2)),--(YEAR('Risk Log'!$L$8:$L$13)=YEAR('Risk Performance
Metrics'!F$2)),--('Risk Log'!Q8:Q13="OPEN"),--('Risk Log'!$I$8:$J$8="H"))

You can also simplify the date check as Don has shown.

Regards,
Fred

 

[IanH]

Hi Fred,

I have used the multiplication rather than addition but I am still
experiencing the #VALUE! error msg. I assume that the data look up is failing
somewhere and displaying the error message, rather than a formula error.

I am not actually sure that this formula will return the value that I am
looking for?

In this calculation I am hoping to display a numeric value of the number of
all OPEN Risks that are counted for the month (the value displayed in Cell F2
i.e. Mar-2010) where their corresponding Severity & Probability is equal to H
meaning High.

My formula is now using ,--('Risk Log'!Q8:Q13="OPEN"),--('Risk
Log'!$I$8:$J$8="H")) values.

I cannot see where the COUNT is executed and it looks like the Cells I8 to
J8 must ALL have a value of H to be TRUE and therefore counted where the
requirement is for each ROW that has an OPEN status (and opened in the month
specified by Cell F2) and has its ROW corresponding Probability & Reliability
Cells to have a H value in them both - to be counted.

Can you assist further with this? Thanks in advance

 

[Fred Smith]

A #Value error in Sumproduct is caused by having ranges that aren't the same
size. In your formula, the problem is the range i8:j8. It contains only 2
cells, your other ranges are 6 sells. Once you have consistent ranges, that
should get rid of the #Value error.

Regards,
Fred