Results 1 to 13 of 13
Thread: Need Help of Matrix (Excel 2003)

20080812, 21:32 #1
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Need Help of Matrix (Excel 2003)
Hi Excel Experts,
I am back with my queries after a long time.
I am attaching an excel in which i need the formula in Sheet "Master Data" to be fixed on Col J.
There are 3 sheet in the file Matrix, Master Data, STI Matrix. In Master Data in Col H the formula check for ColA, F G and pick the value from Matrix Sheet and give me the out put. I need a similar formula in Col J. that will check the Col E & G and pick the value from STI Matrix and give me the output. I tried using the same formula from Col H by modifiying the same however i am not getting the desired out.
Can you please help me fix the formula.
Thanks
Baiju

20080812, 21:58 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Need Help of Matrix (Excel 2003)
How about in J2:
=INDEX('STI Matrix'!$B$3:$F$17,MATCH($E2,'STI Matrix'!$A$3:$A$17,0),MATCH($G2,'STI Matrix'!$B$2:$F$2,0))
Copy down the column
Steve

20080812, 21:59 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Need Help of Matrix (Excel 2003)
How about
=INDEX('STI Matrix'!$A$3:$F$17,MATCH($E2,'STI Matrix'!$A$3:$A$17,0),MATCH($G2,'STI Matrix'!$A$2:$F$2,0))
in cell J2, then fill down?

20080812, 22:03 #4
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need Help of Matrix (Excel 2003)
How about:
=INDEX('STI Matrix'!$B$3:$F$17,MATCH($E2,'STI Matrix'!$A$3:$A$17,0),MATCH($G2,'STI Matrix'!$B$2:$F$2,0))
or:
=INDEX('STI Matrix'!$A$3:$F$17,MATCH($E2,'STI Matrix'!$A$3:$A$17,0),MATCH($G2,'STI Matrix'!$A$2:$F$2,0))
in cell J2, then fill down?
<img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

20080812, 22:09 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Need Help of Matrix (Excel 2003)
But did you create those formulas yourself...? Steve and I did. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

20080812, 22:13 #6
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need Help of Matrix (Excel 2003)
Yes. <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> using only only 3 keys <img src=/S/devil.gif border=0 alt=devil width=15 height=15>

20080812, 22:30 #7
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Need Help of Matrix (Excel 2003)
Thanks Hans, Steve the formula is working perfect.. and thanks Nathan for consolidating the same :)

20080813, 21:46 #8
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Need Help of Matrix (Excel 2003)
HI Hans/Steve,
I have made some changes to my STI Matrix to Include Min, Default & Max %. Earlier matrix had only the Default % and the formula works perfect.
Is is possible with the new matrix have the Min STI% in Col J and Max % on Col L of the Master Data Sheet.
I have attached the new file with the updated Matrix
Thanks
Baiju

20080813, 22:06 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Need Help of Matrix (Excel 2003)
I'd group the Min values together, the Default values together and the Max values together. That makes it easy to adjust the formulas.
By the way, the grades in the new table don't match those in the old table  instead of 2 you now have 2.1 and 2.2 but column E in Master Data still contains 2, not 2.1 or 2.2.

20080813, 22:22 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Need Help of Matrix (Excel 2003)
I have attached the workbook with the changes I'd make. I've used the old grades with the new percentages.

20080813, 22:52 #11
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Need Help of Matrix (Excel 2003)
Thanks Hans,
Its working perfectly.. You had not changed the ranges for Max STI which i have done.
Thanks for the help
Regards
Baiju

20080813, 23:11 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Need Help of Matrix (Excel 2003)
Sorry, forgot about that at the last moment, but as you have found it's not difficult to modify the formula.

20080813, 23:35 #13
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Need Help of Matrix (Excel 2003)
Hi Hans,
I did figure out that 2.1 & 2.2 Missing. I have fixed that as well
Thanks for the help
Regards
Baiju