To succeed as a data analyst, understanding the basic building blocks of Excel is essential. Some of these building blocks are operators, functions, and formulas. The goal of this exercise is to teach you how to create formulas in excel.
You will learn how to create and use a simple formula in excel to calculate the goal ratio of the English Premier League’s top goal scorers.
To avoid confusion, let me quickly explain the terms – operators, functions, and formulas.
- Operators specify the type of calculation you want to perform in a formula
- Functions are predefined formulas that perform calculations
- Formulas are expressions that operate on values in a range of cells
For example: SUM(I13:I15)+10
- SUM(I13:I15)+10 is a formula
- + is an operator
- SUM() is a function
Now that we understand these terms, let’s get right into our exercise.
Data:
- The data used for this exercise is obtained from Wikipedia
- Download the exercise here – English Premier League Top Scorers
Exercise:
- Calculate the goal Ratio of each player
- Calculate the Overall Average of all players
Hints:
- Calculate the Goal Ratio by using the division operator to divide Goals by Games
- Calculate the Overall Average using the AVERAGE() function
- Don’t forget to set your Ratio to two decimal places
Your result should look like this.
Bonus:
Get the data directly from Wikipedia. To do this:
- Go to Data > Get Data > From Other Sources > From Web
- Input the URL https://en.wikipedia.org/wiki/Premier_League_records_and_statistics
- Click OK.
- Click “Connect”
- Click “Load”
Then go ahead to clean up the data and complete the exercise.
Hope you found this tutorial on how to create formulas in excel helpful.
Happy Analyzing!