OPR after Week Four Events

[DELurker]

Ed,

There appears to be a minor discrepancy between the OPR Results and the CCWM Results page regarding the # of Events column. It looks like the CCWM page shows the number of events completed while the OPR page shows the number the team registered for. Is this an artifact of the calculations?

[Alpha Beta]

Originally Posted by DELurker

Ed,

There appears to be a minor discrepancy between the OPR Results and the CCWM Results page regarding the # of Events column. It looks like the CCWM page shows the number of events completed while the OPR page shows the number the team registered for. Is this an artifact of the calculations?

Toronto West has no teams listed. Perhaps that is why 2056 and 1114 only show 2 events, but are scheduled for a 3rd.

987 has 2 events for OPR, but only 1 for WM. They dropped out of Utah, but are still scheduled to attend Las Vegas later.

[Ed Law]

Originally Posted by DELurker

Ed,

There appears to be a minor discrepancy between the OPR Results and the CCWM Results page regarding the # of Events column. It looks like the CCWM page shows the number of events completed while the OPR page shows the number the team registered for. Is this an artifact of the calculations?

Yes I found an error in the formula for counting number of events. The one on OPR page is correct. The one on CCWM page only counts up to Column CC (PineTree) which is why events from Las Vegas and after do not show up. Last year there were less events that only went up to Column CC. I don't know why I would update the formula on one page and forgot to update it on another. It will be fixed next week.

Also I accidentally ran the macro on Toronto West because the color of the tab (green) for Week 5 is too similar to the color (blue) for Week 4. That is why the list of teams is gone. I manually copied the list of teams from last week's version onto the page CCWM Results and OPR Results but forgot to copy the # of Events column also. Hence all Toronto West teams will show one less in the # of Events column. It does not affect anything else. This will be corrected next week after the event is over. If you cannot wait, you can copy that column from Week 3 (Version 3.0) and paste into Week 4 (Version 4.0). Sorry for the confusion.

[FIMAlumni]

I find it interesting that a good auto (18 pts) and a 50 point climb and dump every match would get a team into the top 10 highest OPRs.

[DELurker]

Originally Posted by Ed Law

I accidentally ran the macro on Toronto West because the color of the tab (green) for Week 5 is too similar to the color (blue) for Week 4. That is why the list of teams is gone. I manually copied the list of teams from last week's version onto the page CCWM Results and OPR Results but forgot to copy the # of Events column also. Hence all Toronto West teams will show one less in the # of Events column. It does not affect anything else. This will be corrected next week after the event is over. If you cannot wait, you can copy that column from Week 3 (Version 3.0) and paste into Week 4 (Version 4.0). Sorry for the confusion.

Sorry, I forgot to include an example for you to reference. By my count, there are 274 teams with a difference between the two pages, so it's more than just the Toronto West teams. I've attached a PDF of the list. Looking back at the week 3 data, the discrepancy is present there, as well.

Of course, the flip side is that the discrepancy affects nothing that I can see...

[Hoover]

Hi Ed,

Forgive me if this has already been asked.

For an upcoming event the spreadsheet is of course mostly empty. Is there a way we could find the current OPR rankings for the teams coming to that event? I'd like to cut and paste that ranking order as a list into something I am working on.

[Ed Law]

Originally Posted by Hoover

Hi Ed,

Forgive me if this has already been asked.

For an upcoming event the spreadsheet is of course mostly empty. Is there a way we could find the current OPR rankings for the teams coming to that event? I'd like to cut and paste that ranking order as a list into something I am working on.

Please read the instructions tab. The answer is there. The information you are looking for is available in one of the tabs. Filters are set up for you so you can select only the teams that is going to a particular regional/district.

[Hoover]

Originally Posted by Ed Law

Please read the instructions tab. The answer is there. The information you are looking for is available in one of the tabs. Filters are set up for you so you can select only the teams that is going to a particular regional/district.

Hi Ed,

Thanks. I am getting the hang of it now. After filtering it gave me exactly the list that I wanted.

Now that I have done the filtering, I know its the Excel way to check all the boxes. I decided to make the list for our entire region of >100 teams. I would have to do this each week, but I am thinking I could have written a macro to do it. I think it may be done even if I have to generate the criteria statement, but if I remember right the criteria can refer to a range of cells but it has been a while. I am using Excel 2007 and so I don't think I can refer to a range anywhere but on the OPR results worksheet itself.

[Hoover]

Originally Posted by Hoover

Now that I have done the filtering, I know its the Excel way to check all the boxes. I decided to make the list for our entire region of >100 teams. I would have to do this each week, but I am thinking I could have written a macro to do it. I think it may be done even if I have to generate the criteria statement, but if I remember right the criteria can refer to a range of cells but it has been a while. I am using Excel 2007 and so I don't think I can refer to a range anywhere but on the OPR results worksheet itself.

I found an easy way to do this that just takes a minute. An index look up can be used. This is what, because of a limitation in Excel 2007, that this has to be done on the same worksheet and cannot be done in another workbook or even on another worksheet within the same workbook.

For an example index statement

=INDEX($E$1:$F$2542,MATCH(A2545,$E$1:$E$2542,0),2)

where A2545 is the cell containing a team number.