| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Conditionals in MS Excel 2007
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Conditionals in MS Excel 2007
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Conditionals in MS Excel 2007
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Conditionals in MS Excel 2007
|
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Conditionals in MS Excel 2007
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Conditionals in MS Excel 2007
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 "IanH" wrote in message ... 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditionals statements | Excel Worksheet Functions | |||
| conditionals formulas | Excel Worksheet Functions | |||
| Hep with Conditionals actions please. | Excel Worksheet Functions | |||
| Conditionals containing variables | Excel Discussion (Misc queries) | |||
| Conditionals | Excel Worksheet Functions | |||
Linear Mode
