To check the accuracy of the data that was entered into my spreadsheet I verified the data in my spreadsheet with the data that I originally compiled my research on a piece of paper. This check was to ensure that all of the information that I entered into the spreadsheet was correct.
As I formatted the ‘Price’ and ‘Rating’ columns, data entered that does not fit into the criteria I gave them will be altered so that they will fit into the criteria. For example if a number with any decimal places is entered into the ‘Rating’ column, Excel will automatically round the number up or down so that it becomes a whole number.
Creating formulae
The market research company want to find out the average cost of the games and the rating of games in your list.
To calculate the total cost of all of the games I used the formula ‘=SUM(C2:C25)’ in the cell C27, which is underneath all of the prices of the games, I put it a cell down from the other information so that it would not confuse people. I also labelled the total cost as ‘Total Cost’. The formula simply tells the spreadsheet to add all of the numbers in the cells C2 to C25.
To calculate the average cost of all of the games I used the formula ‘=AVERAGE(C2:C25)’ in cell C28. I labelled this new data as ‘Average cost’. This formula takes all of the prices in the cells C2 to C25 and finds the average price.
To calculate the average rating of all the games, I used the formula ‘=AVERAGE(E2:E25)’. I added a new column between the ‘Price and the ‘Rating’ columns by inserting, this was so I could label the average cost ‘Average cost’.
To state that games, which have a popularity rating that is over 5, I used an IF formula. There are two parts to the IF formula that I used, one part of the formula makes sure that games with a popularity rating of 6 or over will be called ‘Popular’. The other part of the formula makes sure that all the games with ratings of 5 or under will be called ‘Not popular’. I dragged the corner of cell F2 down, with the formula in it, so that all of the games would have an IF formula, which will correspond to their rating.
I verified that the IF formula was working by checking the rating with the popularity rating. If the rating was 5 or under then the popularity rating should be that it is ‘Not popular’. However, if the rating is 6 or over, the game should have been rated as ‘Popular’. Also, I looked through most of the cells with the IF formula in them to check that they all correspond with the cells next to them.
I did not need to change the page orientation or page set up as I had already done this earlier on. As you can see from the screen dump below, all of the data from the spreadsheet fits onto one page.
As the Market research company only want the 10 most popular games, I will need to delete all the other games. I will sort out the 10 most popular games by sorting the games by their ratings. I did this by clicking on one of the cells in the ratings column and then I clicked on the sort-descending button so that the most popular games would be shown first. I then highlighted all the cells, which were not in the top 10 and deleted the data in them.
The difference between the two printouts is that on the second printouts all of the games are rated popular. The total cost of the games has now decreased from £727.77 to £316.90 and the average cost has actually increased to 9 from 6 as the lower rated games have now been removed.
What IF investigations (changing formula)
I changed the IF formula in the popularity column so that all games that have a rating between 0-3 is rated as ‘Not popular’, those that have a rating between 4-7 are ‘Popular’ and the games, which have ratings of 8-10 are ‘Very popular’. I used the formula ‘=IF(D2<=3,"Not popular",IF(D2>=8,"Very Popular","Popular"))’ the first part of the formula states that if the game has a rating of 3 or under, it will be called ‘Not popular’, the second part states that if the game has a rating of 8 or more it will be called ‘Very popular’. The last part of the formula state that if the games does not have a rating that is 0-3 or 8-10 it will be called ‘Popular’
The changes to the spreadsheet now is that there are now more categories for the ratings, allowing people to see whether or not the games are ‘Popular’, ‘Not popular’ or ‘Very popular’. This means that for the market researchers they will be able to see, which games are much better than others and those that are moderately popular.
As I sorted the ratings column in ascending order it is now possible to view all the games popularity ratings in order. This not only makes the spreadsheet seem more professional but neat as well as tidy. It is easier to see, which games are better than others and should help the market researchers more this way.
Creating a graph
The market research company have asked me to create a graph to show the ratings for the top 10 games. I did this by copying the Game name and Ratings columns onto a new sheet. I then highlighted these two columns and clicked on the chart wizard button. I created a simple column graph by using the chart wizard.
I formatted the graph by changing the alignment of the game names so that it was easier to read them. I also changed the font size and type of the writing so that it also much easier to read the writing.
I chose to use a column graph as I think that it shows the data better than another chart. For the market researchers it will be very easy to compare the ratings.
Further investigations
The spreadsheet could be further enhanced and improved by adding this data:
- The genre of the game – this will allow the market researchers to see, which types of genres are most popular.
- The number of players who can play the game – this will allow the market research company to see whether or not people prefer to play one player or multi-player games.
I do not think that the spreadsheet needs any more formulas in order to improve it. I also don’t think that the data there can be calculated by using any formulas.
I created a folder entitled ‘Modelling’, in this folder there are all the spreadsheets that I have created and my assignment as well. Here is a screen dump, which shows where I have saved all my work.