Thread
:
Matrix question
View Single Post
#
8
Posted to microsoft.public.excel.worksheet.functions
Cootha
Posts: n/a
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.
Reply With Quote