Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Finding Values in a "Matrix"
I have a matrix I have created in Excel. I am using this for correlations.
I have cells B1 through G1 filled with text names (the same values as A2 through A7). In the matrix fields I calulate their correlations. In a seperate field, say J1, I am calculating the highest correlation value from my matrix. What I would like to do in cells K1 and/or L1 is find the corresponding text names for that high correlation. So for example if cell E1 is Red and cell A2 is Magenta and their correlation is .95, the highest in the matrix, Cell J1 calculates .95. I would like cell(s) K1 and/or L1 to return Red Magenta. Hope this makes some sense - Happy New Year Regards, Diane |
#2
|
|||
|
|||
Hi
try the following to array formulas (entered with cTRL+SHIFT+ENTER): =INDEX(A1:G1,MAX(IF(B2:G7=J1,COLUMN(B2:G7)))) and =INDEX(A1:A7,MAX(IF(B2:G7=J1,ROW(B2:G7)))) -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... I have a matrix I have created in Excel. I am using this for correlations. I have cells B1 through G1 filled with text names (the same values as A2 through A7). In the matrix fields I calulate their correlations. In a seperate field, say J1, I am calculating the highest correlation value from my matrix. What I would like to do in cells K1 and/or L1 is find the corresponding text names for that high correlation. So for example if cell E1 is Red and cell A2 is Magenta and their correlation is .95, the highest in the matrix, Cell J1 calculates .95. I would like cell(s) K1 and/or L1 to return Red Magenta. Hope this makes some sense - Happy New Year Regards, Diane |
#3
|
|||
|
|||
That works great, but my poor description was a little off ;-( More clearly
what I need to do is find a specific value from the matrix that may not be the MAX value, and return the column & row - Thank you! "Frank Kabel" wrote: Hi try the following to array formulas (entered with cTRL+SHIFT+ENTER): =INDEX(A1:G1,MAX(IF(B2:G7=J1,COLUMN(B2:G7)))) and =INDEX(A1:A7,MAX(IF(B2:G7=J1,ROW(B2:G7)))) -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... I have a matrix I have created in Excel. I am using this for correlations. I have cells B1 through G1 filled with text names (the same values as A2 through A7). In the matrix fields I calulate their correlations. In a seperate field, say J1, I am calculating the highest correlation value from my matrix. What I would like to do in cells K1 and/or L1 is find the corresponding text names for that high correlation. So for example if cell E1 is Red and cell A2 is Magenta and their correlation is .95, the highest in the matrix, Cell J1 calculates .95. I would like cell(s) K1 and/or L1 to return Red Magenta. Hope this makes some sense - Happy New Year Regards, Diane |
#4
|
|||
|
|||
Don't let the MAX() in the formula fool you!
Manually enter *ANY* value in J1, and you'll see that you'll still get your labels. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Diane Alsing" wrote in message ... That works great, but my poor description was a little off ;-( More clearly what I need to do is find a specific value from the matrix that may not be the MAX value, and return the column & row - Thank you! "Frank Kabel" wrote: Hi try the following to array formulas (entered with cTRL+SHIFT+ENTER): =INDEX(A1:G1,MAX(IF(B2:G7=J1,COLUMN(B2:G7)))) and =INDEX(A1:A7,MAX(IF(B2:G7=J1,ROW(B2:G7)))) -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... I have a matrix I have created in Excel. I am using this for correlations. I have cells B1 through G1 filled with text names (the same values as A2 through A7). In the matrix fields I calulate their correlations. In a seperate field, say J1, I am calculating the highest correlation value from my matrix. What I would like to do in cells K1 and/or L1 is find the corresponding text names for that high correlation. So for example if cell E1 is Red and cell A2 is Magenta and their correlation is .95, the highest in the matrix, Cell J1 calculates .95. I would like cell(s) K1 and/or L1 to return Red Magenta. Hope this makes some sense - Happy New Year Regards, Diane |
#5
|
|||
|
|||
Yes, you are correct - I guess my problem is that I have duplicate values in
my sheet. Basically I have the same row headings & column settings - So, when I have a Red/Magenta correlation, I also have a Magenta/Red Correlation (same values in the cells). This seems to pose a problem with the formula. "Ragdyer" wrote: Don't let the MAX() in the formula fool you! Manually enter *ANY* value in J1, and you'll see that you'll still get your labels. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Diane Alsing" wrote in message ... That works great, but my poor description was a little off ;-( More clearly what I need to do is find a specific value from the matrix that may not be the MAX value, and return the column & row - Thank you! "Frank Kabel" wrote: Hi try the following to array formulas (entered with cTRL+SHIFT+ENTER): =INDEX(A1:G1,MAX(IF(B2:G7=J1,COLUMN(B2:G7)))) and =INDEX(A1:A7,MAX(IF(B2:G7=J1,ROW(B2:G7)))) -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... I have a matrix I have created in Excel. I am using this for correlations. I have cells B1 through G1 filled with text names (the same values as A2 through A7). In the matrix fields I calulate their correlations. In a seperate field, say J1, I am calculating the highest correlation value from my matrix. What I would like to do in cells K1 and/or L1 is find the corresponding text names for that high correlation. So for example if cell E1 is Red and cell A2 is Magenta and their correlation is .95, the highest in the matrix, Cell J1 calculates .95. I would like cell(s) K1 and/or L1 to return Red Magenta. Hope this makes some sense - Happy New Year Regards, Diane |
#6
|
|||
|
|||
Hi
yes it does. It returns the values for the max column/row in this case. What would be your expected output for such cases? -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... Yes, you are correct - I guess my problem is that I have duplicate values in my sheet. Basically I have the same row headings & column settings - So, when I have a Red/Magenta correlation, I also have a Magenta/Red Correlation (same values in the cells). This seems to pose a problem with the formula. "Ragdyer" wrote: Don't let the MAX() in the formula fool you! Manually enter *ANY* value in J1, and you'll see that you'll still get your labels. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Diane Alsing" wrote in message ... That works great, but my poor description was a little off ;-( More clearly what I need to do is find a specific value from the matrix that may not be the MAX value, and return the column & row - Thank you! "Frank Kabel" wrote: Hi try the following to array formulas (entered with cTRL+SHIFT+ENTER): =INDEX(A1:G1,MAX(IF(B2:G7=J1,COLUMN(B2:G7)))) and =INDEX(A1:A7,MAX(IF(B2:G7=J1,ROW(B2:G7)))) -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... I have a matrix I have created in Excel. I am using this for correlations. I have cells B1 through G1 filled with text names (the same values as A2 through A7). In the matrix fields I calulate their correlations. In a seperate field, say J1, I am calculating the highest correlation value from my matrix. What I would like to do in cells K1 and/or L1 is find the corresponding text names for that high correlation. So for example if cell E1 is Red and cell A2 is Magenta and their correlation is .95, the highest in the matrix, Cell J1 calculates .95. I would like cell(s) K1 and/or L1 to return Red Magenta. Hope this makes some sense - Happy New Year Regards, Diane |
#7
|
|||
|
|||
As they would both be the same, it wouldn't matter to me, but currently it
seems to be unable to see either. Red/Magenta Magenta/Red, for me, means the same thing. As long as if I am looking for their corresponding value, say .95, that I receive one or the other. Spreadsheet example: Green Red Orange Magenta Blue Green 1 .3 .25 .10 .7 Red .3 1 .6 .95 .21 Orange .25 .6 1 .8 .64 Magenta .1 .95 .8 1 .72 Blue .7 .21 .7 .72 1 So, basically if I put in a separate cell, say J1 the value .95, I would like to receive Magenta Red as my return values. If I put .10 in cell J1, I would like to receive Magenta Green. I am ignoring values = 1 , so those duplications would not be included. And I am going out 5 or six decimal places, so duplicates that are not the same column/row would not be an issue. Thanks again. Regards, Diane "Frank Kabel" wrote: Hi yes it does. It returns the values for the max column/row in this case. What would be your expected output for such cases? -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... Yes, you are correct - I guess my problem is that I have duplicate values in my sheet. Basically I have the same row headings & column settings - So, when I have a Red/Magenta correlation, I also have a Magenta/Red Correlation (same values in the cells). This seems to pose a problem with the formula. "Ragdyer" wrote: Don't let the MAX() in the formula fool you! Manually enter *ANY* value in J1, and you'll see that you'll still get your labels. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Diane Alsing" wrote in message ... That works great, but my poor description was a little off ;-( More clearly what I need to do is find a specific value from the matrix that may not be the MAX value, and return the column & row - Thank you! "Frank Kabel" wrote: Hi try the following to array formulas (entered with cTRL+SHIFT+ENTER): =INDEX(A1:G1,MAX(IF(B2:G7=J1,COLUMN(B2:G7)))) and =INDEX(A1:A7,MAX(IF(B2:G7=J1,ROW(B2:G7)))) -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... I have a matrix I have created in Excel. I am using this for correlations. I have cells B1 through G1 filled with text names (the same values as A2 through A7). In the matrix fields I calulate their correlations. In a seperate field, say J1, I am calculating the highest correlation value from my matrix. What I would like to do in cells K1 and/or L1 is find the corresponding text names for that high correlation. So for example if cell E1 is Red and cell A2 is Magenta and their correlation is .95, the highest in the matrix, Cell J1 calculates .95. I would like cell(s) K1 and/or L1 to return Red Magenta. Hope this makes some sense - Happy New Year Regards, Diane |
#8
|
|||
|
|||
Hi
try the following two array formulas in this case: =INDEX(A1:F1,MAX(IF(B2:F6=J1,COLUMN(B2:F6)))) and =INDEX(A1:A6,MATCH(J1,OFFSET(A1:A6,0,MAX(IF(B2:F6= J1,COLUMN(B2:F6)))-1),0)) -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... As they would both be the same, it wouldn't matter to me, but currently it seems to be unable to see either. Red/Magenta Magenta/Red, for me, means the same thing. As long as if I am looking for their corresponding value, say .95, that I receive one or the other. Spreadsheet example: Green Red Orange Magenta Blue Green 1 .3 .25 .10 .7 Red .3 1 .6 .95 .21 Orange .25 .6 1 .8 .64 Magenta .1 .95 .8 1 .72 Blue .7 .21 .7 .72 1 So, basically if I put in a separate cell, say J1 the value .95, I would like to receive Magenta Red as my return values. If I put .10 in cell J1, I would like to receive Magenta Green. I am ignoring values = 1 , so those duplications would not be included. And I am going out 5 or six decimal places, so duplicates that are not the same column/row would not be an issue. Thanks again. Regards, Diane "Frank Kabel" wrote: Hi yes it does. It returns the values for the max column/row in this case. What would be your expected output for such cases? -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... Yes, you are correct - I guess my problem is that I have duplicate values in my sheet. Basically I have the same row headings & column settings - So, when I have a Red/Magenta correlation, I also have a Magenta/Red Correlation (same values in the cells). This seems to pose a problem with the formula. "Ragdyer" wrote: Don't let the MAX() in the formula fool you! Manually enter *ANY* value in J1, and you'll see that you'll still get your labels. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Diane Alsing" wrote in message ... That works great, but my poor description was a little off ;-( More clearly what I need to do is find a specific value from the matrix that may not be the MAX value, and return the column & row - Thank you! "Frank Kabel" wrote: Hi try the following to array formulas (entered with cTRL+SHIFT+ENTER): =INDEX(A1:G1,MAX(IF(B2:G7=J1,COLUMN(B2:G7)))) and =INDEX(A1:A7,MAX(IF(B2:G7=J1,ROW(B2:G7)))) -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... I have a matrix I have created in Excel. I am using this for correlations. I have cells B1 through G1 filled with text names (the same values as A2 through A7). In the matrix fields I calulate their correlations. In a seperate field, say J1, I am calculating the highest correlation value from my matrix. What I would like to do in cells K1 and/or L1 is find the corresponding text names for that high correlation. So for example if cell E1 is Red and cell A2 is Magenta and their correlation is .95, the highest in the matrix, Cell J1 calculates .95. I would like cell(s) K1 and/or L1 to return Red Magenta. Hope this makes some sense - Happy New Year Regards, Diane |
#9
|
|||
|
|||
SUPER-genius - Happy New Year!
Thank you!!!! "Frank Kabel" wrote: Hi try the following two array formulas in this case: =INDEX(A1:F1,MAX(IF(B2:F6=J1,COLUMN(B2:F6)))) and =INDEX(A1:A6,MATCH(J1,OFFSET(A1:A6,0,MAX(IF(B2:F6= J1,COLUMN(B2:F6)))-1),0)) -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... As they would both be the same, it wouldn't matter to me, but currently it seems to be unable to see either. Red/Magenta Magenta/Red, for me, means the same thing. As long as if I am looking for their corresponding value, say .95, that I receive one or the other. Spreadsheet example: Green Red Orange Magenta Blue Green 1 .3 .25 .10 .7 Red .3 1 .6 .95 .21 Orange .25 .6 1 .8 .64 Magenta .1 .95 .8 1 .72 Blue .7 .21 .7 .72 1 So, basically if I put in a separate cell, say J1 the value .95, I would like to receive Magenta Red as my return values. If I put .10 in cell J1, I would like to receive Magenta Green. I am ignoring values = 1 , so those duplications would not be included. And I am going out 5 or six decimal places, so duplicates that are not the same column/row would not be an issue. Thanks again. Regards, Diane "Frank Kabel" wrote: Hi yes it does. It returns the values for the max column/row in this case. What would be your expected output for such cases? -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... Yes, you are correct - I guess my problem is that I have duplicate values in my sheet. Basically I have the same row headings & column settings - So, when I have a Red/Magenta correlation, I also have a Magenta/Red Correlation (same values in the cells). This seems to pose a problem with the formula. "Ragdyer" wrote: Don't let the MAX() in the formula fool you! Manually enter *ANY* value in J1, and you'll see that you'll still get your labels. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Diane Alsing" wrote in message ... That works great, but my poor description was a little off ;-( More clearly what I need to do is find a specific value from the matrix that may not be the MAX value, and return the column & row - Thank you! "Frank Kabel" wrote: Hi try the following to array formulas (entered with cTRL+SHIFT+ENTER): =INDEX(A1:G1,MAX(IF(B2:G7=J1,COLUMN(B2:G7)))) and =INDEX(A1:A7,MAX(IF(B2:G7=J1,ROW(B2:G7)))) -- Regards Frank Kabel Frankfurt, Germany "Diane Alsing" schrieb im Newsbeitrag ... I have a matrix I have created in Excel. I am using this for correlations. I have cells B1 through G1 filled with text names (the same values as A2 through A7). In the matrix fields I calulate their correlations. In a seperate field, say J1, I am calculating the highest correlation value from my matrix. What I would like to do in cells K1 and/or L1 is find the corresponding text names for that high correlation. So for example if cell E1 is Red and cell A2 is Magenta and their correlation is .95, the highest in the matrix, Cell J1 calculates .95. I would like cell(s) K1 and/or L1 to return Red Magenta. Hope this makes some sense - Happy New Year Regards, Diane |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
Multiple X-Axis Values | Charts and Charting in Excel | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Missing values in Excel Line Chart | Charts and Charting in Excel | |||
linked values in a chart | Charts and Charting in Excel |