Complicated number searching

[Handyy]

Hi,

I have one statistical data which i would want to extract from a hockey shift chart file. Basicly I want to search if a player has been on ice on a specific second in a game, to match out which players were on the ice at the same time to make a chart.

Here is how it looks in a nutshell:

Please Login or Register  to view this content.

Column A: Player number
Column B: Second number when his shift started
Column C: Second number when his shift ended
Column D: Type of shift (EV=Even Strenght, PP=Power Play)

Let's say I want to search second number 340 from a player #17, giving the D-column value (in this case EV) as the result. How do I search this because I have variables in player number, and I have to look for a number between from B and C -columns? Is it even possible?

[Bryan Hessey]

Hi,

I have one statistical data which i would want to extract from a hockey shift chart file. Basicly I want to search if a player has been on ice on a specific second in a game, to match out which players were on the ice at the same time to make a chart.

Here is how it looks in a nutshell:

Please Login or Register  to view this content.

Column A: Player number
Column B: Second number when his shift started
Column C: Second number when his shift ended
Column D: Type of shift (EV=Even Strenght, PP=Power Play)

Let's say I want to search second number 340 from a player #17, giving the D-column value (in this case EV) as the result. How do I search this because I have variables in player number, and I have to look for a number between from B and C -columns? Is it even possible?

Hi,

Because you have no chance of multiple occurrances, you could use the SumProduct as a search, although this would not be generally recommended due the the possible error rate.

=OFFSET(D3,-3+SUMPRODUCT(--(A$3:A$7=F2)*(--(B$3:B$7<=G$2)*(--(C$3:C$7>=G$2)*(ROW(D$3:D$7))))),0)

should give the item required if one exists, but, for the errors,

=IF(ISERROR(OFFSET(D3,-3+SUMPRODUCT(--(A$3:A$7=F2)*(--(B$3:B$7<=G$2)*(--(C$3:C$7>=G$2)*(ROW(D$3:D$7))))),0)),"",OFFSET(D3,-3+SUMPRODUCT(--(A$3:A$7=F2)*(--(B$3:B$7<=G$2)*(--(C$3:C$7>=G$2)*(ROW(D$3:D$7))))),0))

might help

note, F2 = 17, G2 = seconds

Hopefully there is a better solution
---

[duane]

with your data in cells a2:d6, the player # in a12 and time in b12

=OFFSET(D1,MATCH(1,(A2:A6=A12)*(B2:B6<=B12)*(C2:C6>=B12),0),0)

enter with control+shift+enter

if there is not a time which meets the criteria you will get a N/A - if(isna( the above),"does not occur", the above) will handle that.

[Teethless mama]

Hi,

I have one statistical data which i would want to extract from a hockey shift chart file. Basicly I want to search if a player has been on ice on a specific second in a game, to match out which players were on the ice at the same time to make a chart.

Here is how it looks in a nutshell:

Please Login or Register  to view this content.

Column A: Player number
Column B: Second number when his shift started
Column C: Second number when his shift ended
Column D: Type of shift (EV=Even Strenght, PP=Power Play)

Let's say I want to search second number 340 from a player #17, giving the D-column value (in this case EV) as the result. How do I search this because I have variables in player number, and I have to look for a number between from B and C -columns? Is it even possible?

Try this:
=INDEX(D1:D5,MATCH(1,(A1:A5=17)*(B1:B5<=340)*(C1:C5>=340),0))

ctrl+shift+enter (not just enter)

[Maistrye]

Depending on how you're using it, you could use DGET. If you're doing it one by one, this will work fine -- if you're doing a lot of these queries at once, it will be less useful.

With F1: A, F2: 17, G1: B, G2: ="<="&E1, H1: C, H2: =">="&E1, E1=340

=IF(ISERROR(DGET(A1:D6,"D",F1:H2)),"",DGET(A1:D6,"D",F1:H2))

Scott

[Maistrye]

Or

=OFFSET(D1,SUM(IF(A2:A6=F1,IF(B2:B6<=G1,IF(C2:C6>=G1,ROW(C2:C6))))),0)
Entered with CTRL+SHIFT+ENTER.

Where F1, G1 are the player # and the # of seconds, respectively.

The only catch is that if it is not found, the value in D1 is returned, so it might be best if it were blank. :-P

Scott