SUM, COUNT, AVERAGE and their IFs - the basic Excel functions you can't live without (including a video tutorial)
Today I’ll be talking about some of the most basic yet important functions in Excel.
You actually can’t start using Excel without learning these functions first. It’s like when you first go to school and learn how to add and subtract.
These functions are SUM, COUNT, AVERAGE, SUMIF, COUNTIF, AVERAGEIF.
When the data is loaded to the Power Pivot model (as shown in Part 2), you’ll see that we have 6 tables in 6 tabs attached to the model. These tables are:
Now we will be doing all of our data analysis and modelling in Power Pivot.
So first things first. To enable Power Pivot in Excel, go to File -> Options -> Add-ins
In the Manage section at the bottom, choose COM Add-ins -> Go -> and then make sure you select Microsoft Power Pivot for Excel.
Through a series of posts, I will be using the data of a fictitious company called VanArsdel Ltd. to illustrate how you can use the different Microsoft BI tools with business-oriented data, reports and dashboards.
Throughout the series, I will be using Excel to collect and organize the data from different sources (will be Access DB in this case), use PowerPivot to build the data model and finally use Power BI to visualize and further explore the data.
As the title implies, a new add-in has been developed by Microsoft (as a garage project actually) to help transform data in seconds.
It’s mainly targeting Excel users who face problems in data wrangling; that is transforming raw data into a different format so that it can be used for further analysis. Usually this task is one of the most tedious tasks for any Excel user since the data could be coming from different sources in different formats and the cleaning process to make it all "homogeneous" could be a little bit daunting with too many functions and formulas included.