searching an array

**madmanmac** · 08-11-2008, 05:33 PM

Hello!

I need help with the following problem...........

I have generated a couple long lists of baseball data and want manipulate it. I am trying to get a list of pitchers who accounted for at least 20% of their team's win in a given season. I've set up the following short example to try and explain what I need Excel to do for me.

A B C D E F G

1 Player Team Year Wins Team Year Wins
2 A Reds 2002 20 Cubs 2004 94
3 B Dodgers 2003 21 Giants 2007 77
4 C Mets 2004 22 Reds 2002 69
5 D Yankees 2005 23 Dodgers 2006 90
6 E Cubs 2006 24 Yankees 2005 99
7 F Giants 2007 25 Mets 2004 88

I need a way to check the data in column B and column C from a particular row and note when it matches EXACTLY the first two colums from an array (columns E to G). Then I want to know if the number of wins in column D of that line is >= 20% of the corresponding line from the array.

Hope this makes sense.

Thanks.

**protonLeah** · 08-11-2008, 11:14 PM

Can you post a copy of your workbook, or at least a sample of the data array that includes at least one example of the exact matchwith the data sample in your original post?

**rylo** · 08-12-2008, 01:19 AM

Hi

Assuming your example data is in the range A1:G7 then

I2: =IF(SUMPRODUCT(--($E$2:$E$7=B2),--($F$2:$F$7=C2)),"MATCH","")
J2: =IF(AND(I2="MATCH",D2/INDEX(G:G,SUMPRODUCT(--($E$2:$E$7=B2),--($F$2:$F$7=C2),ROW($C$2:$C$7)))>=0.2),"YES","")

Copy from I2:J2 down to I7.

It does assume that there will be only 1 exact match to be found....

rylo

**madmanmac** · 08-12-2008, 02:02 PM

Originally Posted by rylo

Hi

Assuming your example data is in the range A1:G7 then

I2: =IF(SUMPRODUCT(--($E$2:$E$7=B2),--($F$2:$F$7=C2)),"MATCH","")
J2: =IF(AND(I2="MATCH",D2/INDEX(G:G,SUMPRODUCT(--($E$2:$E$7=B2),--($F$2:$F$7=C2),ROW($C$2:$C$7)))>=0.2),"YES","")

Copy from I2:J2 down to I7.

It does assume that there will be only 1 exact match to be found....

rylo

I modified the range and applied this to my entire data set and it seems to work --- although I don't understand it yet..............

Thanks for your help!

**rylo** · 08-12-2008, 07:36 PM

Hi

Keep plugging with the debug of the formula and see how each component works. Don't hesitate to ask questions if you get stuck.

rylo

**madmanmac** · 08-20-2008, 11:42 AM

Originally Posted by rylo

Hi

Keep plugging with the debug of the formula and see how each component works. Don't hesitate to ask questions if you get stuck.

rylo

I think that I now undersand how and why it works but why do you need the dashes "--" in the SUMPRODUCT command?

**rylo** · 08-20-2008, 07:01 PM

Hi

They are used to force the conversion of the boolean result to a number. False is 0, and true is any other number, but is effectively 1.

So the first - converts to a negative number, and the second one converts it back to positive.

rylo

searching an array

LinkBack

Thread Tools

Rate This Thread

Display

searching an array

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions