Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Diane Alsing
 
Posts: n/a
Default 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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Diane Alsing
 
Posts: n/a
Default

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   Report Post  
Ragdyer
 
Posts: n/a
Default

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   Report Post  
Diane Alsing
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Diane Alsing
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Diane Alsing
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Zero values in a log chart Joelle_Smith Charts and Charting in Excel 2 May 8th 23 03:42 AM
Multiple X-Axis Values Rob Herrmann Charts and Charting in Excel 2 January 23rd 05 11:57 PM
Second serie doesn't use X-as values JackRnl Charts and Charting in Excel 1 January 20th 05 02:04 AM
Missing values in Excel Line Chart mw55309 Charts and Charting in Excel 1 January 19th 05 01:10 PM
linked values in a chart Bill H. Charts and Charting in Excel 2 January 2nd 05 06:29 AM


All times are GMT +1. The time now is 09:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"