Math & Statistical Functions — Complete Guide (8 Topics)

Excel ki mathematical aur statistical power — ROUND, RANK, MEDIAN, PERCENTILE, FREQUENCY. Numbers manipulate karo, data analyze karo, statistics nikalo — Data Insights par.

📑 Is Part 6 Mein Aap Kya Sikhenge:

• Topic 1: ROUND, ROUNDUP, ROUNDDOWN — Number Rounding
• Topic 2: CEILING, FLOOR — Multiple Rounding
• Topic 3: MOD, INT, ABS — Number Operations
• Topic 4: LARGE, SMALL, RANK — Position & Ranking
• Topic 5: COUNT, COUNTA, COUNTBLANK — Counting Cells
• Topic 6: MEDIAN, MODE, STDEV — Statistical Measures
• Topic 7: PERCENTILE, QUARTILE — Distribution Analysis
• Topic 8: FREQUENCY — Data Distribution

📋 Note: Same Employee Database use karenge — Salary column (E), Age column (G). Yeh columns pe statistical analysis karenge — median salary, salary quartiles, top performers rank, etc.

🔍 Definition: ROUND rounds a number to specified decimals using standard rounding rules (0.5 rounds up). ROUNDUP always rounds AWAY from zero (up for positive). ROUNDDOWN always rounds TOWARD zero (down for positive). Essential for financial calculations and clean displays.

🎯 Samjho Simple Bhasha Mein: ROUND normal rounding karta hai — 4.5 hoga 5, 4.4 hoga 4. ROUNDUP hamesha upar — 4.1 bhi 5 ho jayega. ROUNDDOWN hamesha neeche — 4.9 bhi 4 ho jayega. Currency mein 2 decimals rakhna, prices ko whole numbers mein banana, discount calculations — sab mein use hota hai.

💡 Syntax:

=ROUND(number, num_digits)
=ROUNDUP(number, num_digits)
=ROUNDDOWN(number, num_digits)

num_digits:
Positive → After decimal (2 = two decimals)
Zero → To whole number
Negative → Before decimal (-2 = nearest hundred)

💻 Real-World Examples:

// ROUND — Standard rounding: =ROUND(75432.678, 2) // 75432.68 =ROUND(75432.678, 0) // 75433 (whole number) =ROUND(75432.678, -2) // 75400 (nearest 100) =ROUND(75432.678, -3) // 75000 (nearest 1000)
// ROUNDUP — Always up:
=ROUNDUP(75.1, 0) // 76 (even 75.01 becomes 76)
=ROUNDUP(75.99, 0) // 76
=ROUNDUP(75432, -3) // 76000 (up to nearest 1000)

// ROUNDDOWN — Always down:
=ROUNDDOWN(75.99, 0) // 75 (even 75.99 becomes 75)
=ROUNDDOWN(75.01, 0) // 75
=ROUNDDOWN(75432, -3) // 75000 (down to nearest 1000)

// Real Use — Salary in lakhs (2 decimals):
=ROUND(E2/100000, 2)
// 75000 → 0.75 lakhs

// GST calculation (rounded):
=ROUND(price * 0.18, 2)

// EMI monthly (always up — safe estimate):
=ROUNDUP(loan_amount / months, 0)

// Age from tenure (always down — completed years):
=ROUNDDOWN((TODAY()-F2)/365, 0)

📊 Comparison Table:

⚠️ Common Mistakes:

• Mistake: Format vs ROUND confuse karna → Format sirf display change karta hai, actual value nahi!
  Fix: Real rounding chahiye toh ROUND use karo, sirf display ke liye Number Format.
• Mistake: Negative num_digits samajh nahi aana → -2 matlab decimal se 2 places pehle.
  Fix: Rule: Positive = right of decimal, Negative = left of decimal.
• Mistake: Financial calculations mein rounding errors → Chain of calculations mein cumulative error.
  Fix: Har step pe ROUND karo, sirf final result pe nahi.

💬 Interview Questions:

🔍 Definition: CEILING rounds a number UP to the nearest multiple of a specified value. FLOOR rounds DOWN to the nearest multiple. Different from ROUNDUP/ROUNDDOWN because they round to any multiple, not just decimal places.

🎯 Samjho Simple Bhasha Mein: CEILING/FLOOR "multiple" ke basis pe rounding karte hain. Jaise pricing round karna hai to nearest ₹100 — CEILING(75432, 100) = 75500. Ya nearest ₹500 mein — CEILING(75432, 500) = 75500. Package pricing, batch sizes, box calculations — sab mein use hota hai.

💡 Syntax:

=CEILING(number, significance) → Round UP to multiple
=FLOOR(number, significance) → Round DOWN to multiple

Modern versions (Excel 2013+):
=CEILING.MATH(number, [significance], [mode])
=FLOOR.MATH(number, [significance], [mode])

💻 Real-World Examples:

// CEILING — Round up to multiple: =CEILING(75432, 100) 
// 75500 (next 100) =CEILING(75432, 500) 
// 75500 =CEILING(75432, 1000) 
// 76000 (next 1000) =CEILING(75432, 50) 
// 75450

// FLOOR — Round down to multiple:
=FLOOR(75432, 100) 
// 75400
=FLOOR(75432, 500) 
// 75000
=FLOOR(75432, 1000) 
// 75000

// Real Use — Package pricing (nearest 99):
=CEILING(cost * 1.3, 99) - 1

// Cost + 30% margin, rounded to 99, 199, 299...

// Box calculation (25 items per box):
=CEILING(total_items / 25, 1)

// 100 items → 4 boxes, 101 items → 5 boxes

// Salary bracket (nearest 5000):
=FLOOR(E2, 5000)

// 75000 → 75000, 78000 → 75000, 82000 → 80000

// Time rounding to 15-minute slots:
=CEILING(time_value, "0:15")

// 2:07 PM → 2:15 PM

// Nearest 500 (up or down):
=MROUND(75432, 500)

// 75500 (nearest 500, rounds to closest)

📊 CEILING vs FLOOR Examples:

💬 Interview Questions:

🔍 Definition: MOD returns the remainder after division. INT returns the integer part of a number (rounds down for positives). ABS returns the absolute value (removes negative sign). Simple but powerful functions for many calculations.

🎯 Samjho Simple Bhasha Mein: MOD matlab "baaki bacha" — 10/3 = 3 aur baaki 1 (MOD). INT matlab "sirf pura hissa" — 4.7 ka INT = 4. ABS matlab "positive banao" — -5 ka ABS = 5. Even/odd check, alternate row shading, difference nikalna — sab mein kaam aate hain.

💡 Syntax:

=MOD(number, divisor) → Remainder
=INT(number) → Integer part
=ABS(number) → Absolute value

Common uses:
MOD → Even/odd check, alternate colors
INT → Extract whole part
ABS → Distance/difference (always positive)

💻 Real-World Examples:

// MOD — Remainder: =MOD(10, 3) // 1 (10/3 = 3 remainder 1) =MOD(20, 5) // 0 (exactly divisible) =MOD(15, 2) // 1 (odd number) =MOD(14, 2) // 0 (even number)
// Check even/odd:
=IF(MOD(A1, 2)=0, "Even", "Odd")

// Alternate row shading (Conditional Formatting):
=MOD(ROW(), 2) = 0
// TRUE for even rows → apply shading

// Every 3rd row highlight:
=MOD(ROW(), 3) = 0

// INT — Integer part:
=INT(4.7) // 4
=INT(-4.7) // -5 (rounds DOWN even for negatives!)
=INT(4.99) // 4

// Extract hours from time:
=INT(A1 * 24)
// 0.5 (12:00 PM) → 12

// Complete years from tenure:
=INT((TODAY()-F2)/365)
// 3.7 years → 3 completed years

// ABS — Absolute value:
=ABS(-500) // 500
=ABS(500) // 500
=ABS(A1 - B1) // Difference (always positive)

// Salary difference from average:
=ABS(E2 - AVERAGE(E2:E11))
// How far from average (positive number)

// Distance/deviation:
=ABS(target - actual)
// Sales target vs actual — deviation

// Combining — Extract decimal part:
=A1 - INT(A1)
// 4.75 - 4 = 0.75

⚠️ Common Mistakes:

• Mistake: INT negative numbers pe strange behavior → INT(-4.5) = -5, not -4!
  Fix: INT rounds DOWN (toward negative infinity). For truncation, use TRUNC.
• Mistake: MOD divisor 0 dena → #DIV/0! error.
  Fix: Divisor kabhi 0 nahi hona chahiye.

💬 Interview Questions:

🔍 Definition: LARGE returns the k-th largest value. SMALL returns the k-th smallest value. RANK returns the position of a value in a list. Essential for top-N analysis, leaderboards, and percentile calculations.

🎯 Samjho Simple Bhasha Mein: Top 3 salaries chahiye? LARGE. Bottom 3? SMALL. Kaunse employee ka salary rank kya hai? RANK. MAX aur MIN sirf 1st position deta hai — LARGE/SMALL aur RANK se 2nd, 3rd, 4th sab nikal sakte ho. Leaderboards, top performers, quartile analysis — sab mein use hote hain.

💡 Syntax:

=LARGE(array, k) → k-th largest
=SMALL(array, k) → k-th smallest
=RANK(number, ref, [order]) → Position

RANK order:
0 or omitted → Descending (largest = rank 1)
1 → Ascending (smallest = rank 1)

Modern versions:
RANK.EQ = same as RANK
RANK.AVG = average of tied ranks

💻 Real-World Examples:

// LARGE — k-th largest salary: =LARGE(E2:E11, 1) 
// Highest salary: 95000 (Anjali) =LARGE(E2:E11, 2) 
// 2nd highest: 92000 (Sneha) =LARGE(E2:E11, 3) 
// 3rd highest: 85000 (Priya)

// SMALL — k-th smallest:
=SMALL(E2:E11, 1) 
// Lowest: 45000 (Kavita)
=SMALL(E2:E11, 2) 
// 2nd lowest: 48000
=SMALL(E2:E11, 3) 
// 3rd lowest: 51000

// RANK — Position:
=RANK(E2, $E$2:$E$11, 0) 
// Descending (highest = 1)

// Rahul (75000) → Rank 5
=RANK(E2, $E$2:$E$11, 1) 
// Ascending (lowest = 1)

// Rahul (75000) → Rank 6

// Real Use — Top 3 average:
=AVERAGE(LARGE(E2:E11, {1,2,3}))

// Top 3 salaries ka average

// Bottom 3 average:
=AVERAGE(SMALL(E2:E11, {1,2,3}))

// Second highest salary (unique):
=LARGE(UNIQUE(E2:E11), 2)

// UNIQUE removes duplicates first (Excel 365)

// Show name of highest earner:
=INDEX(B2:B11, MATCH(LARGE(E2:E11,1), E2:E11, 0))

// Result: "Anjali"

// Show name of 3rd highest:
=INDEX(B2:B11, MATCH(LARGE(E2:E11,3), E2:E11, 0))

// Handling ties (RANK.AVG):
=RANK.AVG(E2, $E$2:$E$11, 0)

// If 2 employees tied for rank 3, both get 3.5

// Youngest employee's name:
=INDEX(B2:B11, MATCH(SMALL(G2:G11,1), G2:G11, 0))

📊 Salary Ranking:

⚠️ Common Mistakes:

• Mistake: RANK mein absolute reference nahi lagana → Copy karne pe range shift ho jaati hai.
  Fix: Range absolute: $E$2:$E$11 (F4 dabao).
• Mistake: Ties handle na karna → 2 same salaries dono ko rank 3, next employee rank 5 (4 skip).
  Fix: RANK.AVG use karo — tied values average rank paate hain.

💬 Interview Questions:

🔍 Definition: COUNT counts only cells with numeric values. COUNTA counts all non-empty cells (numbers + text). COUNTBLANK counts only empty cells. Simple but essential for data validation and completeness checks.

🎯 Samjho Simple Bhasha Mein: COUNT sirf numbers count karta hai. COUNTA sab non-empty (numbers + text). COUNTBLANK sirf empty cells. Data validation ke liye essential — kitne records complete hain, kitni values missing hain. Report banane se pehle data quality check karne mein bahut kaam aate hain.

💡 Syntax:

=COUNT(range) → Numeric cells only
=COUNTA(range) → All non-empty cells
=COUNTBLANK(range) → Empty cells only

Example: Range [10, "Hello", "", 20, ""]
COUNT = 2, COUNTA = 3, COUNTBLANK = 2

💻 Real-World Examples:

// COUNT — Numeric cells only: =COUNT(E2:E11) // 10 (all salaries) =COUNT(A2:A11) // 10 (all EmpIDs) =COUNT(B2:B11) // 0 (names are text!)
// COUNTA — Non-empty cells:
=COUNTA(B2:B11) // 10 (all names filled)
=COUNTA(A2:G11) // 70 (all filled cells)

// COUNTBLANK — Empty cells:
=COUNTBLANK(E2:E11) // 0 (no blanks)
=COUNTBLANK(A2:G11) // Total blanks in table

// Data completeness percentage:
=COUNTA(range)/(ROWS(range)*COLUMNS(range))*100
// % of cells filled

// Total employees (from name column):
=COUNTA(B2:B11)
// 10 employees

// Missing
values in specific column:
=COUNTBLANK(E2:E11)
// Kitne employees ki salary missing?

// Check data health:
=IF(COUNTBLANK(A2:G11)=0, "Complete", "Missing Data")

// Count text
values only:
=COUNTA(range) - COUNT(range)
// Non-empty minus numbers = text count

// Count unique
values (Excel 365):
=COUNTA(UNIQUE(B2:B11))
// Number of unique names

📊 COUNT Functions Comparison:

💬 Interview Questions:

🔍 Definition: MEDIAN returns the middle value when data is sorted. MODE returns the most frequently occurring value. STDEV measures how spread out the data is from the mean. These are core statistical measures for data analysis.

🎯 Samjho Simple Bhasha Mein: AVERAGE outliers se affect hota hai — 1 rich person poore team ka average shift kar deta hai. MEDIAN safer hai — middle value hai, outliers se safe. MODE sabse common value dikhaata hai. STDEV data ki variability batata hai — sab similar hain ya bahut different? Salary analysis, quality control, survey data — sab mein kaam aate hain.

💡 Syntax:

=MEDIAN(range) → Middle value
=MODE(range) → Most frequent value
=STDEV(range) → Standard deviation

Modern versions:
MODE.SNGL → Single most common
MODE.MULT → Multiple modes (array)
STDEV.S → Sample std dev (recommended)
STDEV.P → Population std dev

💻 Real-World Examples:

// MEDIAN — Middle salary: =MEDIAN(E2:E11) // 10 salaries sorted: 45K,
48K, 51K, 53K, 68K, 71K, 75K, 85K, 92K, 95K // Middle two: (68K+71K)/2 = 69500
// MEDIAN vs AVERAGE — outlier effect:
=AVERAGE(E2:E11) // 68300
=MEDIAN(E2:E11) // 69500
// Close because no extreme outliers

// If someone earned 500K (outlier):
// AVERAGE jumps to ~110K, MEDIAN stays ~70K
// MEDIAN better represents "typical" employee

// MODE — Most common age:
=MODE(G2:G11) // Most repeated age
=MODE.SNGL(G2:G11) // Same, modern version

// Most common city:
// MODE only works with numbers!
// For text mode, use INDEX+MATCH+COUNTIF:
=INDEX(D2:D11, MATCH(MAX(COUNTIF(D2:D11, D2:D11)), COUNTIF(D2:D11, D2:D11), 0))

// STDEV — Salary variability:
=STDEV(E2:E11) // ~17500
=STDEV.S(E2:E11) // Sample std dev (modern)

// Coefficient of Variation:
=STDEV(E2:E11)/AVERAGE(E2:E11)*100
// % variability
from mean
// 30% = high variability

// Employees within 1 std deviation:
=COUNTIFS(E2:E11, ">="&AVERAGE(E2:E11)-STDEV(E2:E11),
E2:E11, "
// Normal distribution: ~68% within 1 stdev

// Outlier detection (beyond 2 std dev):
=IF(ABS(E2-AVERAGE(E$2:E$11))>2*STDEV(E$2:E$11), "Outlier", "Normal")

📊 When to Use Which?

⚠️ Common Mistakes:

• Mistake: Salary analysis mein AVERAGE use karna → Highly paid CEOs skew results.
  Fix: MEDIAN use karo for "typical" salary — more representative.
• Mistake: MODE text values pe use karna → MODE only works with numbers!
  Fix: Text mode ke liye INDEX+MATCH+COUNTIF combination use karo.
• Mistake: STDEV vs STDEV.P confuse karna → S = sample, P = population.
  Fix: Usually STDEV.S use karo (we have sample data). STDEV.P only if you have entire population.

💬 Interview Questions:

🔍 Definition: PERCENTILE returns the value at a specified percentile (0-100). QUARTILE returns the value at a specific quarter (0, 25th, 50th, 75th, 100th percentile). Essential for salary bands, grading systems, and understanding data distribution.

🎯 Samjho Simple Bhasha Mein: PERCENTILE batata hai "kaunsi value pe kitna % data neeche hai." 75th percentile matlab 75% employees isse kam kamate hain. Quartiles data ko 4 equal parts mein divide karte hain — Q1 (25%), Q2 (50% = median), Q3 (75%), Q4 (100%). Salary bands, performance ratings, exam scores — sab mein use hote hain.

💡 Syntax:

=PERCENTILE(array, k) → k between 0-1
=QUARTILE(array, quart) → quart 0-4

Quartiles:
0 → Minimum
1 → 25th percentile (Q1)
2 → 50th percentile (Median)
3 → 75th percentile (Q3)
4 → Maximum

Modern: PERCENTILE.INC, PERCENTILE.EXC, QUARTILE.INC, QUARTILE.EXC

💻 Real-World Examples:

// PERCENTILE — Salary percentiles: =PERCENTILE(E2:E11, 0.25) 
// 25th percentile: ~52000 =PERCENTILE(E2:E11, 0.5) 
// 50th (median): 69500 =PERCENTILE(E2:E11, 0.75) 
// 75th percentile: ~87500 =PERCENTILE(E2:E11, 0.9) 
// 90th percentile: ~92300

// QUARTILE — Same but simpler:
=QUARTILE(E2:E11, 1) 
// Q1: ~52000
=QUARTILE(E2:E11, 2) 
// Q2 (Median): 69500
=QUARTILE(E2:E11, 3) 
// Q3: ~87500
=QUARTILE(E2:E11, 0) 
// Min: 45000
=QUARTILE(E2:E11, 4) 
// Max: 95000

// Salary Band Classification:
=IF(E2>=PERCENTILE($E$2:$E$11, 0.75), "Top 25%",
IF(E2>=PERCENTILE($E$2:$E$11, 0.5), "Above Median",
IF(E2>=PERCENTILE($E$2:$E$11, 0.25), "Below Median", "Bottom 25%")))

// Bonus based on percentile:
=IF(E2>=PERCENTILE(E$2:E$11, 0.9), E20.20, E20.10)

// Top 10% get 20% bonus, rest 10%

// IQR (Interquartile Range):
=QUARTILE(E2:E11, 3) - QUARTILE(E2:E11, 1)

// Range of middle 50% data

// Outlier detection using IQR:
=IF(OR(E2  QUARTILE(E$2:E$11,3)+1.5*(QUARTILE(E$2:E$11,3)-QUARTILE(E$2:E$11,1))),
"Outlier", "Normal")

// Employee's percentile position:
=PERCENTRANK(E$2:E$11, E2) * 100

// Rahul at percentile 60 = better than 60% employees

📊 Salary Distribution:

💬 Interview Questions:

🔍 Definition: FREQUENCY counts how many values in a range fall within specified intervals (bins). Perfect for creating histograms, salary bands analysis, and understanding data distribution shape. Returns an array of counts.

🎯 Samjho Simple Bhasha Mein: FREQUENCY se histogram banate hain — kitne log 40-50K mein hain, kitne 50-60K mein, kitne 60-70K mein. Data ko groups (bins) mein divide karke count nikalta hai. Sales distribution, marks distribution, age groups — sab visualize karne mein help karta hai. Array formula hai — special way se enter karna padta hai.

💡 Syntax:

=FREQUENCY(data_array, bins_array)

data_array: Values to analyze
bins_array: Upper bounds of intervals

Entry (Old Excel): Select output range → type formula → press Ctrl+Shift+Enter (array formula)

Excel 365: Just press Enter (dynamic array)

💻 Real-World Examples:

// Setup — Create salary bins: // Column J: Bin upper limits // J2: 50000 (0-50K) // J3: 60000 (50-60K) // J4: 70000 (60-70K) // J5: 80000 (70-80K) // J6: 90000 (80-90K) // J7: 100000 (90-100K)
// Apply FREQUENCY (Excel 365 — dynamic array):
=FREQUENCY(E2:E11, J2:J7)

// Old Excel — Select K2:K8, type formula, Ctrl+Shift+Enter:
{=FREQUENCY(E2:E11, J2:J7)}

// Result — Salary distribution:
// K2: 2 (up to 50K) → Kavita(45K), Amit(48K)
// K3: 2 (50-60K) → Suresh(51K), Ravi(53K)
// K4: 1 (60-70K) → Deepak(68K)
// K5: 2 (70-80K) → Neha(71K), Rahul(75K)
// K6: 1 (80-90K) → Priya(85K)
// K7: 2 (90-100K) → Sneha(92K), Anjali(95K)
// K8: 0 (above 100K)

// Age distribution:
// Bins: 25, 30, 35, 40
=FREQUENCY(G2:G11, {25;
30;
35;
40})

// Alternative — COUNTIFS for single bin:
=COUNTIFS(E2:E11, ">=50000", E2:E11, "
// Same as one FREQUENCY bin — but simpler for single bin

// Percentage in each bin:
=FREQUENCY(E2:E11, J2:J7) / COUNT(E2:E11) * 100

// Cumulative count:
// After FREQUENCY results, use running SUM

📊 Salary Distribution Histogram:

⚠️ Common Mistakes:

• Mistake: Old Excel mein Ctrl+Shift+Enter na dabaana → Array formula work nahi karega.
  Fix: Excel 365 mein direct Enter. Older versions mein CSE array formula.
• Mistake: Bins array wrong order mein — ascending nahi.
  Fix: Bins hamesha ascending order mein hone chahiye — 50K, 60K, 70K, etc.
• Mistake: Output range bin array se 1 chhota select karna → Last bucket miss ho jaayega.
  Fix: Output range = bins count + 1 (for "above last bin" bucket).

💬 Interview Questions:

🎯 Statistical Analysis — Quick Formulas

Mean: =AVERAGE(range)

Median: =MEDIAN(range)

Mode: =MODE.SNGL(range)

Range: =MAX(range)-MIN(range)

Std Dev: =STDEV.S(range)

IQR: =QUARTILE(range,3)-QUARTILE(range,1)

Top 3 avg: =AVERAGE(LARGE(range, {1,2,3}))

Percentile: =PERCENTILE.INC(range, 0.75)

Next: Data Insights Excel Masterclass — Part 7

Part 7 mein hum cover karenge: Data Tools & Advanced Features — Pivot Tables, Pivot Charts, Conditional Formatting, Data Validation, What-If Analysis, Named Ranges, Dynamic Arrays, Power Query, Sparklines — Excel ke advanced tools Data Insights par.

Happy Learning & Keep Excelling! 🚀