Yes if you are searching for an easy way of calculating scores of your BNI Chapter's power team games then you are at the right place.
As the leadership team of the chapter or as the Power Team Coordinator, you might be really excited to devise games in order to increase the engagement and participation of the members of your chapter. Definitely, BNI Games have always proven to achieve some great results in various chapters across various regions.
We usually have the games focussed on the core parameters of BNI namely:
If you use these parameters, then it becomes extremely simple to calculate scores with a simple 2 steps process:
Export Summary PALMS report of the last week whose score is to be calculated. Please export it without headers.
Copy and Paste Values from the downloaded report in the BNI Games Scorecard file provided as an attachment with this blog.
Bingo, just 2 steps and you are ready with the last week's score.
Now add the last week's score in the Cumulative scoring sheet and get the cumulative score for more than 1 week.
I have kept the provision of scoring in this sheet for up to 200 members and 8 power teams.
If you have more members and/or more power teams, then you will need to do some changes to the sheet.
I am explaining the logic of the formula as well so that if you want to modify anything then you can do the same for your chapter's requirement.
The sheet named PT Wise Numbering demands manual entry but this is only a ONE TIME process.
It is important to write the correct number beside the name of the member corresponding to the right power team. This is the root of the entire scorecard.
You will notice that we have used this data in the sheet named Report to map the number for the respective power team and then sorting has been done to separate the members team-wise.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
This is a very simple formula in Microsoft Excel to find an answer against a lookup value. The first use of the same is done to map the right power team against each member and then sort them team-wise.
If you will open the above file, you will find a sheet named Copy and Paste from PALMS.
You just have to copy and paste the values from the Summary PALMS report.
As the Summary PALMS report has First Name and Last Name separate, it is required to concatenate them. So I have used the below formula for the same.
=CONCATENATE(First_Name, " ", Last_Name)
You have to add the cell having the First Name and Last Name in the above formula. You can see the same in the attached file with this blog.
The sheet named Report is developed such that it will check for the FULL NAME of the member in the sheet named Copy Paste from PALMS and map for the values against that member's name.
The sheet named Report is sorted power team-wise. So it is necessary that you do not change the sequence of the members in that sheet.
I have kept space for the addition of 5 more members in each of the power team. If you want to use a lesser number of members then delete the values from the PALMS part only in the REPORT sheet. Let the extra rows of members be as it is.
You will get the scores for the current week automatically calculated in this sheet. You can see different points have been given for each parameter in the first row. You can modify the same as per the weightage you wish to give to each of them.
Copy and paste only values of the score in the Cumulative Scoring sheet.
I have kept the provision of 8 weeks in this sheet. You can add more if you want. When you add more, then kindly please modify the last cumulative score table to update and accommodate the new weeks added.
In order to sort and declare the rankings, kindly please copy and paste the values in a separate excel sheet and do the sorting. Any changes in the sorting will disturb the formulas and hence it is advised not to do any changes there.