| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a formula to return a particular result from a Matrix Tab
I currently have a Matrix table below which contains the values I want
returned when the 2 columns - Impact Degree & % Customer Impacted are populated. % Customer Impact Impact Degree <2% 2%-10% 10% Nil NIL LOW MODERATE Low LOW MODERATE HIGH Moderate MODERATE HIGH VERY HIGH Significant HIGH VERY HIGH EXTREME So for example if the Impact Degree is Low and the % Impacted is 10% then the value I want returned is HIGH. I am currently using IF formulas adn trying to nest these without success. I know that there are maybe lookup formulas I can use but this seems a bit beyond me as I do not know how to reference the above table if in a different worksheet but in the same workbook. Thanks Dave |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a formula to return a particular result from a Matrix Tab
Try this:
=INDEX(A1:A5,MATCH("Low",A1:A5,0),MATCH("10%",A1: D1,0)) "Dave Ah Ching" wrote: I currently have a Matrix table below which contains the values I want returned when the 2 columns - Impact Degree & % Customer Impacted are populated. % Customer Impact Impact Degree <2% 2%-10% 10% Nil NIL LOW MODERATE Low LOW MODERATE HIGH Moderate MODERATE HIGH VERY HIGH Significant HIGH VERY HIGH EXTREME So for example if the Impact Degree is Low and the % Impacted is 10% then the value I want returned is HIGH. I am currently using IF formulas adn trying to nest these without success. I know that there are maybe lookup formulas I can use but this seems a bit beyond me as I do not know how to reference the above table if in a different worksheet but in the same workbook. Thanks Dave |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a formula to return a particular result from a Matrix
Thanks for this. I maybe not have provided enough info. Understand the
formula, Where I need to use it is in workbook I am using contains 200 rows of info of which the 2 main columns that I am populating are "% Customer Impact & Impact Degree". Where the formula you have provided is to be used is when onces these 2 columns are populated, then I need a third column called "Risk Rating" to be automatically populated with the required value as per the Risk Rating Matrix table. So it should look like something likethis: Issue 1 % Customer Impact Impact Degree Risk rating Mapping <2% LOW LOW Communication 10% LOW HIGH Change 2 - 10% Moderate Moderate Thanks "Teethless mama" wrote: Try this: =INDEX(A1:A5,MATCH("Low",A1:A5,0),MATCH("10%",A1: D1,0)) "Dave Ah Ching" wrote: I currently have a Matrix table below which contains the values I want returned when the 2 columns - Impact Degree & % Customer Impacted are populated. % Customer Impact Impact Degree <2% 2%-10% 10% Nil NIL LOW MODERATE Low LOW MODERATE HIGH Moderate MODERATE HIGH VERY HIGH Significant HIGH VERY HIGH EXTREME So for example if the Impact Degree is Low and the % Impacted is 10% then the value I want returned is HIGH. I am currently using IF formulas adn trying to nest these without success. I know that there are maybe lookup formulas I can use but this seems a bit beyond me as I do not know how to reference the above table if in a different worksheet but in the same workbook. Thanks Dave |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a formula to return a particular result from a Matrix
Think there was a typo in the earlier suggestion given
=INDEX(A1:A5,... should be: =INDEX(A1:D5,... Anyway, here's a quick sample which illustrates how the earlier suggestion could be adapted to suit: http://cjoint.com/?lqkevJChBH DaveAhChing_misc.xls In D11: =INDEX($A$2:$D$6,MATCH(C11,$A$2:$A$6,0),MATCH(B11, $A$2:$D$2,0)) Copy down as far as required to populate the "Risk rating" col -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dave Ah Ching" wrote: Thanks for this. I maybe not have provided enough info. Understand the formula, Where I need to use it is in workbook I am using contains 200 rows of info of which the 2 main columns that I am populating are "% Customer Impact & Impact Degree". Where the formula you have provided is to be used is when onces these 2 columns are populated, then I need a third column called "Risk Rating" to be automatically populated with the required value as per the Risk Rating Matrix table. So it should look like something likethis: Issue 1 % Customer Impact Impact Degree Risk rating Mapping <2% LOW LOW Communication 10% LOW HIGH Change 2 - 10% Moderate Moderate Thanks "Teethless mama" wrote: Try this: =INDEX(A1:A5,MATCH("Low",A1:A5,0),MATCH("10%",A1: D1,0)) "Dave Ah Ching" wrote: I currently have a Matrix table below which contains the values I want returned when the 2 columns - Impact Degree & % Customer Impacted are populated. % Customer Impact Impact Degree <2% 2%-10% 10% Nil NIL LOW MODERATE Low LOW MODERATE HIGH Moderate MODERATE HIGH VERY HIGH Significant HIGH VERY HIGH EXTREME So for example if the Impact Degree is Low and the % Impacted is 10% then the value I want returned is HIGH. I am currently using IF formulas adn trying to nest these without success. I know that there are maybe lookup formulas I can use but this seems a bit beyond me as I do not know how to reference the above table if in a different worksheet but in the same workbook. Thanks Dave |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a formula to return a particular result from a Matrix
Thanks Max & Teethless Mama,
Your help has enabled me to get this working. It is now working perfectly. Thanks again Dave "Max" wrote: Think there was a typo in the earlier suggestion given =INDEX(A1:A5,... should be: =INDEX(A1:D5,... Anyway, here's a quick sample which illustrates how the earlier suggestion could be adapted to suit: http://cjoint.com/?lqkevJChBH DaveAhChing_misc.xls In D11: =INDEX($A$2:$D$6,MATCH(C11,$A$2:$A$6,0),MATCH(B11, $A$2:$D$2,0)) Copy down as far as required to populate the "Risk rating" col -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dave Ah Ching" wrote: Thanks for this. I maybe not have provided enough info. Understand the formula, Where I need to use it is in workbook I am using contains 200 rows of info of which the 2 main columns that I am populating are "% Customer Impact & Impact Degree". Where the formula you have provided is to be used is when onces these 2 columns are populated, then I need a third column called "Risk Rating" to be automatically populated with the required value as per the Risk Rating Matrix table. So it should look like something likethis: Issue 1 % Customer Impact Impact Degree Risk rating Mapping <2% LOW LOW Communication 10% LOW HIGH Change 2 - 10% Moderate Moderate Thanks "Teethless mama" wrote: Try this: =INDEX(A1:A5,MATCH("Low",A1:A5,0),MATCH("10%",A1: D1,0)) "Dave Ah Ching" wrote: I currently have a Matrix table below which contains the values I want returned when the 2 columns - Impact Degree & % Customer Impacted are populated. % Customer Impact Impact Degree <2% 2%-10% 10% Nil NIL LOW MODERATE Low LOW MODERATE HIGH Moderate MODERATE HIGH VERY HIGH Significant HIGH VERY HIGH EXTREME So for example if the Impact Degree is Low and the % Impacted is 10% then the value I want returned is HIGH. I am currently using IF formulas adn trying to nest these without success. I know that there are maybe lookup formulas I can use but this seems a bit beyond me as I do not know how to reference the above table if in a different worksheet but in the same workbook. Thanks Dave |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating a formula to return a particular result from a Matrix
Always good to hear that, Dave !
Thanks for feeding back .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dave Ah Ching" wrote in message ... Thanks Max & Teethless Mama, Your help has enabled me to get this working. It is now working perfectly. Thanks again Dave |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula result as real empty/blank cell | Excel Worksheet Functions | |||
| Conditional Formatting Against a Formula - not it's result | Excel Discussion (Misc queries) | |||
| Currency to Text | Excel Worksheet Functions | |||
| I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
| EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
Hybrid Mode
