Counting Assessments at Footy games

[VisionSmart]

Hi all,

With no sport happening, working in the background on part of another Football game.

The required outcome is to count the number of times each Assessor watches a referee on allocated games and present the results in columns

The attached WB is my example

Referring to Games Draw Tab;
10 x Fields operate in each time-slot - Row 6
Teams allocated to play each other - Rows 12, 17, 22, 27, 32, 37, 42, 47.
3 x Referees allocated to each game - Rows - 9:11, 14:16, 19:21, 24:26, 29:31, 34:36, 39:42, 44:46, 49:51
Referees are watched by Team of Assessors who assesses the referees performance on various games.
Assessors are allocated a code and specific color for each identification - - Row 3
Require a formula in G, I, K, M, O, Q, S, U, W, Y down the page to row 95 which will count the number of times a Referee is seen by each Assessor and the total count is placed in the Assessor Column (G, I, K, M, O, Q, S, U, W, Y against the referee row.
e.g. Referee 1 - seen by A1 3 times, seen by A4 1 time, seen by A10 2 times and so on

The attached WB is my example

[ChemistB]

In G5 copied down and pasted and copied across

Please Login or Register  to view this content.

In your example, you state that Referee 01 is seen by A1 3 times, by A4 1 time and A10 2 times. Referee 1 only appears 3 times in your Game_Draw sheet and each time he/she is seen by A1. Am I missing something?

[nicnaws]

Hello! ChemistB's formula works great! However, I was working a bit with this workbook and found that you cannot drag the formula across. You can drag the formula down, but you must do so for every column, as the "Assessor Code" in row 3 for each value must remain absolute. Edit of formula in G5 is highlighted in RED.

Formula in G5 is: =SUMPRODUCT((Games_Draw!$B$9:$T$51=$G$3)*(Games_Draw!$C$9:$U$51=$D5)) --- and drag down to the end of the column
Formula in I5 is: =SUMPRODUCT((Games_Draw!$B$9:$T$51=$I$3)*(Games_Draw!$C$9:$U$51=$D5)) --- and drag down to the end of the column
Formula in K5 is: =SUMPRODUCT((Games_Draw!$B$9:$T$51=$K$3)*(Games_Draw!$C$9:$U$51=$D5)) --- and drag down to the end of the column

... and so on and so forth.
Please correct me if I'm wrong.

[ChemistB]

Let's analyze in G5

Please Login or Register  to view this content.

The Column reference is G$3 will not change when you copy/drag entries down, so no issues there. When you copy it across, you want that reference (the assessor) to change to the same column that you will be copying it to so it is best not to anchor it.

For the Row Reference $D5, the opposite is true. You want it to change as you copy it down as the referee will change and when you copy it across, it won't change anyway because it is in the same row.
The formula works as it should unless I am missing something.

[VisionSmart]

Well that solution works just fine thank you ChemistB!!

However, a further amendment would be appreciated please.

Some forward research, I find there will be 2 x Teams of Assessors in operation on games

Team A1 – A10 and Team B1 – B10

Refer the attached WB iRev C has another Tab - Referees_2 which list the extra Assessor Team.
Have 'hidden' original Referees Tab to prevent confusion.
In the Games Draw Tab, have added extra allocations of Team B throughout various time-slots and fields.
The allocations may not be exactly accurate, but the counts calculations should confirm numbers.
So in cell H8, I tried modifying the formula to include the B Team (Row5) but I get error #N/A

This is the formula.
=SUMPRODUCT((Games_Draw!$B$12:$T$54= H$3:H$5)*(Games_Draw!$C$12:$U$54=$E8))

So would appreciate the applicable formulae to cater for 2 x Teams of Assessors please.

[Bo_Ry]

Please try at H8
=SUMPRODUCT(COUNTIFS(Games_Draw!$B$12:$T$54,H$3:H$5,Games_Draw!$C$12:$U$54,$E8))

[VisionSmart]

That works great thankyou Bo_Ry.

Appreciate the input

If you are interested, I have an outstanding post you may care to investigate for a solution please.

Bit of a challenge.

[VisionSmart]

Hello Bo-Ry,

I believe I anticipated the formulae is correct too soon.
Refer to the Attached WB – Rev D
Some additional comments in Readme Tab
Results in Referees_2 tab do not calculate correctly.
e.g. - Column H totals to 11 (Row 55, when total should be 16. and so on
In addition, I have added another Tab designed to read specific cells and populate the new Assessors_List Tab

[Bo_Ry]

Referees_2 Sheet E52 is Referee 47 formula only count until Referee 47

While in Games_Draw sheet have Referee 01-90

[VisionSmart]

My fault Bo-Ry.
Thank you for picking that up.
I have corrected and now works just fine.
Are you able to assist with he Assessors_List Tab matter?
Formulae is preferred, but VBA as an alternative would be appreciated please.

[VisionSmart]

Hello again Bo_Ry

Have corrected the number of Referees to = 90 and result in Referees_2 Tab presents correctly.
Regards the Assessors List Tab.
Have been trying some formulae in an additional new Tab - They have been suggested by a friend to try.
See attached WB Rev E.
They may assist

[Bo_Ry]

Please try at

C3
=IF(ROWS(C$3:C3)>B$1,"",INDEX(Games_Draw!$B$1:$U$54,AGGREGATE(15,6,ROW(Games_Draw!$B$12:$T$54)/(Games_Draw!$B$12:$T$54=C$2),ROWS(C$3:C3)),E3*2))

D3
=IF(C3="","",INDEX(Games_Draw!$A:$A,INT(AGGREGATE(15,6,ROW(Games_Draw!$B$12:$T$54)/(Games_Draw!$B$12:$T$54=C$2),ROWS(C$3:C3))/5)*5))

E3
=IF(C3="","",MOD(AGGREGATE(15,6,ROW(Games_Draw!$B$12:$T$54)*1000+COLUMN(Games_Draw!$B$12:$T$54)/(Games_Draw!$B$12:$T$54=C$2),ROWS(C$3:C3)),1000)/2)

C16
=COUNTIF(C3:C15,"*?")

[VisionSmart]

Getting closer Bo_Ry

I loaded your formulae into the attached WB - Rev F
The A Team Assessors seems to calculate and present OK
But the B Team does not - even though the formulae is in place.

Cannot figure out what I am doing wrong.

[Bo_Ry]

You have so many circular references in your sheet
go to File > Options > Formulas > uncheck Enable Iterrative calculation
then check every circular cells that show on the lower left of the windows.

Circular.png

[VisionSmart]

Hi again Bo_Ry

Have cleared the Circular references
Refer attached WorkBook Rev G
Have placed the formulae you provided in Columns C, D, E, but no result presents
What do I nee to do to fix please?

[Bo_Ry]

B3
=COUNTIF(Games_Draw!$B$12:$U$54,C2)

C3
=IF(E3="","",INDEX(Games_Draw!$A:$A,INT(AGGREGATE(15,6,ROW(Games_Draw!$B$12:$T$54)/(Games_Draw!$B$12:$T$54=C$2),ROWS(D$3:D3))/5)*5))

D3
=IF(E3="","",INDEX(Games_Draw!$A:$A,INT(AGGREGATE(15,6,ROW(Games_Draw!$B$12:$T$54)/(Games_Draw!$B$12:$T$54=C$2),ROWS(D$3:D3))/5)*5))

E3 =IF(ROWS(E$3:E3)>B$1,"",MOD(AGGREGATE(15,6,ROW(Games_Draw!$B$12:$T$54)*1000+COLUMN(Games_Draw!$B$12:$T$54)/(Games_Draw!$B$12:$T$54=C$2),ROWS(E$3:E3)),1000)/2)

[VisionSmart]

Just looked at your last response so will see how you did this

[VisionSmart]

Hello Bo-Ry

Some further research indicates I would like to try this Workbook at a major Footy event coming up soon.

The attached WorkBook Rev G-12 has been ‘expanded’ to try to cater for the envisaged event.

Games_Draw Tab has been expanded to 5 x days.

I have not been able to figure out how your magic Array Formulae works.
So would appreciate appropriate formulae for each day separately please.

Thank you

[JeteMc]

In the attached copy of the file Bo_Ry's formulas are modified to fill all of day 1 and the sections of day 2 corresponding to assessors A1 and B1.
Basically the range of cells will need to be changed for each day and for team A and team B within the day as seen in the difference between day 1 and day 2.
I don't have time to go into detail at the moment, however let us know if you have problems applying the formulas to the other days.

[VisionSmart]

JetMc, thankyou for formulae fix. Getting closer to a resolution.

Further to earlier discussions, and following a meet with the Organizers, the up-coming major Footy Event, a Schedule has been decided as follows:

5 x Days
10 x Fields
15 x Rounds each day
25 minute games – 5 minute break between games – so start of each round 30 mins apart

I have now created the expanded Draw to cater for 15 x time-slots each day in attached WB Rev G-12B.

This WB has specific spaces (blank rows) between days in the Games_Draw tab and need to stay as I have set please.
So my task with two other people is to set up the Referees appointments for the whole event.
I plan to use this WB as the template. Referee Names and Teams will be filled in later.

In setting up all the formulae, I am experiencing errors as can be seen in The Assessors Tab.
The Time Heading column across D, I, N, S, X, AC, AH, AM, AR, AW don’t present correctly with various incorrect data. Such as Field>>> in the Time Column not a time and in some cells Referee Day xx; while others have 12:00 AM instead of the correct time

Would appreciate advices / assistance please.

[JeteMc]

The following seems to work:

Please Login or Register  to view this content.

Note that the formula is array entered
1. Select cell D4 on the Assessors sheet
2. Paste the formula into the formula bar
3. Simultaneously press the Ctrl, Shift and Enter keys
4. Drag the fill handle down to cell D23
5. Choose Without Formatting from the Auto Fill Options
6. While D4:D23 are still selected press the Ctrl + c keys to copy
7. Select cells I4 and press the Ctrl + v keys to paste
8. Continue pasting into the cell in the fourth row of columns N, S, X, AC, AH, AM, AR and AW before pressing the Esc key
Let us know if you have any questions.

EDIT: The formula will work with whole column Indexing i.e.

Please Login or Register  to view this content.

[VisionSmart]

Hello again JetMc

In the attached WB Rev G-12C, with your latest formulae in Time column
Assessor Tab, DAY 01 & DAY 02, I have used the first formulae.
I note the range in the first part of the formulae is shows =IF(E4="","",INDEX(Games_Draw!$A$1:$A$81.
Whilst I have left A Team to that range, I have changed the range in B Team to $A$1:$A$85.
Assessor Tab, DAY 03, 04 & 05, I have used the 2nd formulae (indexing)
Errors present in the Time Column

What am I doing wrong please?

[JeteMc]

I had not looked at the new set up on the Games_Draw sheet closely enough.
A formula that seems to work is: =IF(E4="","",INDEX(Games_Draw!$A:$A,INT(AGGREGATE(15,6,(ROW(Games_Draw!$B$13:$T$85)-RIGHT($A$2,2))/(Games_Draw!$B$13:$T$85=C$3),ROWS(D$4:D4))/5)*5+RIGHT($A$2,2)))
Note that the RIGHT($A$2,2) sections will need to be modified for each day, so for instance for day 2 they will need to read: RIGHT($A$50,2)
The formula is modeled in the cells highlighted with light blue fill.

[VisionSmart]

Well that last formula worked great thank you JeteMc and help from Bo-Ry.
Some of the expressions used I have never seen, so I need to go t EXCEL school?

If you are interested, I have an outstanding thread you may be able to assist with please.

See #13 and attachment WB.
Thank you again, and stay safe and healthy.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[VisionSmart]

Hello again JetMc and Bo_Ry

Have removed the SOLVED flag as would appreciate formulae change please

Further to the solution to as above, there has been a change to the event due to the limited time-frame for remainder of the year.

Because it will probably be the only major event for at least nine months, several smaller footy tournaments are now to be combined into this one large event. A suitable venue has been secured which will accommodate.

5 x days
20 x Fields
30 minutes round.
3 x Referees on each game
40 x Assessors – 2 x Teams of 20 – A and B
Many games will have 3 x Assessors on the same game – see Games Draw Tab.

In the attached WB Ver G-12D (VisionSmart), has been expanded to cater for the growth,

In short, instead of one (1) Assessor being allocated to assess ne referee per game, they are to be allocated to assess all 3 X referees; and there will be many games where three (3) assessors will be allocated to the same game
Example A1, B1 and A5 could be on same game
So the formulae in Assessors Tab requires modification please.


Referring to WB Ver G-12D, adjacent to left side of each game in each field, now has 3 x columns instead of one ( see Col B, C D., F, G, H etc.

The Assessors Code is to be located in these 3 x columns against all three Referees on the same game.
Refer coloured examples in Games_Draw Tab..

I have created an Expected Outcome Tab which tries to explain what I am hoping for. Some boxed Text explanations appear in this Tab – DAY 01.

Same data should appear in A1, A2, A3 to match Games_Draw

Hope you can assist please

[JeteMc]

These modifications seem to work:
1. For field# (cell E4 on the Expected Outcome sheet):

Please Login or Register  to view this content.

2. For referee name (cell C4):

Please Login or Register  to view this content.

Note that these both work as regular formulas (just press the Enter key to activate)
Note that no modifications are needed for the time formula.
Let us know if you have any questions.

[VisionSmart]

Thank you again PeteMc.
That formula works just fine; and enables good ‘checking’ of allocations.
Now another issue has arisen.
This up-coming is the biggest event of the year – about 5,000 players, 400 referees, 200 officials, 20 fields, WE use Golf carts and Segways to get around.
Following a further meeting with our Assessors Co-ordination team, the matter of providing some Feedback to Referees as to their performance was discussed.
So the best way to provide some consistent feedback is to provide each referee with some verbal feedback, supported by a a written Feedback Ticket at the end of each game.
This ticket would be prepared by the Assessor during the game ready to hand over at end of game to assist with coaching and improvement areas.

So a separate Feedback Ticket needs to be prepared for each referee for each game. That’s a lot of tickets.
So a template has been prepared which provides up to 9 x tickets on A4 landscape pages.

The attached WB Rev G-12F has an additional Tab Feedback to Referee Tab plus an outcome Tab which contains some example data. Some of the previous Tabs are hidden.
All the data-fields displayed in Grey, and are all sourced from the Panel Draw Tab.

I envisage the Feedback Ticket would generate as the Panel Draw is created progressively.
These Tickets are initially printed to PDF, so last minute changes can be made, then to hard copy at the event as required – usually 1st half of day print, then another lot for 2nd half of day.

I am not sure how this can work. Is it possible to use one template and produce Tickets from each game or will I need to have a separate template prepared for every game?
Suggestions, comments would be appreciated pleas.

[JeteMc]

I feel as if this needs a wider audience than it will get in this thread, as there are probably only a few people still monitoring due to the number of posts and I have a very limited knowledge of anything having to do with producing printouts.
I suggest that you mark this thread as 'Solved' using the thread tools menu above your first post and then ask the question posed in post #28 as a starter for a new thread.
I feel that a new thread will draw the attention of more contributors. I would suggest posting in the Excel General forum. Also I would suggest using a title that indicates you are linking data to a template to be printed.
Best of luck and I hope that you have a blessed day.

[VisionSmart]

Thank you PeteMc.

I will SOLVED this one and start a new thread.

[VisionSmart]

Have reset this thread to UNSOLVED because of the following additional requirements

Thanks to JetMc and Bo_Ry, I recently used the formulae in a test process for a forthcoming major sporting event, and it worked well. –except for the need to provide some additional information.
These being for Date and Division
The data is in the Panel Draw Tab but require formula to populate newly added cells (2 x columns) in Expected Outcome (2) Tab
The attached WBook Rev G -12H WorkBook has the extra colored columns with some manually entered data as expected outcome in the Expected Outcome (2) Tab.
The ReadMe tab in the WBook provides source locations for the data.
Would appreciate assistance by way of Formulae in the two columns headed Time and Division please.

[JeteMc]

Try the following:
1. For date in F4 and down:

Please Login or Register  to view this content.

2. For division in G4 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[VisionSmart]

Thank you for the speedy response JetMc

I met with other Assessors members today and we did some forecasting / modeling based on what we understand will be the large numbers of teams and officials at the event.

So, additional changes are now needed to accommodate to size of actual event.

Changes have been made as follows in template WBook Ver 12H1 (attached) are:
Refer Expected Outcome (2) Tab:
• Need a Heading row at the top for Print Macro Boxes
• See Row `1 & Row 52 now inserted.
• Allowance for up to 45 x Referee names per Assessor per Day
See Expected Outcome (2) Tab – example range B3 : G50
• Rearranged Assessor sheets so Day 01 thru Day 03 are now across the page instead of down the screen - will create print Macros as applicable.
This will enable the first 3 days to be printed to A3 hard copy.
• Have created Assessor A1 and A2 layouts, as I understand the same formulae will work with all other columns yet to be created.
I assume this will change some formulae somewhat?

When I use your latest formulae in the additional columns (Time and Division) , I get various errors as per the range B5 : G49

Because the discussion take up lots of space on the thread, complete description is in the ReadMe Tab of the attached WBook.

Would appreciate the attached WorkBook and returned with corrections please.

Stay safe and healthy.

[JeteMc]

The issue with the formulas is that they needed to reference columns B:CC and some did not. For example if you look back at the field# formula in post #27 it only references B:AL as that was the width of the Panel Draw data at that time. Now the data extends to column CC.
Note that assessor A1 is scheduled to be on both field #10 and #16 at 2:00 PM on day 1.
Let us know if you have any questions.

[VisionSmart]

Your formulae works great in the WBook received back thankyou JetMc.
However, when I paste the same formulae into my live workbook (same columns, live rows data in same cells), I keep getting #NUM! error in the Date column. Have been going through all the formulae and I have not been able to locate the error. = mainly due to my limited understanding of how VBA functions work.

Because Formulae being used would take up lots of space in a Post, they are detailed in the Readme Tab of attached WBook – Ver 12H2

Would appreciate your suggestion where I look for a change needed to get the Date field to work please?

[JeteMc]

When I opened the file I saw dates in column F on the Expected Outcome sheet, so I am not sure what the issue is, however it seems that it may be easier to simply make a lookup table for Days and Dates and then populate column F from that table.
The lookup table is in XFC1:XFD5 of the Expected Outcome sheet.
The formula that populates column F is: =IF(E5="","",INDEX(XFD$1:XFD$5,MATCH(E$3,XFC$1:XFC$5,0)))
Let us know if you have any questions.

[VisionSmart]

That is a very clever work around JetMc.

I realize now I did not explain very well in my last post the error I am getting

The WBook Ver 12H2 attached has screen picture and explanations is in the Readme Tab.
Your formula is in Column F and works fine in this WBook, but as explained, when I use that formula in my live WorkBook, I get the dreaded #NUM! error.

[JeteMc]

The reason for the error may be that the values in columns I and R are preceded by an apostrophe making them text. You could either retype as dates or make a table like the one talked about in post #36 and then reference that table using a formula like the one used in post #36.
In the attached file I retyped I9 and R9 on the Panel Draw sheet as actual dates, note that there is no apostrophe.
Let us know if you have any questions.

[VisionSmart]

Thankyou to Bo-Ry and PeteMc for assistance to this thread
I have been away with work on other matters so I am just getting back onto this one.

I am at the stage now of trying to complete the formulae allocating Assessors to Referees over the 4 x days.
With my limited knowledge of very technical functions, I cannot get the right formulae for Day 02, 03, 04

The attached WorkBook has examples of my attempts with appropriate explanations of what outcome should be for each day.
Would appreciate the correct formula placed in the attached Wbook and returned.

Thank you

[JeteMc]

I believe that Days 2, 3 and 4 are now displaying correctly.
Let us know if you have any questions.

[VisionSmart]

Works great thankyou JetMc.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.