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
0
12/31/2004 4:45:03 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
649 Views

Similar Articles

[PageSpeed] 44

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" <DianeAlsing@discussions.microsoft.com> schrieb im 
Newsbeitrag news:0CE1D386-00BB-42B2-9AE2-7C86F6A8C81A@microsoft.com...
>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 


0
frank.kabel (11126)
12/31/2004 5:15:10 PM
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" <DianeAlsing@discussions.microsoft.com> schrieb im 
> Newsbeitrag news:0CE1D386-00BB-42B2-9AE2-7C86F6A8C81A@microsoft.com...
> >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 
> 
> 
> 
0
12/31/2004 5:45:05 PM
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" <DianeAlsing@discussions.microsoft.com> wrote in message
news:1D737B38-8C2E-409A-A1CB-34FCE56352DD@microsoft.com...
> 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" <DianeAlsing@discussions.microsoft.com> schrieb im
> > Newsbeitrag news:0CE1D386-00BB-42B2-9AE2-7C86F6A8C81A@microsoft.com...
> > >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
> >
> >
> >

0
ragdyer1 (4060)
12/31/2004 6:28:29 PM
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" <DianeAlsing@discussions.microsoft.com> wrote in message
> news:1D737B38-8C2E-409A-A1CB-34FCE56352DD@microsoft.com...
> > 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" <DianeAlsing@discussions.microsoft.com> schrieb im
> > > Newsbeitrag news:0CE1D386-00BB-42B2-9AE2-7C86F6A8C81A@microsoft.com...
> > > >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
> > >
> > >
> > >
> 
> 
0
12/31/2004 6:43:03 PM
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" <DianeAlsing@discussions.microsoft.com> schrieb im 
Newsbeitrag news:EAEDA429-5853-44B8-8B0B-563D0E1EBCB8@microsoft.com...
> 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" <DianeAlsing@discussions.microsoft.com> wrote in message
>> news:1D737B38-8C2E-409A-A1CB-34FCE56352DD@microsoft.com...
>> > 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" <DianeAlsing@discussions.microsoft.com> schrieb im
>> > > Newsbeitrag 
>> > > news:0CE1D386-00BB-42B2-9AE2-7C86F6A8C81A@microsoft.com...
>> > > >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
>> > >
>> > >
>> > >
>>
>> 


0
frank.kabel (11126)
12/31/2004 6:55:20 PM
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" <DianeAlsing@discussions.microsoft.com> schrieb im 
> Newsbeitrag news:EAEDA429-5853-44B8-8B0B-563D0E1EBCB8@microsoft.com...
> > 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" <DianeAlsing@discussions.microsoft.com> wrote in message
> >> news:1D737B38-8C2E-409A-A1CB-34FCE56352DD@microsoft.com...
> >> > 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" <DianeAlsing@discussions.microsoft.com> schrieb im
> >> > > Newsbeitrag 
> >> > > news:0CE1D386-00BB-42B2-9AE2-7C86F6A8C81A@microsoft.com...
> >> > > >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
> >> > >
> >> > >
> >> > >
> >>
> >> 
> 
> 
> 
0
12/31/2004 7:17:02 PM
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" <DianeAlsing@discussions.microsoft.com> schrieb im 
Newsbeitrag news:1A3C2A3F-AF76-4ECA-9878-C52C5AA5AB5B@microsoft.com...
> 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" <DianeAlsing@discussions.microsoft.com> schrieb im
>> Newsbeitrag news:EAEDA429-5853-44B8-8B0B-563D0E1EBCB8@microsoft.com...
>> > 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" <DianeAlsing@discussions.microsoft.com> wrote in 
>> >> message
>> >> news:1D737B38-8C2E-409A-A1CB-34FCE56352DD@microsoft.com...
>> >> > 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" <DianeAlsing@discussions.microsoft.com> schrieb im
>> >> > > Newsbeitrag
>> >> > > news:0CE1D386-00BB-42B2-9AE2-7C86F6A8C81A@microsoft.com...
>> >> > > >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
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>>
>>
>> 


0
frank.kabel (11126)
12/31/2004 7:41:43 PM
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" <DianeAlsing@discussions.microsoft.com> schrieb im 
> Newsbeitrag news:1A3C2A3F-AF76-4ECA-9878-C52C5AA5AB5B@microsoft.com...
> > 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" <DianeAlsing@discussions.microsoft.com> schrieb im
> >> Newsbeitrag news:EAEDA429-5853-44B8-8B0B-563D0E1EBCB8@microsoft.com...
> >> > 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" <DianeAlsing@discussions.microsoft.com> wrote in 
> >> >> message
> >> >> news:1D737B38-8C2E-409A-A1CB-34FCE56352DD@microsoft.com...
> >> >> > 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" <DianeAlsing@discussions.microsoft.com> schrieb im
> >> >> > > Newsbeitrag
> >> >> > > news:0CE1D386-00BB-42B2-9AE2-7C86F6A8C81A@microsoft.com...
> >> >> > > >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
> >> >> > >
> >> >> > >
> >> >> > >
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
12/31/2004 8:21:01 PM
Reply:

Similar Artilces:

Advantage of Matrix vs Standard items
I have an exclusive boutique. Usually an item its available in different sizes and colors. Therefore a Matrix Item seemed to be the way to go. I thought the way to add the items in the matrix (one by one, instead of been able to select several at a time) seemed awkward. But still, I figure that in the end it will be worth the extra work involved, for example when searching (in the whole database of within the matrix) for all the items size 38. On the other hand, after reading the manual (something I should have done in the first place), I found that I can use a Standard Item´s s...

Matrix selection
Hello, I have a problem and I need a solution that I'm not finding out. I need a formula that give me a specific value in a matrix (under the condition of column and row). Example: I have the matrix described below. I need a formula that gives me how many keybords has room 1. Of course that the formula must allow me to change the conditions of the serach. Advisors PC Mouse keybord Room 1 20 30 40 Room 2 30 67 90 Thanks in Advance for your support Regards Marco Hi, Try this =INDEX(A1:D20, MATCH(F1,A1:A20,0), MATCH(G1,A1:D1,0)) Where A1:D20 is the full t...

Top half of covariance matrix
Hi So I've used data analysis to produce a covariance matrix, however it only generates the bottom half of the matrix. Since I want to use the matrix with an index function, I need both halves of the table. Does anyone know an automatic way of doing this, or do I need to do each cell manually? Thanks Aaron Aaron - Select the entire original matrix. Edit > Copy. Select some other cell. Edit > Paste Special, Values, Transpose > OK. Select the entire new transposed matrix. Edit > Copy. Select the entire original matrix. Edit > Paste Special, Skip bl...

Customer Price Matrix new Version 7.0.9
anybody using CPM's new version 7.0.9 having problem with the registration of security key. The problem we are having is whenever we use CPM it asks us registration key. the system is not storing it. please let me know if anybody else is having the same problem and how did they solve it. ...

keeping track of matrix items
Im setting up a golf store. For each club such as a driver we set up a matrix item. Then add each variation to the matrix item (such as loft and shaft). Each item has a unique UPC on the product so most of the time they just scan it in and ring it up. Is it possible to keep track of how many of a particular driver they sold, the sales report will show each item variation (i.e. 4 9 degree reg, 6 10 stiff, etc.) But the customer would like to know how many of Driver X he sold regardless of the variations. Any thoughts? Perhaps Im overlooking something again. YES we have a module RR...

3 tier incentive matrix w/ infinite possibilities
I am trying to set up an incentive matrix for sales reps. If there were only 3 clearly defined tiers or payout %, then I could wrap this up with an IF formula. What I actually need, however, is some way of calculating infinite ranges in between my established tiers. So let's say my sales reps have a goal of selling 100 widgets per month at a cost of $1000 each. My tiers are constructed like this: % of Goal Achieved % of Revenue Share Tier 1 90.00% 0.75% Tier 2 125.00% 3.00% Tier 3 150.00% 4.50% If, for example, the ...

Inventory Matrix for GP
Hello Looking for inventory matrix capability for apparel prospect -- anyone know of a 3rd party? Thank you. Compass Technologies has a matrix product for GP. I haven't seen the latest versions- I think the last one I saw was for Gp v7 Had some nice features then. "Serial Numbers in Smartlist" <SerialNumbersinSmartlist@discussions.microsoft.com> wrote in message news:901E5716-DC60-49EF-9A6E-B1A2C5C4D9B4@microsoft.com... > Hello > > Looking for inventory matrix capability for apparel prospect -- anyone > know > of a 3rd party? > > Thank you. ...

Matrix Import
I am trying to import matriproducts into RMS 2.0. I have imported Dimensions, Dimension Attributes, Item Classes and Item class components. When i enter RMS Store Ops Mgr, i can see everything i have imported as expected. When i open an imported Matrix item it does not assign the items i have imported. Am i missing something? I fear i have not updated an essential table or field that links the items to the matrix. Help!! On Jun 26, 6:56=A0pm, Anjicoot <Anjic...@discussions.microsoft.com> wrote: > I am trying to import matriproducts into RMS 2.0. I have imported Dimensi= ons, >...

Export to excel fit a matrix to one page
Hi , I am creating a scorecard for a client which shows a range of kpi's . I would like to fit this on one page ? The excel sheet fits onto two pages when i export it .The client is quite pedantic about this . I have tried just about everything . Any suggestions will be appreciated .It is a matrix . Regards Malcolm I suggest instead of a matrix that you use a pivot statement in your sql statement. Pivot does the same thing as the matrix as long as you do not have a variable number of columns. I'm assuming that since this is a scorecard that it isn't v...

Re: Dot-Matrix Printer for SOP Picking Tickets ?
Re: Dot-Matrix Printer for SOP Picking Tickets ? Hi, Does anyone know of a dot-matrix printer that works (without issues) for the SOP Picking Ticket "Long Form" ? (This is Microsoftbusinesschecks.com' s #90782 *continuous* form). The MBS "Microsoft Great Plains 8.0 Printer Compatibility" document lists only one "Narrow-Carriage Dot-Matrix" printer for Windows XP and that is the Panasonic KX-P2124, which has not been manufactured for several years. According to Panasonic, their KX-P2124 (as 24 pin printer) was replaced by the current KX-P1131, which em...

how can i make a line chart from matrix(both row and col) of data
Select the data, and create the chart. By default, Excel is likely to use the first column as X values (or category labels) and the first row as series names, if the data is plotted by column. If Excel has trouble with this, clear the top left cell of the range (the intersection of series names and category labels/X values) and try again. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gourav" <Gourav@discussions.microsoft.com> wrote in message news:CEA158CD-95F4-4903-BFBB...

Help with Lot Matrix Items
I am using the lot matrix to sell pop, since the can or bottle upc code could be sold as a single, 6 pack or case. I was able to build the items as the lot matrix and selling them and receiving them seems to work fine, however, my inventory doesn't work. I was hoping it would act as a parent/child. My inventory removes 1 single and doesn't adjust the case qty. Did I do something wrong? Jay ------=_NextPart_0001_47A6C9C8 Content-Type: text/plain Content-Transfer-Encoding: 7bit Good morning Jay, Thank you for posting your Lot Matrix question. Check to make sure that you have a...

matrix
Hi! Is there any way to create larger than 3x3 matrices in vc++.net. The built in Matrix class creates only 3x3. Thanks in advance Regards Hi > Is there any way to create larger than 3x3 matrices in vc++.net. The built > in Matrix class creates only 3x3. i think you should create your own class for such purpose. i.e, you can create martix of matrixes: each element of such matrix should be matrix too. maybe it will help you. Regards, Robert ...

Deleting matrix item parents
After making sold items inactive, would there be any effect if I delete the parent item# they are asscociated with? (After inactivating old items, the parent item numbers remain in the item list...) I want to make sure this does not affect any reports. Nope... The" Parent" of the matrix is just a sort of container. you have to go out of your way to put it into a report. -- -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all responses here in the n...

In Regression Analysis, How do you get a Correlation Matrix to be.
How do you get the Correlation Matrix for your variables to appear on the output screen? tjp32 - > How do you get the Correlation Matrix for your variables to appear on the > output screen? < Tools | Data Analysis | Correlation - Mike www.mikemiddleton.com ...

matrix or lot matrix
I recently integrated RMS at my liquor store. It was suggested that I use a Matrix type for the items such as beer that are sold different ways but use the same UPC code. For instance, six packs and singles have one UPC code and are therefore a Matrix because of the different QTY and Price. The 24 pack case is sold warm and cold and therefore is a matrix because it has the same UPC but sold at a different price depending on the TEMP. The six packs and singles are children of the warm 24 pack as this is how they are made and how the original product is received. I've read in some other...

If/Then Matrix? Can Excel do this?
Hey guys- got a semi-easy one here... I have a range of dates that I need a specific code associated with each year. For example- if the year is 1982, the code to use in this cell is ..9445. If the year is 1993, the code is .9669, and so on. This will run in a macro eventually. How/where do I write this, and, can Excel do something like this? IE- can Excel 'store' a 'matrix' of codes for each year? I hope this is making sense. Bbtw, it doesnt HAVE TO work in a macro- it'd be pretty cool if it could actually work automatically as soon as the date was entered into a specific...

SSRS 2005 matrix using rows as headings
Has anyone ever had to do this? Display a matrix on an SSRS report using the row data as a heading and repeating the column headings for each row? I've gotten pretty close using a matrix containing a rectangle in the Data area which itself contains another matrix; however since the column data needs to repeat, the "row heading" doesn't span accross the entire report. Below is an example - plus some generic SQL to generate the dataset (ListName=Row, OptionName=Column, SelectedYN=Data). Thanks for the help! BLeppert Standard Matrix: Neve...

MATRIX
Anyone have a matrix add-on that does NOT use up all the subdescription fields? We are already using those fields for BRAND, MANUFACTURER ITEM NUMBER, and MANUFACTURER CODE. I cannot believe there is not a specific field for color and size. Just about every item has that! A supplier is not a brand. A supplier is not always the manufacturer. A Manufacturer generally has many brands. The supplier's number is not the manufacturer's number (for comparison purposes). There should be a named field for MANUFACTURER NAME, MANUFACTURER ID, BRAND, COLOR, & SIZE. the ad-hoc fields c...

matrix item greyed out
I have a matrix item pulled up in a purchase order and I am unable to make quantity selections because the matrix is greyed out. I checked the item and it is active. Any suggestions ...I have never seen this one. Thanks. ...

RMS allow adding reorder number at matrix item creation
When creating a new matrix item it would be useful to be able to add in supplier reorder numbers at the component item screen (as you can with reorder point and restock levels by choosing columns), rather then having to go to the Inventory wizard and running Task 140 after the items have been created. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Micr...

SSRS2005, matrix but concat keys for last group level ?
Hello all, Let's start with a matrix report that groups my data by area, status, company and project. Now the problem is that it creates numerous columns and actually : - I don't care about the project sub totals but would still like to know which projects are involved. That is basically I would like to group my data by area, status, company. It shows the total for all projects as I would like. But now I would like to add a 4th level that would show only a single column per company and that would show all the project codes contributing to this company : I tried to...

changing a standard item to a matrix item
Does anyone know if it's possible to change a standard item to a matrix item??? Also, does anyone have any feeback from using any of the matrix add-on programs that are availble?? As of right now we manually add/create each item in a matrix but it sounds like these programs can do it in one swoop. We are using the Matrix Master add-on and we really like the features it has to offer, like adding items to a matrix without having to join in a different sku preamble. It was created by Digital Retailer Solutions (DRS), and likely available through your business partner. -- Jocelyn ...

How do I print an excel spreadsheet including the matrix lines?
File>pagesetup>sheet and select gridlines Regards, Peo Sjoblom "Lexus" <Lexus@discussions.microsoft.com> wrote in message news:4B2AA9D1-9428-489E-BA15-D997E4F7BB10@microsoft.com... > ...

Excel matrix
I need some help with setting up an excel matrix? my boss needs me to setup an excel matrix (dont know what that is) based upon software products that can backup active directory. how so I do this what is a matrix? -- scripttron75 ------------------------------------------------------------------------ scripttron75's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33617 View this thread: http://www.excelforum.com/showthread.php?threadid=533947 I would guess they want a table of Columns being the product providers row being the features and the intersection b...