Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Result from data in two columns....Best function to use?
Greetings:
In Column A I will have text data that will be "Significant", "Moderate" or "Minor" In Column B I will have text data that will be "Imminent", "Likely", "Unlikely" In column C depending on the data in A & B I want to display a result that which will be "High", "Medium" or "Low" Significant, Imminent = High Significant, Likely = High Moderate, Imminent = High Significant, Unlikely = Medium Moderate, Likely = Medium Minor, Imminent = Medium Moderate, Unlikely = Low Minor, Likely = Low Minor, Unlikely = Low What function would be the best to use here. One could make a ponderous IF statement but I'm assuming there is a better way and a better function to use. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Result from data in two columns....Best function to use?
Hi,
Risk assesments, great fum. I'd build a table like the one below out of the way which I've put in J1 - M4 Then this formula in B1 and drag down =INDEX($J$1:$M$4,MATCH(A1,$J$1:$M$1,0),MATCH(B1,$J $1:$J$4,0)) Significant Moderate Minor Imminent High High Medium Likely High Medium Low Unlikely Medium Low Low Mike "tim m" wrote: Greetings: In Column A I will have text data that will be "Significant", "Moderate" or "Minor" In Column B I will have text data that will be "Imminent", "Likely", "Unlikely" In column C depending on the data in A & B I want to display a result that which will be "High", "Medium" or "Low" Significant, Imminent = High Significant, Likely = High Moderate, Imminent = High Significant, Unlikely = Medium Moderate, Likely = Medium Minor, Imminent = Medium Moderate, Unlikely = Low Minor, Likely = Low Minor, Unlikely = Low What function would be the best to use here. One could make a ponderous IF statement but I'm assuming there is a better way and a better function to use. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Result from data in two columns....Best function to use?
tim m wrote:
Greetings: In Column A I will have text data that will be "Significant", "Moderate" or "Minor" In Column B I will have text data that will be "Imminent", "Likely", "Unlikely" In column C depending on the data in A & B I want to display a result that which will be "High", "Medium" or "Low" Significant, Imminent = High Significant, Likely = High Moderate, Imminent = High Significant, Unlikely = Medium Moderate, Likely = Medium Minor, Imminent = Medium Moderate, Unlikely = Low Minor, Likely = Low Minor, Unlikely = Low What function would be the best to use here. One could make a ponderous IF statement but I'm assuming there is a better way and a better function to use. One possible way: =CHOOSE(MATCH(A1,{"Minor","Moderate","Significant" },0) +MATCH(B1,{"Unlikely","Likely","Imminent"},0) ,"Low","Low","Low","Medium","High","High") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Result from data in two columns....Best function to use?
Set up a table exactly like the one shown, with one column for the
combined comments and another one for High, Medium or Low. Suppose this occupies L1:M9, like this: Significant, Imminent High Significant, Likely High Moderate, Imminent High Significant, Unlikely Medium Moderate, Likely Medium Minor, Imminent Medium Moderate, Unlikely Low Minor, Likely Low Minor, Unlikely Low Then in C1 you can have this formula: =VLOOKUP(A1&", "&B1,L$1:M$9,2,0) and copy this down as required. Hope this helps. Pete On Jan 26, 4:49*pm, tim m wrote: Greetings: In Column A I will have text data that will be "Significant", "Moderate" or "Minor" In Column B I will have text data that will be "Imminent", "Likely", "Unlikely" In column C depending on the data in A & B I want to display a result that which will be "High", "Medium" or "Low" Significant, Imminent = High Significant, Likely = High Moderate, Imminent = High Significant, Unlikely = Medium Moderate, Likely = Medium Minor, Imminent = Medium Moderate, Unlikely = Low Minor, Likely = Low Minor, Unlikely = Low What function would be the best to use here. *One could make a ponderous IF statement but I'm assuming there is a better way and a better function to use. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Result from data in two columns....Best function to use?
Thanks for all the responses.
I used Glenns formula and it worked nicely. (Now I have to reverse engineer it to learn why it worked.) I think I will use this formula rather than the other methods because I will be using this on ad hoc type of reports rather than a more permanent report. thus it will be easier to not create a grid each time I have to make use of it. Once again, thanks alot Gents! "Glenn" wrote: tim m wrote: Greetings: In Column A I will have text data that will be "Significant", "Moderate" or "Minor" In Column B I will have text data that will be "Imminent", "Likely", "Unlikely" In column C depending on the data in A & B I want to display a result that which will be "High", "Medium" or "Low" Significant, Imminent = High Significant, Likely = High Moderate, Imminent = High Significant, Unlikely = Medium Moderate, Likely = Medium Minor, Imminent = Medium Moderate, Unlikely = Low Minor, Likely = Low Minor, Unlikely = Low What function would be the best to use here. One could make a ponderous IF statement but I'm assuming there is a better way and a better function to use. One possible way: =CHOOSE(MATCH(A1,{"Minor","Moderate","Significant" },0) +MATCH(B1,{"Unlikely","Likely","Imminent"},0) ,"Low","Low","Low","Medium","High","High") |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Result from data in two columns....Best function to use?
The MATCH() functions rank the data from one to three as follows:
Minor or Unlikely = 1 Moderate or Likely = 2 Significant or Imminent = 3 The CHOOSE() function selects the correct result. Based upon your data, when adding those ranks together a total of either 5 or 6 was High, 4 was Medium and 1 through 3 was Low. tim m wrote: Thanks for all the responses. I used Glenns formula and it worked nicely. (Now I have to reverse engineer it to learn why it worked.) I think I will use this formula rather than the other methods because I will be using this on ad hoc type of reports rather than a more permanent report. thus it will be easier to not create a grid each time I have to make use of it. Once again, thanks alot Gents! "Glenn" wrote: One possible way: =CHOOSE(MATCH(A1,{"Minor","Moderate","Significant" },0) +MATCH(B1,{"Unlikely","Likely","Imminent"},0) ,"Low","Low","Low","Medium","High","High") tim m wrote: Greetings: In Column A I will have text data that will be "Significant", "Moderate" or "Minor" In Column B I will have text data that will be "Imminent", "Likely", "Unlikely" In column C depending on the data in A & B I want to display a result that which will be "High", "Medium" or "Low" Significant, Imminent = High Significant, Likely = High Moderate, Imminent = High Significant, Unlikely = Medium Moderate, Likely = Medium Minor, Imminent = Medium Moderate, Unlikely = Low Minor, Likely = Low Minor, Unlikely = Low What function would be the best to use here. One could make a ponderous IF statement but I'm assuming there is a better way and a better function to use. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sort data that is the result of a sum function | Excel Worksheet Functions | |||
Enter data and display function result in same cell | Excel Discussion (Misc queries) | |||
analysing data from alternate columns using the countif function | Excel Discussion (Misc queries) | |||
MATCH function - 2 columns w/ SIMILAR, not EXACT data | Excel Worksheet Functions | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions |