Table 3: Subs
Each cadet is due to pay a £10.00 subscription each month. Payments will be entered into this table each month and the last two columns will calculate the amount each cadet has paid to date and what percentage of the amount due has been paid
I have obtained the information to be entered in the spreadsheet from the A.T.C. leader who has given me the paper-based records
I am now going to explain what FORMATS I will use in each grid.
Table 1: Information
The columns were widened so all the data was displayed; using Format, Column, autofit Selection as shown in the screen shot :
I then made the heading in row 2 bold. Using format, format cells, font tab, bold.
To make it easer to read and more attractive I changed the colours of the rows and titles. To do this I highlighted the cells I wanted to change the colour of then clicked format, format cells, pattern tab, then selected the colour from the palette:
Table 2: Attendance
I formatted the column widths, headings and colours the same as table 1. In addition, all the cells us in column J were formatted to appear as percentages, by highlighting the cells I wanted to change, then selecting format, format cells, number tab and selecting percentages from the options.
Table 3: Subs
I formatted the column widths, headings and colours the same as tables 1 and 2. In addition, I formatted all the columns that show an amount of money as currency by highlighting the cells I wanted to change then clicking format, format cells, then selecting currency from the options on the number tab and selecting 2 decimal places . I needed two decimal places as this is how currency is always shown : £00.00
I am now going to explain how I setup the FORMULAS and FUNCTIONS in each grid. You always have to enter an equals sign (=) before typing in a formula or function, otherwise the formula appears as text.
Table 1: Information
As this is just a database of information, there are no formulas or functions in this table
Table 2: Attendance
The last column in this grid, column J, shows the average attendance to date of each cadet. I have entered the formula =I3/COUNT(C3:H3) into cell J3 then copied this formula into the cells below it by highlighting this cell, clicking the copy button on the standard toolbar, highlighting the cells I want it copied to, then clicking the paste button.
This formula takes the total attendance to date from cell I3, then divides it by the number of cells in that row which have a value entered into it. So after two months the total attendance will be divided by two, after three months it will be divided by three and so on.
Column I shows the the number of months to date that each cadet has attended. I have entered the function =SUM(A3:H3) into cell I3 then copied this formula into the cells below it as before. This function gives the total of all the values in the range of cells from A3 to H3
To enter the function, click on the Paste button on the standard toolbar :
Then select from the Function name box :
I have used the Average function =AVERAGE(I3:I18) to give the average attendance for all cadets. I typed the function into the cell where I wanted the answer to appear. I did this so I could see how much each cadet was attending compared with the average for the squadron. I did this for both the total and percentage columns.
I have used the Maximum function =MAX(I3:I18) to show the highest attendance and the Minimum function =MIN(I3:I18) to show the lowest attendance. I typed the function into the cell where I wanted the answer to appear
Finally I have used a Range formula (=122-I24) to show the difference between the maximum and minimum attendance. Again, I typed this formula into the cells where I wanted to answer to appear
Table 3: Subs
The last column in this grid, column J shows the average amount paid by each cadet to date. I have used the formula =I3/COUNT(C3:H3) as explained for Attendance
I have also used the functions SUM, AVE, MAX and MIN as explained for Attendance and the AUTOSUM function to add up the total amount paid to date :
The actual formulas and functions used in each grid are shown on the following printouts.
To display the formulas and functions I clicked on Tools, Options, then selected Formulas from the Window
options box :
I have learned that a formula is something entered by me, for example cell =A1+A2, and a function is already is already set up in Excel, for example SUM
I am now going to explain how I produced GRAPHS from the data in each grid.
Table 1: Information
As this is just a database of information, there are no graphs in this table
Table 2: Attendance
Table 3: Subs
I am now going to explain how I used a ‘WHAT IF …’ query
Table 1: Information
As this is just a database of information, there is no What if query
Table 3: Subs
I want to find out what the total amount paid to date would be if the monthly subscription is increased from £10.00 to £12.00 for each cadet.
To do this I highlighted the cells to be changed, clicked on Edit, find then entered 10 in the Find what box and entered 12 in the Replace with box
I then clicked on Replace all and the grid was changed as the print-out on the next page.
The total amount to date has changed from £350.00 to £420.00.
I am now going to explain how I used a ‘IF query
Table 1: Information
As this is just a database of information, there is no If query
Table 3: Subs
Each cadet has to pay a £10.00 per month subscription to cover administration costs and this is recorded in the Subs grid. To help the user pick out which cadets are behind with their subs I have used a If query.
To do this I inserted a new column after the Total amount to date column and entered the IF query
=IF(I13<21,”Chase”,”-“) where I wanted it to appear in cell J3. I then copied this formula into the cells below it by highlighting this cell, clicking the copy button on the standard toolbar, highlighting the cells I want it copied to, then clicking the paste button.
This means that IF the total amount paid to date is less than £21.00 the message “chase” will appear. IF the subs are up to date, a dash will appear
I am now going to explain how I SORTED DATA in the spreadsheet
Table 1: Information
As new cadets join the Squadron, their information is entered onto the sheet. Using the Sort feature, I can then put the first column (Second Name) into alphabetical order.
I did this by clicking on a cell in the column I wanted the rows sorted by, then clicking on the Sort Ascending button on the standard toolbar
The New Cadet now appears in the correct alphabetical position. All the information in the row about this cadet has moved with the first column
Table 2: Attendance
It might be useful to sort the grid by percentage attendance to show who has the highest attendance and who do not attend often. Using the Sort feature, I can put the last column ( % to date) into alphabetical order.
I did this by clicking on a cell in the column I wanted the rows sorted by, but this time clicked on the Sort Descending button on the standard toolbar.
This will give me the highest percentages at the top of the table
Table 3: Subs
A similar sort could be done on the % Paid column to show which cadets are behind with their subs
Accurate and safe ???
