Posts

Image
  8.  Counta() COUNTA determines whether a cell is empty or not. You’ll come across incomplete data sets daily as a data analyst. Without needing to restructure the data, COUNTA will allow you to examine any gaps in the dataset. SYNTAX = COUNTA (value1, [value2], …) 9. Vlookup() The acronym VLOOKUP stands for ‘Vertical Lookup.’ It’s a function that tells Excel to look for a specific value in a column (the so-called ‘table array’) to return a value from another column in the same row. SYNTAX = VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup]) 10.  Hlookup() “Horizontal” is represented by the letter H in HLOOKUP. It looks for a value in the top row of a table or an array of values, then returns a value from a row you specify in the table or array in the same column. When your comparison values are in a row across the top of a data table and you wish to look down a specific number of rows, use HLOOKUP. When your comparison values are in a column to the left of the data
Post a Comment
Read more
Image
  5.  Sumifs() One of the “must-know” formulas for a data analyst is =SUMIFS. =SUM is a familiar formula, but what if you need to sum data based on numerous criteria? It’s SUMIFS. SYNTAX = SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], …) 6.  Averageifs() AVERAGEIFS, like SUMIFS, lets you take an average based on one or more parameters. SYNTAX = AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], …)
Post a Comment
Read more
Image
  3.  Days() The number of calendar days between two dates is calculated using this function = DAYS. SYNTAX =DAYS (end_date, start_date)   4.  Networkdays The number of weekends is automatically excluded when using the function. It’s classified as a Date/Time Function in Excel. The net workday’s function is used in finance and accounting for determining employee benefits based on days worked, the number of working days available throughout a project, or the number of business days required to resolve a customer problem, among other things. SYNTAX = NETWORKDAYS (start_date, end_date, [holidays])
Post a Comment
Read more
Image
  2.  Len() In data analysis, LEN is used to show the number of characters in each cell. It’s frequently utilised when working with text that has a character limit or when attempting to distinguish between product numbers. SYNTAX = LEN (text)
Post a Comment
Read more
Image
  Essential Excel Data Analysis Functions Excel has hundreds of functions and trying to match the proper formula with the right kind of data analysis can be overwhelming. It is not necessary for the most valuable functions to be difficult. You’ll wonder how you ever lived without fifteen easy functions that will increase your ability to interpret data. 1.  Concatenate When conducting data analysis, the formula =CONCATENATE is one of the simplest to understand but most powerful. Text, numbers, dates, and other data from numerous cells can be combined into a single cell. SYNTAX = CONCATENATE (text1, text2, [text3], …)
Post a Comment
Read more
Image
  Data analysis is a valuable skill that can help you make better judgments. Microsoft Excel is one of the most used data analysis programs, with the built-in pivot tables being the most popular analytic tool. Microsoft Excel allows you to examine and interpret data in a variety of ways. The information could come from several different places. A variety of formats and conversions are available for the data. Conditional Formatting, Ranges, Tables, Text functions, Date functions, Time functions, financial functions, Subtotals, Quick Analysis, Formula Auditing, Inquire Tool, What-if Analysis, Solvers, Data Model, PowerPivot, PowerView, PowerMap, and other Excel commands, functions, and tools can all be used to analyse it.
Post a Comment
Read more
Image
  The iterative Data Analysis Process is comprised of the following phases: • Specification of Data Requirements • Data Gathering • Data Processing • Data Cleaning • Data Analysis • Data Communication  
Post a Comment
Read more