As usual no two days are the same at my work placement, I got in and completely forgot it was flexi Friday so at home I was taking my time this morning in terms of getting ready. As the PA was slightly late I helped out the receptionist with some errands to use time wisely I filled up the coffee jars, counted the competitions and put the magazines away in the magazine cupboard.
Today task was seeing what happens with all the data that I worked on when typing up all those competition entries a few weeks ago. It was quite exciting for me because it feels like next time I can fully finish the job. What happens is I type up the competition entries by writing the person’s name, their house number and postcode, followed by their magazine subscription number and filling in which competitions they answer. When the document is finally finished, the next step is to open Key Note…
Once Key Note is opened, I started with the usual steps of opening up the inspector, colour pallet and font. I then, (with the help of the PA assisting me and showing me how to do it as it was my first time doing this assignment.) started to compose the presentation of what the stats revealed from the competition entries to then show the client of the magazine. What I had to do with the Excel document was calculate the % of how many entered what competition. Below is a diagram of one I have made up to show you the process of what I learnt…
To calculate the amount of people who entered a competition is you have to use a sum called COUNTIF as shown in the diagram below…
What you do is choose a cell where you want the sum to by totaled (as seen above) click = enter =COUNTIF(Click cell where the first entry is:Cell where the last entry is,”Word you want counted” Then click Enter and it will calculate it for you
A quick way to add them all up to see how many people entered which competition simply highlight and drag to the last column as shown below and let go and it will have calculated the formula for each column.
However once you have let go it the rest of the columns will look like this…
This is because the formula is not calculating the right column, such as in my first diagram it is still counting for the word dog. To change this simply click on the cell and change the word “Dog” to the correct word i.e the next column for it to count how many people preferred “Cats” instead.
Once you have done this you are then ready to calculate the percentage for the whole competition. To calculate the percentage…
Firstly, where you can see the Total in the diagram you need to add up all of the totals in a separate cell to do this you need to click = in a cell & add all of the cells up. Make sure to include the plus sign in between each cell number as shown below…
Secondly, you then need to make a row for where you percentages will go, then start dividing the answer with the total of entries that there were such as in my example the formula will be =B14/I14 (Note: / means dividing in Excel) do the same for each column remember to have to substitute the first part of the equation only such as C14, D14 etc…
Once you have done that you will notice that all the numbers have been worked out as decimals, as shown below….
To convert them into percentages:
-Click onto the decimal cell
-Decimal places 0
Once you have done this you will then have all the data you need to then start converting into tables for the Key Note document.
One thing that you need to be careful with is sometimes when you put in a formula you may see the words but the formula might not pick them up this is because if you have wrote the word “Cat ” and put in the formula to search for “Cat” the search won’t pick it up so what you need to do to fix this is to click Apple + F The following will come up…
Click on the Replace tab and find the word “Cat ” & replace with “Cat”. When recalculating the formula it will then calculate it correctly.
Another tip, the PA taught me to put in colour the different sums that you have to calculate so it’s easier for you to read when looking back for the answer. A tip that the PA taught me is to put the $ sign in between the the letter & number of the cell and you will find that it doesn’t make the cell move.
After I had calculated the % of how many people preferred which competition to enter, I then made a slide on Key Note remembering to apply F.A.C.E (Font, Alignment Consistency & Engagement) (I’m not able to show you the steps for Key Note as I don’t have it installed on my computer) for each competition and made a table so that for the client it’s visually easier to see the differences in percentages.
Today was packed with learning about how to make more use of Excel, I learnt some more keyboard short cuts such as….
Apple + F = Find
$ = It doesn’t make the cell move
What I find is sometimes I reflect over what I did at placement and I am so grateful for the PA asking me to come in and do something which could take someone who has more experience and knows what there doing 30 minutes whereas it takes me a whole working day, yet she still assists me if I’m stuck or don’t understand something at all to pause her busy working day and help me through it.
The PA also said to me it would be useful if I made a Word document of the experience I have with them so that for them it’s handy to know what I can and can’t do so I’m going to be working on that. I now won’t be posting until after the 12th September as i’m going on holiday on the 3rd Eeeekkk
See you soon. X
Also posted in http://lifeisfunbeingme.wordpress.com/