Ivy Mar 21, 2018 No Comments
MS EXCEL, the most wanted skill set in the industry irrespective of your academic background. With an easy User Interface, this tool is popular across every segment of the business. It allows a wide array of activities ranging from bringing data together to extracting information from it using a variety of functions at your disposal to visualising the extracted information and deriving the best possible outlook.
So is it that you know Excel well?
With this Excel Series to come we make sure that you not only gain an insight into its properties but also gain in-depth knowledge about them.
DATABASE FUNCTION
You are well aware of functions like SUMIFS, COUNTIFS, AVERAGEIFS etc that help compute when your problem involves numerous AND conditions. Have you ever thought what if we have combinations of both AND and OR conditions?
Well, DATABASE FUNCTIONS come to your rescue at exactly this point. It has the added advantage of letting you compute and get specific information when you face multiple AND and OR conditions.
TYPES
| DSUM | Allows you to add data based on set criteria |
| DAVERAGE | Allows you to average data based on set criteria |
| DMAX | Allows you to find max based on set criteria |
| DMIN | Allows you to find min based on set criteria |
| DCOUNT | Allows you to count numeric values based on set criteria |
| DCOUNTA | Allows you to count non-blank values based on set criteria |
| DGET | Used to return a single field of data from a column of a database that matches conditions you specify |
| DSTDEV | Allows you to find std. deviation based on set criteria |
| DVAR | Allows you to find variance based on set criteria |
Each Database function has the same 3 arguments:
Find an example below of how to work with these functions:
Advanced Filtering
Well, keeping in tune with conditions based setups, an important feature that Excel provides is Filtering whereby we can extract rows of information based on some criteria. The only drawback is that this allows filtering stepwise such that at each step of filtering lesser data is available. Advanced Filtering is a step ahead by allowing its users to apply multiple filtering criteria at once.
All you need to mention are these 3 options:
Let’s have a look at an example:
Advanced filtering not only provides you with the option to copy-paste your output to some other location and also has the option whereby you can extract Unique Records only without requiring removing duplicates separately!
Ivy Pro School is the best Data Science Training institute since 2007 with campuses in Bangalore, New Delhi, Pune and Kolkata. Ivy’s flagship program – Data Science and Machine Learning Certification has created a benchmark in the Analytics Training Industry. Call/Whatsapp us at 9748441111 for more details.
Leave a Reply