View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ianh ianh is offline
external usenet poster
 
Posts: 5
Default Multiple Conditionals in MS Excel 2007

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" wrote:

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" wrote in message
...
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

"IanH" wrote:

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 a
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


.