|
Matrix question
Hi everyone out there,
I have a spreadsheet (3 columns) and two of the columns have five possible inputs. "Column A" has the following possibilities - Always (A), Likely (B), Possible (C), Unlikely (D) and Rare (E). "Column B" has the following possibilities -Insignificant (1), Minor (2), Moderate (3), Major (4) and Catastrophic (5). I need "Column C" to return a value between 1 and 25 (with each number corresponding to a particular combination of the input from columns A and B). Have tried the IF formula, and it worked up to 7 values. Is there any other way to have up to 25 combinations of outcomes from my two columns, or some other formula that is more appropriate? Thanks in advance for helping out. |
Matrix question
Hi Ardus Petus,
Thanks very much for your reply. Tried that and I get a #VALUE error in C1. Can you explain what it is that your formula is trying to do and I will have another try using my spreadsheet? Any other suggestions? Thanks again Cootha "Ardus Petus" wrote: Enter in C1: =(CODE(A1)-65)*5 + B1 HTH -- AP "Cootha" a écrit dans le message de news: ... Hi everyone out there, I have a spreadsheet (3 columns) and two of the columns have five possible inputs. "Column A" has the following possibilities - Always (A), Likely (B), Possible (C), Unlikely (D) and Rare (E). "Column B" has the following possibilities -Insignificant (1), Minor (2), Moderate (3), Major (4) and Catastrophic (5). I need "Column C" to return a value between 1 and 25 (with each number corresponding to a particular combination of the input from columns A and B). Have tried the IF formula, and it worked up to 7 values. Is there any other way to have up to 25 combinations of outcomes from my two columns, or some other formula that is more appropriate? Thanks in advance for helping out. |
Matrix question
Are you sure A1 contains some value between "A" and "E",
and B1 contains a number between 1 and 5 ? My formula takes the ascii code of the letter in A1 (65 for "A"), substracts 65 (result = 0 for "A) and finally adds the value found in B1 Works perfectly by me. HTH -- AP "Cootha" a écrit dans le message de news: ... Hi Ardus Petus, Thanks very much for your reply. Tried that and I get a #VALUE error in C1. Can you explain what it is that your formula is trying to do and I will have another try using my spreadsheet? Any other suggestions? Thanks again Cootha "Ardus Petus" wrote: Enter in C1: =(CODE(A1)-65)*5 + B1 HTH -- AP "Cootha" a écrit dans le message de news: ... Hi everyone out there, I have a spreadsheet (3 columns) and two of the columns have five possible inputs. "Column A" has the following possibilities - Always (A), Likely (B), Possible (C), Unlikely (D) and Rare (E). "Column B" has the following possibilities -Insignificant (1), Minor (2), Moderate (3), Major (4) and Catastrophic (5). I need "Column C" to return a value between 1 and 25 (with each number corresponding to a particular combination of the input from columns A and B). Have tried the IF formula, and it worked up to 7 values. Is there any other way to have up to 25 combinations of outcomes from my two columns, or some other formula that is more appropriate? Thanks in advance for helping out. |
Matrix question
Oooops:
My formula takes the ascii code of the letter in A1 (65 for "A"), substracts 65 (result = 0 for "A) , multiplies that by 5 (result = 0 for "A", 5 for "B", ..., 20 for "E") and finally adds the value found in B1 "Cootha" a écrit dans le message de news: ... Hi Ardus Petus, Thanks very much for your reply. Tried that and I get a #VALUE error in C1. Can you explain what it is that your formula is trying to do and I will have another try using my spreadsheet? Any other suggestions? Thanks again Cootha "Ardus Petus" wrote: Enter in C1: =(CODE(A1)-65)*5 + B1 HTH -- AP "Cootha" a écrit dans le message de news: ... Hi everyone out there, I have a spreadsheet (3 columns) and two of the columns have five possible inputs. "Column A" has the following possibilities - Always (A), Likely (B), Possible (C), Unlikely (D) and Rare (E). "Column B" has the following possibilities -Insignificant (1), Minor (2), Moderate (3), Major (4) and Catastrophic (5). I need "Column C" to return a value between 1 and 25 (with each number corresponding to a particular combination of the input from columns A and B). Have tried the IF formula, and it worked up to 7 values. Is there any other way to have up to 25 combinations of outcomes from my two columns, or some other formula that is more appropriate? Thanks in advance for helping out. |
Matrix question
Hi Ardus,
Thanks again. Have tried this, and I get a result, but it is not exactly what I was wanting. Maybe I did not make my request clear enough. The matrix layout is as follows - with the abcde and 12345 as they appear in the columns and with the corresponding values of 1-25 as they are reached by using the matrix. 1 2 3 4 5 a 11 16 20 23 25 b 7 12 17 21 24 c 4 8 13 18 22 d 2 5 9 14 19 e 1 3 6 10 15 Hope this is clearer this time. "Ardus Petus" wrote: Oooops: My formula takes the ascii code of the letter in A1 (65 for "A"), substracts 65 (result = 0 for "A) , multiplies that by 5 (result = 0 for "A", 5 for "B", ..., 20 for "E") and finally adds the value found in B1 "Cootha" a écrit dans le message de news: ... Hi Ardus Petus, Thanks very much for your reply. Tried that and I get a #VALUE error in C1. Can you explain what it is that your formula is trying to do and I will have another try using my spreadsheet? Any other suggestions? Thanks again Cootha "Ardus Petus" wrote: Enter in C1: =(CODE(A1)-65)*5 + B1 HTH -- AP "Cootha" a écrit dans le message de news: ... Hi everyone out there, I have a spreadsheet (3 columns) and two of the columns have five possible inputs. "Column A" has the following possibilities - Always (A), Likely (B), Possible (C), Unlikely (D) and Rare (E). "Column B" has the following possibilities -Insignificant (1), Minor (2), Moderate (3), Major (4) and Catastrophic (5). I need "Column C" to return a value between 1 and 25 (with each number corresponding to a particular combination of the input from columns A and B). Have tried the IF formula, and it worked up to 7 values. Is there any other way to have up to 25 combinations of outcomes from my two columns, or some other formula that is more appropriate? Thanks in advance for helping out. |
Matrix question
Assuming your column headers are in G1:K1, your row headers in F2:F6, and
your matrix data in G2:K6, =INDEX(G2:K6,MATCH(A1,F2:F6,0),MATCH(B1,G1:K1,0)) HTH -- AP "Cootha" a écrit dans le message de news: ... Hi Ardus, Thanks again. Have tried this, and I get a result, but it is not exactly what I was wanting. Maybe I did not make my request clear enough. The matrix layout is as follows - with the abcde and 12345 as they appear in the columns and with the corresponding values of 1-25 as they are reached by using the matrix. 1 2 3 4 5 a 11 16 20 23 25 b 7 12 17 21 24 c 4 8 13 18 22 d 2 5 9 14 19 e 1 3 6 10 15 Hope this is clearer this time. "Ardus Petus" wrote: Oooops: My formula takes the ascii code of the letter in A1 (65 for "A"), substracts 65 (result = 0 for "A) , multiplies that by 5 (result = 0 for "A", 5 for "B", ..., 20 for "E") and finally adds the value found in B1 "Cootha" a écrit dans le message de news: ... Hi Ardus Petus, Thanks very much for your reply. Tried that and I get a #VALUE error in C1. Can you explain what it is that your formula is trying to do and I will have another try using my spreadsheet? Any other suggestions? Thanks again Cootha "Ardus Petus" wrote: Enter in C1: =(CODE(A1)-65)*5 + B1 HTH -- AP "Cootha" a écrit dans le message de news: ... Hi everyone out there, I have a spreadsheet (3 columns) and two of the columns have five possible inputs. "Column A" has the following possibilities - Always (A), Likely (B), Possible (C), Unlikely (D) and Rare (E). "Column B" has the following possibilities -Insignificant (1), Minor (2), Moderate (3), Major (4) and Catastrophic (5). I need "Column C" to return a value between 1 and 25 (with each number corresponding to a particular combination of the input from columns A and B). Have tried the IF formula, and it worked up to 7 values. Is there any other way to have up to 25 combinations of outcomes from my two columns, or some other formula that is more appropriate? Thanks in advance for helping out. |
Matrix question
Ardus,
Thankyou so much for this - it actually works and I am now clicking and dragging to put this formula into all the parts of my spreadsheet that I need. Thanks again Cootha "Ardus Petus" wrote: Assuming your column headers are in G1:K1, your row headers in F2:F6, and your matrix data in G2:K6, =INDEX(G2:K6,MATCH(A1,F2:F6,0),MATCH(B1,G1:K1,0)) HTH -- AP "Cootha" a écrit dans le message de news: ... Hi Ardus, Thanks again. Have tried this, and I get a result, but it is not exactly what I was wanting. Maybe I did not make my request clear enough. The matrix layout is as follows - with the abcde and 12345 as they appear in the columns and with the corresponding values of 1-25 as they are reached by using the matrix. 1 2 3 4 5 a 11 16 20 23 25 b 7 12 17 21 24 c 4 8 13 18 22 d 2 5 9 14 19 e 1 3 6 10 15 Hope this is clearer this time. "Ardus Petus" wrote: Oooops: My formula takes the ascii code of the letter in A1 (65 for "A"), substracts 65 (result = 0 for "A) , multiplies that by 5 (result = 0 for "A", 5 for "B", ..., 20 for "E") and finally adds the value found in B1 "Cootha" a écrit dans le message de news: ... Hi Ardus Petus, Thanks very much for your reply. Tried that and I get a #VALUE error in C1. Can you explain what it is that your formula is trying to do and I will have another try using my spreadsheet? Any other suggestions? Thanks again Cootha "Ardus Petus" wrote: Enter in C1: =(CODE(A1)-65)*5 + B1 HTH -- AP "Cootha" a écrit dans le message de news: ... Hi everyone out there, I have a spreadsheet (3 columns) and two of the columns have five possible inputs. "Column A" has the following possibilities - Always (A), Likely (B), Possible (C), Unlikely (D) and Rare (E). "Column B" has the following possibilities -Insignificant (1), Minor (2), Moderate (3), Major (4) and Catastrophic (5). I need "Column C" to return a value between 1 and 25 (with each number corresponding to a particular combination of the input from columns A and B). Have tried the IF formula, and it worked up to 7 values. Is there any other way to have up to 25 combinations of outcomes from my two columns, or some other formula that is more appropriate? Thanks in advance for helping out. |
| All times are GMT +1. The time now is 03:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com