Virtual Workshops

Part of keithjbrown.co.uk

Main Menu

• Server Environment
• MySQL
• PHP Databases
• Web Design
• Excel

Averages and Mail Merges

You have been approached by the secretary of a golf club who wants to create a spreadsheet that will calculate the Handicaps of the members and produce handicap certificates. There are three things that you need to know:

• Handicaps are calculated by deriving an average OVER the par of the course from three submitted scores
• The Official Handicap is calculated to one decimal place
• Handicaps used in competitions are 7/8s of the official handicap.

Note: For UK handicaps the three submitted cards are evaluated for scoring on each hole. Any score greater than +2 is reduced to two over for that hole and the final round total recalculated.

Getting Started

Open Excel and create the following columns:

Adding the Data and Creating an Average

Next we need to add some sample (fictional, I know ;-) data:

This information gives us the 4 figures we will work from: Par and 3 scores. Remember, what we want to express here is the average over par. To work that out we need to express this type of equation in an Excel formula:

The average of the 3 rounds minus par = handicap. The first stage here is to use Excel AVERAGE function to calculate the average of the fields 'D2', 'E2' and 'F2'. Click in 'G2' and enter the following formula in the Formula Bar: =AVERAGE(D2:F2). Note: Using a colon ':' means all fields between the two that are named in the formula, and is entered automatically if you drag over the boxes concerned after typing (. You should now see something similar to this:

There are two things wrong with this as a resulting 'G2' value. First, this doesn't show us the average over par and secondly, the handicap isn't calculated to one decimal place, thus we have to add more to the formula to achieve these results. To obtain the average OVER par we need to subtract the value in 'C2' from the average. Transform the existing formula to read: =AVERAGE(D2:F2)-C2. Which should result in:

Rounding up and down of figures

Three main Excel functions are used to control the rounding of figures:

INT(number) - finds the integer part (the part before the decimal point)

eg =INT(23.3) gives an answer of 23.

ROUND(number, decimal places) - rounds to the nearest number with the appropriate number of decimal places

eg =ROUND(36.476, 1) gives an answer of 36.4

ROUNDUP(munber, decimal places) - rounds the number UP to the nearest HIGHEST number with the appropriate number of decimal places

eg =ROUNDUP(46.2, 0) gives an answer of 47

Thus to calculate our official handicap to one decimal place we need to use the ROUND function, and amend our formula like so: =ROUND(AVERAGE(D2:F2)-C2,1). Note: The brackets signify what is evaluated by each function and the outermost function evaluates the results of the any other function inside its brackets. The final result:

More mathematic functions

The basic tenet of any manipulation of figures is the ability to add, subtract, multiply and divide. We saw above how to subtract a number, but we can also use other maths functions as well. These are represented within a formula like so:

Operator	Function
+	Add
-	Subtract
*	Multiply
/	Divide

We will use the Multiply and Divide functions to calculate 7/8 of the total of the handicap for the final. To do this we will take the value of the handicap, divide that by 8 and multiply the result by 7. Finally, we want to ROUND that figure to a whole number.

=G2/8	Divides by 8
=(G2/8)*7	Multiplies the result by 7
=ROUND((G2/8)*7,0)	Rounds to a whole number - '0' decimal places

 

Adding more rows

In the next two rows add the following data:

In order to apply the formula we created for cell 'G2' to cells 'G3' and 'G4' simply select 'G2' and drag the black square at the bottom right over cells 'G3' and 'G4'. Repeat this with cells 'H2', 'H3' and 'H4'. You should now save and close the file.

 

Merging with a Certificate in Word

Download and open the file handicap.doc. We will use this file to carry out a mail merge, inserting the data from the Excel file into the text of the word document to create handicap certificates. You will see that there are definite spaces between the existing lines for a name and a handicap to be inserted. To begin our mail merge select Tools / Mail Merge and the following screen will pop-up:

You should then select Create / Form Letters and choose Active Window when asked which document you want to use for the merge.

Next choose Get Data / Open Data Source and browse to the Excel file you created above (ensure that you set the 'Files of Type' to MS Excel Worksheets *.xls). Again choose Entire Spreadsheet when prompted. You will also be asked to 'Edit Main Document'. This is what we want to do, so click that button as well. This will show us our Word file, waiting for us to add our merged fields: you will also notice that the Mail Merge toolbar has appeared.

We will use this bar to add our fields to the document. Move the cursor to the second (of three) blank lines below the text 'This certifies that'. Now select 'FirstName' from the Insert Merge Field drop down menu. Add a space and then select 'LastName' as well. Move the cursor to the second (of three) blank lines below 'holds a current handicap of' and insert the field Exact_Handicap. If you click on the ABC button you will see the fields merge. You can also browse through the records to see that everything is OK.

Finally, place the cursor after 'as of ' and choose Insert / Date and Time.

Choose the format you want, but ensure that the 'Update automatically' box is ticked.

Doing the merge

The only thing left to do is the actual mail merge. Click the Merge button on the Mail Merge toolbar:

You are given the option to merge straight to the printer or to a New Document. Select New Document and click Merge. A new document containing all of the Handicap Certificates will be created.

 

Lastest 10 Threads - view all

---

Post a Comment or Question

Name

Email

URL (optional)

Title of Comment

Comment
HTML Allowed:
a,code,strong,em

I would like to be e-mailed any response to my comment.

In this section

• Averages and Mail Merges
• Basic Financial Statements
• Database Functions
• Whole Applications

Related Reading

• Another Tutorial

Related Books

Related Ads