Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
geting a if/and number off of two list cells
I need to figure out a formula (or a set of formulas) to get a number off of
a combination of two different drop down lists. My two lists are like this: A - Frequent B - Probable C - Occasional D - Remote E - Improbable and: I - Catastrophic II - Critical III - Marginal IV - Negligible I need to combine it so that each different combination gives you a different risk number. Like A and I gives you 20, B and I = 19, A and IV = 12, B and IV = 8 and so on. I know how to do the formula when it is two static cells =IF(AND(B4="A - Frequent", C4="I - Catastrophic"), 20, 0) And I have seen examples of how two use a VLOOKUP with a list, which seems like what I need to use, but I don't know how to get it to work when you need to combine two different lists to get one result. Can anyone help me with this? I've seen all the different formulas that seem to be what I need, but I'm at a loss to figure out how to put them together. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
geting a if/and number off of two list cells
Hmm,
Risk assesments. If yours are done in this way then one way would be to produce a list of all possible combinations and then assign the numeric value to each. Put your frequency list in Column A and your severity list in Column B both starting in row 1. Right click your sheet tab, view code and paste this code in and run it There mustn't be any other data in columns A or B Sub stance_abuse() lastrowa = Cells(Rows.Count, "A").End(xlUp).Row lastrowb = Cells(Rows.Count, "B").End(xlUp).Row For x = 1 To lastrowa For y = 1 To lastrowb Cells(x + z, 3).Value = Cells(x, 1).Value & Cells(y, 2).Value If y < lastrowb Then z = z + 1 Next Next End Sub You will have a complete list of all possible combinations in column C. Assign numeric values to each in column D and copy the table to somewhere out of the way. A good idea would be to give it a name such as RiskTable You can then do your lookup like this =VLOOKUP(B4&C4,G1:H20,2,FALSE) or if you name the range like this =VLOOKUP(B4&C4,RiskTable,2,FALSE) Mike "nbach" wrote: I need to figure out a formula (or a set of formulas) to get a number off of a combination of two different drop down lists. My two lists are like this: A - Frequent B - Probable C - Occasional D - Remote E - Improbable and: I - Catastrophic II - Critical III - Marginal IV - Negligible I need to combine it so that each different combination gives you a different risk number. Like A and I gives you 20, B and I = 19, A and IV = 12, B and IV = 8 and so on. I know how to do the formula when it is two static cells =IF(AND(B4="A - Frequent", C4="I - Catastrophic"), 20, 0) And I have seen examples of how two use a VLOOKUP with a list, which seems like what I need to use, but I don't know how to get it to work when you need to combine two different lists to get one result. Can anyone help me with this? I've seen all the different formulas that seem to be what I need, but I'm at a loss to figure out how to put them together. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
geting a if/and number off of two list cells
I think there is something wrong with your description of how the points are
attributed to the combination of letters and Roman numerals. You said AI=20 and BI=19...next higher letter for the same Roman numeral decreased the value by one However, you also said AIV=12 and BIV=8... next higher letter for the same Roman numeral decreased the value by four. Can you simply list the letter-Roman numeral combinations (there are only 20 of them) with the values you want assigned to them? Also, are the letters and Roman numerals in separate columns or are they a text string (like CIII for example)? -- Rick (MVP - Excel) "nbach" wrote in message ... I need to figure out a formula (or a set of formulas) to get a number off of a combination of two different drop down lists. My two lists are like this: A - Frequent B - Probable C - Occasional D - Remote E - Improbable and: I - Catastrophic II - Critical III - Marginal IV - Negligible I need to combine it so that each different combination gives you a different risk number. Like A and I gives you 20, B and I = 19, A and IV = 12, B and IV = 8 and so on. I know how to do the formula when it is two static cells =IF(AND(B4="A - Frequent", C4="I - Catastrophic"), 20, 0) And I have seen examples of how two use a VLOOKUP with a list, which seems like what I need to use, but I don't know how to get it to work when you need to combine two different lists to get one result. Can anyone help me with this? I've seen all the different formulas that seem to be what I need, but I'm at a loss to figure out how to put them together. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I add down a list a specified number of cells? | Excel Worksheet Functions | |||
How do I number the cells when I am making a list? | Excel Worksheet Functions | |||
geting the smaller number in a line?? | Excel Discussion (Misc queries) | |||
Question that should be easy but is geting on my nerves :) | Excel Discussion (Misc queries) | |||
geting hiperlink wiyh vlookup | Excel Worksheet Functions |