The information will I need to collect and where will I find it?
I am going to need to find out:
- The names of the teams in the championship
- The name of the events that’s been held
- The records for each of the events
- The scores awarded for each place
- I will need to find out how to do technical things like macros
Things that I may use to help me:
- Textbooks
- Online tutorials in Excel
- The help function in Excel
The resources (people, document, Hardware and Software) I will use:
I will be using the school’s PC’s on the network in the collage and save my work into my user area, I will also do back up copies on my memory stick which I will keep in a safe place. I will need a spreadsheet package (Excel) and a word processing package (Word) to produce my spreadsheet, the report and the user guide. I will ask my teacher about things I get stuck on and also the Help function and on-line tutorials for Excel.
How I set up my the spreadsheet
Row Height and Column Width
Function needed for the spreadsheet:
For the Input sheet I have a grid called position grid which reads the team names in the Results grid and calculates which position each team achieved in that event. You will be doing this by using the function =MATCH(“Team Name”, Row of Result Grid, 0)
On the input sheet I have a grid called the Total grid which reads the position of each event and looks up how many points each event team scored and places them in the grid using a VLOOKUP function. You type in =VLOOKUP(Position in Event, Points Grid, 2)
I have set up an IF function in the Results grid to show if a record has been broken in that event. You will indicate if records have been broken alongside each event in each Results Grid by using the function:
=IF(First Place Time<Event Record,”New Record”,””)
For the Totals grid I have used a SUM function to total up the scores for each team.
You can use cell formats to determine what data will be entered into the cell. The cells I have used in my spreadsheet have text in them, so I have used text format for the cells that have numbers in them I have used number format. (shown above)
Cell references
I used cell referencing in my spreadsheet to refer to other grids. E.g. I have referenced cells in the results grid from the positions grid. A cell is referenced by its column letter and row number co-ordinates e.g. B13 maybe easier to just fill in data by using the fill option in the edits menu.
By doing this it will automatically increments the cell reference number. To get around this I had to use absolute cell referencing where I told the spreadsheet to look at a specific place that would not change.
Formulas and Functions that I used
The function to calculate new records:
The VLOOKUP to calculate the scores and SUM to calculate the final overall score:
The MATCH function to calculate position of each team in each race
How to find data in the spreadsheet
Graphs I chose to generate and why I chose them
The reason why I chose to use a bar chart was because it’s an easy way to compare the results also it’s easy to read and understand them.
Why I chose this line graph, was because it’s a good way to show the progress of each of the teams/ area. The background is striking and the information is clear to read. Consequently it might have been more accurate but not easy to read quickly by the scorer. For my system graph I will choose a variation of the bar chart used in the first graph.
“What if”…………Queries
In this exercise I am going to change the scores of the points for the “Athletic Championship”. My prediction is that the final scores are going to change and it will be more competitive with all the teams and will have closer results. The screen dumb, of my spreadsheet before I changed the scores is shown below. If you look at the spreadsheet you can see that “South” won the championship.
In the screen shot below you can see that the competition was much harder and that the results were much closer as I predicted. This was because I changed the “Points Grid” (shown on the table below) from 12, 9, 5, 1 to 5, 4, 3, and 2.
How I ensured that my work was safe
I have saved all my original files in my user area under Unit 4 folder in the schools network system (shown above).
I saved my files on to my memory stick as my back up copy (shown above)
How I created the interface
First of all I renamed the three sheets in my workbook as Input, Interface and Graph. I then highlighted the whole Interface sheet by going in between the blank cell of A and 1 and went to Format- Cells- Patterns and chose a pale colour and clicked OK. Then I went to cell B2 and holding the mouse button down highlighted all the cells from B2 to F3. Next I went to the merge cells button (shown below) in the toolbar and clicked it.
Merge cells button
I then highlighted the same area and formatted it a different colour. In cell B4 I typed the words “Type the name of the event in the box above”. So the event organiser can type in the name of the event.
Next I highlighted cells I2 to K3 and changed the colour to white. I merged the cells and typed this formula into the cell: =TODAY(). That is – equals, TODAY, open brackets, close brackets. It will then automatically enter the day’s date.
This is how the spreadsheet looked
I then had to insert some buttons on the interface that will carry out actions on the spreadsheet.
Firstly I needed the Forms toolbar to be showing. I Right clicked on any toolbar in Excel at the top of the window, a list of toolbars showed up then I clicked on Forms. I then clicked the button icon on the Forms Toolbar it looked like this:
I then Drew a button (shown below). When I finished drawing the button a macro window opened up called “Assign Macro”.
I clicked on the Record button and this window opened up
I clicked OK. A small widow opened up (shown below) to let me record the macro. Now everything I did was recorded. The action I wanted to record was to switch to the Input sheet so I clicked on the Input tab at the bottom of the spreadsheet and the icon appeared in the input page. I clicked on the stop button to stop the macro recording.
I then did the same and created another button for the graph that printed a graph of the results and the spreadsheet looked like this (shown below). I right clicked on the buttons and edited the text to show the user what buttons to what.
How I created drop down boxes to help validate data input
In my spreadsheet I went to the cell E5 in the “Results Grid” then I went to the “Data” menu located near the top of the window and scrolled down to “Validation” and clicked on it, then I went to the settings tap like (shown below). When the window opened up I went to “Allow:” and pressed on the drop down box and scrolled down to “Lists”.
When I clicked on “List” I had to type in the “Source” shown below and I then typed in the =H13:K13 Then clicked on the icon next to it and another window opened up and I just pressed on the icon similar to the one shown below and then clicked “OK” and my drop down box was created.
How my drop down box looked like
I then repeated this exercise completing all the cells from E5 to H9 which is 20 cells that all had drop down boxes. (This is a range that contains all the names of the teams)
Testing my system
User guide
Step
1- Turn the computer and printer “ON” (make sure printer is connected to the computer so it is able to print).
2- After the machine has booted up and you have logged on go to the “Start” button and choose “Excel” from the programmes list.
3- Insert memory stick with system on it.
4- In Excel go File- Open and go to the “Look in” box choose the drive that has the “system” work on it and click on the file to open it.
5- Insert the name of the event where it prompts you on the Interface sheet.
6- The date will be entered automatically.
7- When you are ready to enter the results click on the Input button and this will take you to the Input page.
8- On the Input page use the drop down boxes to record the positions of the teams in each race.
9- In the Win name of the Results grid you should record the Winner of the race.
10- In the Win time column you should record the winning time of the race. In the “New record?” column it will tell you if a new record for that event has been created.
11- Do not enter data into any other cells all the calculations are done for you and the final results of the championship are recorded in the bottom line of the Total grid.
12- To return to the Interface page click on the return to interface button at the bottom of the page.
13- To print the graph, of the position of each team in each race and the final score click on the print graph button.
14- Ensure you save your work regularly by clicking on the Save icon in the Standard toolbar or by going to File and then Save.
15- When you have finished ensure that you save the file again and then close it, then close the program and turn the power off, on the machine.
Evaluation
For the athletic champion spreadsheet which calculated an automatic score, I created graphs, save backup copies and made validation checks. With a little help I completed the spreadsheet. I also had to create an interface to make the working of the system easier for a novice to follow. This was hard at first but I then got the hang of it, because by doing one macro gave me confidence to do others.
Some new things I learnt were how to create a spreadsheet, as I had never done such a complicated one before. The hardest thing to do and understand was the teaching because we went through the project very quickly because we had to do it in a certain amount of time. The easiest thing was screen dumping, although it had to be re-formatted so that the screen dumps could be kept in the place I wanted it. The macros were a new thing for me and I had to have a few goes to make them work. Doing one macro meant I got the hang of it and the others were much easier.
Testing the system was straightforward and I had understood most of the problems that occurred, as I was making the system and in the formal testing. At the end of it all worked out well.
The good points of the spreadsheet are doing the formulas, I had trouble with the “MATCH” formula and had to insert it several times into the cells to make it work but the others worked fine. If I did this project again with more time, I would put a password on the file to protect it and may also use hidden files. I will then lock out the user from the formulas so they can’t change them. Furthermore I would make it easier for the user to use the spreadsheet by producing a more illustrated/ simpler user manual. Other ideas I had would be to give a choice of graphs to the user and perhaps print out individual team scores from a new grid I would have created.
