| Concept | Description |
|---|---|
| Building Formulas | |
| Formula Anatomy and Operator Precedence | Formulas open with =, and parentheses, exponent, multiply and divide, add and subtract, then comparison resolve in that order |
| Aggregation Functions | SUM, AVERAGE, MEDIAN, MIN, MAX, STDEV.S and VAR.S over a range or a Table column |
| Sample versus Population Standard Deviation | STDEV.S divides by n minus 1 for a sample; STDEV.P divides by n for a full population |
| The Four Counting Functions | COUNT for numbers, COUNTA for anything, COUNTBLANK for gaps, ROWS for table size |
| Conditional Aggregation | |
| SUMIF, COUNTIF, AVERAGEIF | Aggregate a range subject to one condition, with the sum range given last |
| SUMIFS, COUNTIFS, AVERAGEIFS | The same with several conditions, with the aggregate range given first instead |
| Criteria Syntax and Wildcards | Comparisons and wildcards go inside quotes; cell-based comparisons need the operator concatenated |
| Logic and Numbers | |
| IF, Nested IF, and IFS | Two-outcome tests, nested tests, and the flatter IFS form with a TRUE catch-all |
| AND, OR, NOT, and IFERROR | Combining conditions, and replacing an error with a usable value |
| Rounding versus Formatting | ROUND changes the stored value; number formatting changes only the display |
| Rank, LARGE, SMALL, and Percentiles | RANK.EQ, LARGE, SMALL, PERCENTILE.INC and QUARTILE.INC need absolute comparison ranges |
| Text, Dates, and Errors | |
| Text Functions | TRIM, LEN, LEFT, RIGHT, MID, FIND, SUBSTITUTE, TEXTJOIN, and the modern TEXTBEFORE family |
| Date Functions | TODAY, DATE, YEAR, EOMONTH, EDATE, NETWORKDAYS, and direct arithmetic on date serial numbers |
| Excel Error Values | What each of DIV/0, N/A, VALUE, REF, NAME, NUM and SPILL indicates and how to resolve it |
13 Essential Formulas and Functions
Excel ships with well over 500 functions. An analyst uses perhaps thirty of them daily, and the conditional aggregation family alone (SUMIFS, COUNTIFS, AVERAGEIFS) answers most questions a cooperative actually asks of its data.
Every formula in this section runs against the FarmData sheet, whose columns are laid out as follows. Keep this to hand, because the rest of the topic refers to these letters constantly.
| Column | Field | Type |
|---|---|---|
| A | FarmID |
Text |
| B | District |
Text |
| C | CropCode |
Text |
| D | AreaHa |
Number |
| E | Irrigation |
Text |
| F | FertilizerSpend |
Number, thousand rupees per hectare |
| G | Rainfall |
Number, mm |
| H | NDVI |
Number, 0 to 1 |
| I | SoilMoisture |
Number, percent |
| J | YieldQtPerHa |
Number, quintals per hectare |
| K | Status |
Text, Healthy or Stressed |
Data occupies rows 2 to 61.
13.1 How a Formula Is Built
Every formula opens with =. Without it, Excel stores what was typed as text and displays it literally.
=(F2 + G2/100) * D2Operators resolve in a fixed order: parentheses, then exponent ^, then * and /, then + and -, then comparisons (=, <>, <, >, <=, >=). Same-precedence operations run left to right. When in doubt, add parentheses; they cost nothing and they document intent for whoever reads the sheet next.
The ampersand joins text: =B2 & " / " & C2 returns Guntur / PDY.
Three habits prevent most formula pain. Build the formula in one cell and check it against a value you can verify by hand before filling it down. Use F2 to see which cells a formula actually references, since Excel colour-codes them in place. And when a long formula misbehaves, select any fragment of it in the formula bar and press F9 to evaluate just that piece.
13.2 Aggregation
=SUM(J2:J61) Total of all yields
=AVERAGE(J2:J61) Arithmetic mean
=MEDIAN(J2:J61) Middle value, resistant to outliers
=MIN(J2:J61) Smallest
=MAX(J2:J61) Largest
=STDEV.S(J2:J61) Sample standard deviation
=VAR.S(J2:J61) Sample varianceWith the range converted to a Table, the same formulas read far better:
=SUM(FarmData[YieldQtPerHa])
=AVERAGE(FarmData[YieldQtPerHa])
=STDEV.S(FarmData[YieldQtPerHa])STDEV.S and VAR.S treat the data as a sample and divide by \(n-1\). Their counterparts STDEV.P and VAR.P divide by \(n\) and assume the data is the entire population. For 60 surveyed farms out of a district’s several thousand, STDEV.S is correct. The distinction is the same one drawn in Measures of Dispersion.
13.2.1 Counting
Four count functions exist because “how many” has four different meanings.
=COUNT(J2:J61) Cells containing numbers
=COUNTA(A2:A61) Cells containing anything at all
=COUNTBLANK(J2:J61) Empty cells
=ROWS(FarmData) Rows in the Table, filled or notCOUNT against COUNTA on the same column is a fast data quality check. If COUNT(J2:J61) returns 58 while COUNTA(J2:J61) returns 60, two yield values are stored as text and every numeric function on that column is quietly ignoring them.
13.3 Conditional Aggregation
This is the family that earns its keep. Each function takes a range to test, a condition, and (for the IF forms with a separate sum range) a range to aggregate.
Single condition
=SUMIF(B2:B61, "Guntur", J2:J61) Total yield in Guntur
=COUNTIF(K2:K61, "Stressed") How many stressed farms
=AVERAGEIF(E2:E61, "Rainfed", J2:J61) Mean yield on rainfed farmsMultiple conditions, where the aggregate range comes first and the criteria pairs follow:
=SUMIFS(J2:J61, B2:B61, "Guntur", C2:C61, "PDY")
=COUNTIFS(K2:K61, "Stressed", E2:E61, "Rainfed")
=AVERAGEIFS(J2:J61, C2:C61, "PDY", G2:G61, ">=700")Note the argument order flips between the two families. SUMIF puts the sum range last; SUMIFS puts it first. This catches people out permanently, and the only cure is to notice the S.
Comparison and wildcard criteria go inside quotes:
=COUNTIFS(J2:J61, ">=40") Yields of 40 or more
=COUNTIFS(G2:G61, ">=600", G2:G61, "<800") Rainfall between 600 and 800
=COUNTIF(B2:B61, "K*") Districts starting with K
=COUNTIF(A2:A61, "F0??") FarmIDs of exactly that shapeTo compare against a value held in a cell rather than typed in, concatenate the operator with the reference:
=COUNTIF(J2:J61, ">" & N2)=COUNTIF(J2:J61, ">N2") would search for the literal text >N2 and return zero, with no warning that anything is wrong.
13.4 Logical Functions
IF takes a test, a result when true, and a result when false.
=IF(J2 >= 40, "Above target", "Below target")Nesting handles more than two outcomes, though it gets unreadable fast:
=IF(J2>=45, "High", IF(J2>=35, "Medium", "Low"))On Excel 2019 and later, IFS reads better and avoids the bracket-counting:
=IFS(J2>=45, "High", J2>=35, "Medium", TRUE, "Low")The final TRUE acts as the catch-all. Without it, a value below 35 returns #N/A.
AND, OR, and NOT combine tests:
=IF(AND(H2>0.55, I2>25), "Healthy", "Check field")
=IF(OR(E2="Canal", E2="Drip"), "Assured water", "Weather dependent")
=IF(NOT(K2="Stressed"), 1, 0)IFERROR replaces an error with something useful, and belongs around any lookup or division that might legitimately fail:
=IFERROR(J2/D2, "")
=IFERROR(VLOOKUP(C2, CropLookup!$A$2:$D$9, 2, FALSE), "Unknown crop")Use it deliberately. Wrapping every formula in IFERROR hides faults rather than handling them, and a sheet full of blanks where errors used to be is harder to debug, not easier.
13.5 Rounding
=ROUND(J2, 1) Two-way rounding to one decimal
=ROUNDUP(J2, 0) Always away from zero
=ROUNDDOWN(J2, 0) Always toward zero
=INT(J2) Largest integer not greater than the value
=MROUND(F2, 0.5) To the nearest halfRounding changes the stored value. Cell formatting changes only what is displayed, leaving the full precision underneath. A column formatted to one decimal still sums at full precision, which is why a displayed column of one-decimal figures can appear not to add up to its own total. When a report has to reconcile exactly, round with ROUND rather than with formatting.
13.6 Rank and Position
=RANK.EQ(J2, $J$2:$J$61, 0) Rank, 0 for descending
=LARGE($J$2:$J$61, 3) Third highest yield
=SMALL($J$2:$J$61, 3) Third lowest yield
=PERCENTILE.INC($J$2:$J$61, 0.9) 90th percentile
=QUARTILE.INC($J$2:$J$61, 1) First quartile
=MODE.SNGL($C$2:$C$61) Most frequent valueThe absolute references on the full range matter here. Copied down a column with relative references, the comparison range would shift row by row and every rank below the first would be computed against a different, shrinking set of values.
Quartiles feed straight into the interquartile range used for outlier detection, and the same quantities appear in R through quantile() and IQR(), covered in Measures of Dispersion.
13.7 Text Functions
Text functions matter mostly for cleaning, which is why the next section leans on them heavily. The core set:
=TRIM(B2) Strip leading, trailing, and repeated inner spaces
=UPPER(B2) =LOWER(B2) =PROPER(B2)
=LEN(B2) Character count, useful for spotting stray spaces
=LEFT(A2, 1) First character
=RIGHT(A2, 3) Last three characters
=MID(A2, 2, 3) Three characters starting at position 2
=FIND("-", A2) Position of a character, case sensitive
=SEARCH("-", A2) Same, case insensitive, accepts wildcards
=SUBSTITUTE(B2, " ", " ") Replace one string with another
=CONCAT(B2, " / ", C2) Join
=TEXTJOIN(", ", TRUE, B2:C2) Join with a delimiter, skipping blanksOn Microsoft 365, splitting text got much easier:
=TEXTBEFORE(A2, "-") Everything before the first hyphen
=TEXTAFTER(A2, "-") Everything after it
=TEXTSPLIT(A2, "-") Split into separate cellsThe legacy equivalents, which work in every version:
=LEFT(A2, FIND("-", A2) - 1) Before the hyphen
=MID(A2, FIND("-", A2) + 1, LEN(A2)) After the hyphenLEN is worth a mention on its own. A district that looks like Guntur but returns =LEN(B2) of 7 has a trailing space, and every lookup and every COUNTIF treating it as Guntur will fail to match. That failure is silent: COUNTIF returns a count that is simply too low.
13.8 Date Functions
=TODAY() Current date, updates on recalculation
=NOW() Current date and time
=DATE(2026, 9, 16) Build a date from parts
=YEAR(L2) =MONTH(L2) =DAY(L2)
=EOMONTH(L2, 0) Last day of that month
=EOMONTH(L2, 1) Last day of the following month
=EDATE(L2, 3) Same day three months on
=NETWORKDAYS(L2, M2) Working days between two dates
=WEEKDAY(L2, 2) Day number, 2 starts the week on MondaySince dates are serial numbers, arithmetic works directly: =M2-L2 gives the number of days between sowing and harvest, and =L2+120 gives the expected harvest date for a 120-day variety.
DATEDIF survives from Lotus 1-2-3 and is absent from Excel’s function list, but still works:
=DATEDIF(L2, M2, "d") Days between
=DATEDIF(L2, M2, "m") Complete months between
=DATEDIF(L2, M2, "y") Complete years between13.9 Reading Excel’s Error Values
Errors name their own cause once the vocabulary is familiar.
| Error | Cause | Usual fix |
|---|---|---|
#DIV/0! |
Division by zero or by an empty cell | Wrap in IFERROR, or test the denominator first |
#N/A |
A lookup found no match | Check for trailing spaces and for text-versus-number mismatch |
#VALUE! |
Wrong argument type, often arithmetic on text | Find the text cell in the range, often a number stored as text |
#REF! |
The referenced cell no longer exists | Caused by deleting rows or columns a formula pointed at |
#NAME? |
Unrecognised function or name | A typo, or a modern function on an older Excel version |
#NUM! |
Numerically impossible | Square root of a negative, or a value too large to represent |
#SPILL! |
A dynamic array has no room to expand | Clear the cells below or to the right of the formula |
##### |
Not an error | The column is too narrow. Widen it |
#NAME? on XLOOKUP, FILTER, SORT, UNIQUE, TEXTSPLIT, or LET almost always means the file is open in Excel 2019 or earlier. Each of those has a legacy equivalent, given alongside it wherever this topic uses one.