Day 4 - Excel Functions & Formulas

📊 Day 4: Excel Functions & Formulas

Mastering Data Analysis

Ghaziabad, Mohan Nagar

Drafted by: HR Prateek Sharma

Microsoft Excel

Complete Data Solution: Store • Clean • Transform • Analyze • Report

Total Rows
10,48,576
Total Columns
16,384

📈 Counting Functions

COUNT

📊 Purpose: Counts numerical data only

Used for counting cells containing numbers

COUNTA

📝 Purpose: Counts textual data

Used for counting cells containing any text or data

COUNTIF

🎯 Purpose: Single condition counting

Syntax: =COUNTIF(range, criteria)

Example: Count employees with specific designation

COUNTIFS

🎯🎯 Purpose: Multiple conditions counting

Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, ...)

Example: Count employees with specific department AND salary range

🔧 Data Transformation Functions

UNIQUE

✨ Purpose: Extract unique/specific data

Removes duplicate values and shows only distinct entries

Use case: Finding unique employee IDs, departments, or dates

TRANSPOSE

🔄 Purpose: Change data alignment

Converts rows to columns and columns to rows

Use case: Restructuring data layout for better analysis

💡 Power Combo: TRANSPOSE + UNIQUE
Transform and extract unique data simultaneously!

📊 Statistical Functions

AVERAGE (AVG)
MINIMUM (MIN)
MAXIMUM (MAX)

AVERAGE: Calculate mean values (salary, age, performance scores)

MIN: Find lowest values (minimum salary, youngest age)

MAX: Find highest values (maximum salary, oldest age)

🎯 Key HR Metric: Attrition Rate (AR)

Attrition Rate Formula:

AR = (Number of Employees who Left / Total Number of Employees) × 100
Example Calculation:
If 45 employees left from a total of 1500
AR = (45 / 1500) × 100 = 3%

📋 Excel Capabilities Summary

  • Data Storage: Store large datasets efficiently
  • Data Cleaning: Remove duplicates, fix errors, standardize formats
  • Data Transformation: Restructure and reorganize data
  • Data Analysis: Perform calculations and statistical analysis
  • Dashboard/Reports: Create visual representations and insights

Ghaziabad, Mohan Nagar

Design & Content by HR Prateek Sharma

Day 4 - Excel Functions & Data Analysis Training

Microsoft Excel • Data Analytics • HR Metrics

Comments

Post a Comment

Popular posts from this blog

  HR Analytics - Day 2 Notes 📊 HR Analytics - Day 2 Ghaziabad, Mohan Nagar 🔄 Different Phases of HR Phase 1: Job Posting Internal Hiring: Recruiting from within the organization External Hiring: Recruiting from outside the organization External Hiring Process: Recruitment Example Scenario: 1000 resumes received for 2 job positions Step 1: Short Listing - ATS (Applicant Tracking System) From 1000 resumes → ATS shortlists → 100 candidates ⚠️ Critical Point: Selection of wrong candidates = Loss of time KPI Metri...
Read more
  HR Analytics - Day 1 Notes 📊 HR Analytics - Day 1 Ghaziabad, Mohan Nagar 💱 Currency Conversion Reference $1 Million = ₹8,000 Cr $5 Million = ₹40,000 Cr $50 Million = ₹40 Lakh Crore 📚 Key Terminology BPO Business Process Outsourcing GPU Graphics Processing Unit KRA Key Responsibility Area KPI Key Performance Indicator - Quality of work, helps che...
Read more
  Day 3 - HR Analytics Notes 📊 Day 3: HR Analytics & Business Intelligence Ghaziabad, Mohan Nagar Drafted by: HR Prateek Sharma 📈 Key Metrics Attrition Rate (A.R): Total Employees Left / Total Employees × 100 Example: 45/1500 × 100 = 3% Yearly Increment of Employee = 10% 🔍 Four Types of Analytics 1. Descriptive Analytics Like describing the problem to a doctor Name, Age, Gender, Address Last college name Kinship Rule: Blood relations allowed in companies (preference-based) Considerations: Discrimination, same comp...
Read more