Formulas by task

1043 tasks

Choose a category or search for a task. Open a formula to see sample data, its result, and how it works. Related functions explain syntax and arguments.

Excel function list →

1043 / 1043 Formula

Count 60

Count
FormulaRelated Functions
Counting negative numbers in one column
Counting rows with a wildcard pattern
Counting how many rows are filled of the total
Counting rows still visible after filtering
Counting the number of days in a month
Counting working days between two dates
Counting items in a comma-separated list
Counting the words in one cell
Counting how often a word appears in a text
Counting how many different values there are
Counting values that appear exactly once
Counting how many people reached the top score
Count cells with values between two numbers
Count rows with dates between two dates
Counting cells that contain numbers
Count cells that hold text rather than numbers
Counting cells that match partially
Counting cells above the average
Counting cells that meet two criteria at once
Count how many cells are still empty
Counting cells above a target held in another cell
Counting cells that meet one condition
Counting cells that meet either of two criteria
Counting cells that are not empty
Cells holding numbers against cells merely filled
Count cells holding a word anywhere inside them
Count how many different cities sit in one column
Count invoices from 1 to 3 million, ends included
Count rows meeting a city and a revenue test at once
Count deposit cells that are blank, not worth zero
Count the invoices issued in one single year
Count one month's sales with two real date bounds
Count activities that land on a Saturday or Sunday
Count how many different days had transactions
Count city-and-item pairs that really exist
Count deposits still blank in one city only
Count staff whose two deposit cells are both empty
Count items whose stock disagrees with the record
Count codes that start with a prefix, not hold it
Count phone numbers exactly 12 digits long
Count the values that land in one histogram bin
Count rows from two cities that clear one target
Count how many shipping weights are whole numbers
Count codes whose upper and lower case match exactly
Count notes with the word batal, upper or lower case
Count distinct customers in a city, not their orders
Count rows still missing a phone number or address
Count serials without mixing in lower-case variants
Count codes of the exact length, not just the prefix
Filter March invoices with no other year creeping in
Count holidays that really land on a working day
Count stock gaps outside tolerance in both directions
Count how many names are registered more than once
Count items that never appear on a single invoice
Count cells that look empty but hold only spaces
Count distinct routes from origin and destination
Count items whose stock has hit the reorder point
Count the exact status, not the ones containing it
Count how many quantity cells are stored as text
Count how often one word appears across a column

Sum 51

Sum
FormulaRelated Functions
Summing only the positive numbers
Summing values between two dates
Adding up several separated ranges
Sum numbers that meet two conditions at once
Summing with criteria taken from another cell
Summing numbers that meet one condition
Adding up two categories at once
Summing on a text condition and a number condition
Summing numbers greater than a threshold
Adding up values above the average
Summing quantity times price row by row
Computing 11 percent PPN on selected items
Adding up numbers within a single row
Summing every number in one column
Summing everything except one category
Total sales after a per-row discount
Closing stock from goods in and goods out
Summing scores without the highest and lowest
Adding up the last three filled rows
Summing the three largest values
Building a running total that grows each row
Add up codes with the prefix in front, not inside
Add up services whose name ends in one word
Add up rows whose name holds a literal asterisk
Add up only the rows whose name column is filled
Add up one month in one particular year only
Total one quarter from a column of dates
Add up revenue from Monday to Friday only
Add up several cities at once from a list
Add up deposits while skipping the error cells
Add up claims whose code matches down to the case
Add three month columns at once for one city
A running total per city anchored at the first row
Add up only every third row in a list
Add up numbers written inside a single cell
The number that decides if a summary sums or averages
Add up the sales that exactly reach the bonus target
Add up values above a limit kept in another cell
Add up sales of item codes that share a prefix
Add up the values from two categories at once
Add up only the invoices already marked as paid
Add up one month's sales from a column of dates
A running cash balance from money in and money out
A running total that restarts from zero each month
The cumulative share of the top three products
The balance just before the transaction on that row
Units still waiting to be dispatched as at that row
A cross-table grand total without counting twice
Add up one item across the whole three-branch list
Totalling one item when blocks have unequal rows
The gap between the cash book total and the statement

Average 33

Average
FormulaRelated Functions
Finding the value that appears most often
Finding the middle value when one number is extreme
Averaging without counting the zeros
Averaging without being thrown by empty cells
Calculating a three-day moving average
Averaging within one date range
Averaging with two criteria at once
Averaging only the values above a threshold
Showing an average as a whole number
Average speed over a return journey
Averaging percentages correctly
Computing average growth per year
Averaging just one branch
Average after the highest and lowest are dropped
Averaging with text cells counted as zero
Averaging after trimming 20 percent of the data
Final score from the weight of each component
Averaging the three highest scores
Averaging branch revenue when one cell holds an error
Averaging profit only on days that really earned
A class average without the pupils who missed the exam
A weighted average price for one branch alone
Combining branch averages in proportion to their bills
Average spend per customer rather than per invoice
Averaging the last three months of a monthly list
Averaging two cities at once inside one formula
Averaging the prices of codes sharing one prefix
Averaging the marks that reach a threshold in a cell
Averaging one month's sales without guessing serials
Measuring how far visitor numbers stray from the mean
Averaging only the figures above the team average
A sentence instead of an error when a city has no data
Averaging every city except the one left out

Dynamic array 54

Dynamic array
FormulaRelated Functions
Taking two rows in the order requested
Reading a duty roster column by column
Adding up without the opening balance and total rows
Looking up an item code from its name
A list of destination cities with no repeats
Counting distinct people across two lists
Summing sales that pass two conditions
Summing the first and the last column
Adding up week two's values from a long list
Collecting the distinct categories from one block
Checking random numbers fall in the dice range
Average visitors over the last four days
Finding the top score among active members
Taking customers from one city only
Marking quota slots that are still unfilled
Summing a penalty that rises every day
Sorting names by a value in another column
Sorting participant names alphabetically
Listing distinct cities from a column that repeats
Showing the cities that appear exactly once
Sorting city names by turnover instead of alphabetically
Filtering cities whose turnover clears a threshold
Taking the three largest turnover figures in order
Generating a running number down a list of names
Filtering rows from two cities in a single formula
Filtering names that contain a fragment anywhere
Filtering codes with upper and lower case kept apart
Filtering prices that sit between two limits
Show your own message when a filter finds nothing
Take every third row from a list of daily takings
Showing the three highest figures with their owners
Sorting by branch first, then by the largest turnover
Sorting codes by their trailing number, not their letters
List distinct cities without counting empty cells
Count distinct customers at one branch alone
List a debt balance falling by a fixed amount
Take the last two transactions that pass the filter
Skipping the heading rows above a table's data
Reordering report columns without moving any data
Last year's customers who did not order again
Finding item codes entered more than once
Filtering the qualifiers and sorting them by mark
A per-city recap without building a pivot table
The city with the biggest total, not the biggest bill
Total interest when the principal falls every month
Renumbering from one after the rows are filtered
Counting pairs of items that reach free delivery
A per-division report with the highest points on top
Filtering invoices that are large or from key accounts
Take the top two sales levels without tripping on ties
Counting free tables despite a double booking
A deposit schedule falling every ten days
Ranking each pupil inside their own class
List the items that have hit their reorder point

Min and max 34

Min and max
FormulaRelated Functions
Counting how many times the top score appears
The gap between the first and second highest scores
Keeping a bonus between a floor and a ceiling
Finding the product with the second-highest sales
Finding the supplier with the cheapest price
Finding the largest transaction in one month
Highest revenue for one branch in one month
Finding the highest sales in one city only
Finding the second highest score
Finding the highest value when a cell has an error
Finding the smallest stock among the branches
Finding the lowest score in one class
Finding the third fastest travel time
Finding the smallest number that is not zero
Finding the highest value across two columns
Finding the highest price that fits the budget
Finding the highest sales figure
The gap between the highest and lowest temperature
The largest figure for one city, not the whole column
Top revenue once returned invoices are excluded
The smallest cash surplus, rather than the zero ones
The third cheapest quote, ignoring the blank ones
The price spread inside one single category
Pick the priciest package a budget can still afford
The smallest basket that still earns free shipping
Name the branch with top revenue in one region
The second largest deposit despite error cells
Checking a week's temperature swing against 3 degrees
Taking the last name that reached the top score
Highest price among codes that share one prefix
Finding the month that strayed furthest from target
The highest score reached by more than one person
The second highest quote from one vendor only
Find the branch whose queue is the least stable

Lookup 71

Lookup
FormulaRelated Functions
Taking a column named by its header
Taking a value from the column on the left
Taking the last value in one column
Taking the whole row that was found
Gathering every matching name into one cell
Looking up through two related tables
Finding the last matching row from the bottom
Searching a second table when the first is empty
Looking up a price from a product code
Finding the name behind the highest value
Finding a value where a row and a column cross
Look up a value matching two criteria at once
Looking up a value by a horizontal column heading
Looking up a value by part of a name
Knowing a name's position number in a list
Setting commission from a tiered rate table
Finding a rate by rounding up to the next tier
Checking whether a name is in a list
Showing a message when the data is not found
Deciding a category from an item code prefix
Turning a number code into a grade label
Find stock from product name and size together
Take one branch's revenue for a particular month
Find one city's revenue for one month in a cross table
Look up a code that is sensitive to letter case
Pick the pipe size closest to what is needed
Find the product name with the most characters
List entrants missing from the attendance sheet
Search on part of a name, not the whole name
Return the second match instead of the first one
Report the address of a lookup hit, not its contents
Find the first number in a column of mixed text
Find the first month that turned a loss
Find the first row matching either of two values
Look up a rate in a descending upper-bound table
Add up the column picked out by its heading
Pick the rental capacity closest to the load
Charging delivery from a banded weight table
The smallest data plan that still covers the need
The nearest rate when the threshold table is unsorted
Set a tiered discount from the quantity ordered
Bill a rental at the next duration package up
A delivery charge read from a weight-by-distance grid
Pick the dearest item that still fits the budget
Take the newest purchase price, not the oldest one
Find the last occurrence of a tied highest value
Find the last month whose figures have arrived
The last deposit of one month from a mixed list
A not-found message that swallows no other error
Telling a zero apart from a number never registered
Telling an empty cell apart from a name that is missing
Take a value from the column left of the key
Read the unit price from a tiered quantity table
Showing a message of your own when a code is missing
The last figure entered when the bottom cell is empty
Finding the latest record of a code used twice
Read a rate from a city-by-service crosstab
Find who is on duty for a given day and shift
Reading freight cost from an origin-destination grid
The cheapest supplier for one item in a price grid
Match a heading that carries a year on the end
Shipping cost when the weight sits right on a threshold
Find the price in force on the transaction date
The roll whose length comes closest to what is needed
A freight rate from two axes at once: weight and zone
A grade for a score below the very lowest threshold
Look up a price by a joined product-and-size key
Filter entrants who meet two conditions at once
Filter staff from two branches at the same time
Take a customer's second transaction, not the first
Check two values ever met on the very same row

Logic 40

Logic
FormulaRelated Functions
Comparing two codes letter by letter
Telling an empty cell from a zero
Giving a discount if either condition is met
Replacing a divide-by-zero error with your own message
Giving a letter grade from score ranges
Making sure only one option is ticked
Checking for numbers stored as text
Checking a temperature is within a sane range
Showing a message when a code is not listed
Picking a label from a rank number
Choosing a shipping rate by parcel weight
Marking stock as Habis, Menipis, or Aman
Flagging invoices that need a second check
Flagging names that appear more than once
Flagging submissions past the deadline
Flagging orders that are not complete
Deciding pass or remedial from an exam score
Setting a tiered discount percentage
Accepting an applicant only if both conditions hold
Turning a branch code into a city name
Pass only when two conditions hold at the same time
Passing when either one of two conditions is met
Invert a condition so it reads the other way round
Assigning a grade from several ordered thresholds
Swapping an errored result for a sentence of your own
Setting a penalty rate by how late the return is
Setting a commission rate from target attainment
Free shipping for members or big enough baskets
Group ticket fares by the passenger's age
Labelling delivery status including the slowest case
Deciding a pass from marks and attendance together
Flagging a thin margin without dividing by zero sales
Promise a ticket response time by its urgency
Approving credit when two of three rules are met
Price a print job by its size and paper type
Overtime pay with both a floor and a ceiling on hours
Separate the not-yet-replied from those who declined
Checking that not one subject has fallen below the line
Checking an item code's prefix and length at once
Naming which mistake got a form rejected first

Grouping 29

Grouping
FormulaRelated Functions
Splitting scores into three tiers
Counting the city and month combinations present
Listing cities without repetition
Counting the orders for one product only
Counting the data that falls in one range
Counting projects that fall in one quarter
Finding the city with the largest sales total
Summing the sales of one month
Working out one group's share of the total
Average score for one class
The total for one city in one month
What the largest group total comes to
Summing the sales of one city
Turning a number into a range label
Placing a participant's age into a band table
Count items in one price band with no double edge
Writing a five-year age band label from one age
Raise a price into its five-thousand band
How many ten-year age bands are actually in use
Which age band holds the most members
The top sales figure inside one price band only
Turn a numeric score into a letter grade by table
Which price band brings in the largest revenue
Counting item codes by their three-letter prefix
Average spend in one age band, not everything above
How many pupils land in the top quarter of a class
One label joining a city with an age band
Which age band has no members in it at all
Sorting ribbon labels by level rather than by alphabet

Conditional formatting 29

Conditional formatting
FormulaRelated Functions
Highlight rows where one of the columns is empty
Banding alternate rows by their row number
Highlighting rows that contain a given word
Highlight invoices already past their due date
Highlighting required fields still left empty
Highlighting values above their column average
Highlight temperatures outside a reasonable range
Highlighting values that appear more than once
Highlighting values that appear exactly once
Highlight results that have not reached target
Highlighting gaps between physical and system stock
Highlighting stock below its minimum level
Highlight dates that fall on the weekend
Highlighting the three largest values in a column
Highlighting the second appearance, not both of them
Spotting double entries from name and date together
Finding twin names that differ only in letter case
Marking a code read twice in a row, not twice in all
Marking a shipping code that is on the recall list
Ruling a guide line every five rows of data
Marking the first row of each group in sorted data
Finding an invoice number that skips in the sequence
Highlighting the week ahead without dragging in the past
Counting the time left in working days, not calendar
Marking deadlines on holidays, not just on weekends
Highlighting invoices due soon that are still unpaid
Highlighting contracts that end in the current month
Making the warning fire exactly on its threshold day
Setting the warning distance by the job's priority

Data validation 28

Data validation
FormulaRelated Functions
Accepting numbers between two bounds, inclusive
Accepting only quantities in multiples of twelve
Accepting only codes that begin with BRG
Accepting whole numbers only, no fractions
Accepting only numbers, not text that looks numeric
Rejecting an asset code that is already in use
Rejecting an ID number that is not 16 digits
Accepting codes that exist in a reference list
Rejecting an email address without an @ sign
Accepting only dates that fall on a working day
Rejecting dates before a set cutoff
Rejecting a user name that contains a space
Rejecting a code of the right length but the wrong shape
Making sure a subcategory belongs to its own category
Showing the cities that belong to the chosen province
Building a rack list for one warehouse with no repeats
Checking an address down to all three region levels
Accepting the first invoice code and rejecting its copy
Flagging attendance twins on the same name and date
Counting how many item codes are used more than once
Listing duplicate serial numbers without naming them twice
Keeping referral codes unique right down to letter case
Measuring a note's length without its edge spaces
Allowing exactly one of the two contact columns
Matching catalogue and invoice names that space differently
Checking that total demand still fits the remaining stock
Checking a supplier code that carries an unseen space
Listing proposed codes not yet used in the old list

Rank 30

Rank
FormulaRelated Functions
The third quartile boundary of a distribution
Finding which decile a score falls into
Breaking a tied rank with a second column
Finding the name holding third rank
The threshold value at the 80th percentile
The 25th percentile the exclusive way
Ranking by the sum of two score columns
Sales rank within a salesperson's own region
Ranking product names in alphabetical order
Ranking values from the highest down
A value's position as a percentage of the spread
Average rank for tied scores
Ranking that does not skip after a tie
Ranking race times with the fastest at number one
Ranking times while ignoring zeros
The rank numbers skipped after a tie at the top
How many people share the same top score
Ranking within a branch with units sold as tiebreak
Count who really makes the top five when scores tie
Adding the three best turnovers from one branch only
The agent with the top premium in one region
Ranks with no duplicates, split by alphabetical order
Add up the sales outside the top three branches
Show the top three names without dropping a tie
Ranking a competitor among the active entrants only
How many more points to pass the person just above
Unique rank numbers split by the order of the rows
The name holding the second highest distinct score
Ranking branches by total revenue, not by deal size
The deposit threshold for a city's own top three

Rounding 32

Rounding
FormulaRelated Functions
Taking the integer toward the smaller side
Turning a price into one ending in 900
Rounding work duration to 15-minute steps
Rounding a price to the nearest thousand
Rounding up regardless of the significance sign
Trimming decimals toward zero
Rounding to the next odd number
Rounding to the next even number
Rounding a price to the nearest multiple of 500
Rounding a negative value to the nearest multiple
Rounding negative numbers away from zero
Always rounding away from zero
Rounding an exact half away from zero
Rounding down to the smaller number
Cutting decimals without rounding
Round to the nearest thousand instead of the unit
Round a duration to the nearest fifteen minutes
Work out cartons from an order counted in bottles
Build a shelf price that ends in 990
Round a selling price up to the next 5000
Count buses when the party fits the seats exactly
Round a till total down to a multiple of fifty
Round a large number to three significant figures
Rounding an order up to whole cartons
How many more boxes until the last pallet is full
Round an interest rate to the nearest half percent
Round a price to the nearest multiple of 250
How many units fit inside the purchase budget
Raise a page count to the next multiple of four
End a price in 900 without passing the discount price
Counting the banknotes that must be set aside
Round a cutting size up to a multiple of 5 cm

Date and time 75

Date and time
FormulaRelated Functions
Finding which day of the year a date is
Counting the working days in one month
Working out hours worked from clock in and out
Counting work hours for a shift past midnight
Counting full months between two dates
Working out age in full years
Calculating age in years, months, and days
Showing the month name from a date
Showing the day name for a date
The ISO week number at the turn of the year
Checking whether a year is a leap year
Marking weekends without being fooled by day numbering
Counting the days left until the due date
Finding the 1st of the next month
Finding a due date after so many working days
Finding the Monday of the same week
Finding the date three months earlier
Working out the quarter from a date
Turning total minutes into hours:minutes
Turning a day difference into weeks and days
The finish date ten working days after the start
Adding one month starting from 29 February
How many days February has in a leap year
Count the days between two first-of-month dates
Working days left until the due date
Turning a weekday number into its day name
The 1st of next month inside a leap year
Turn a fraction of a day into hours worked
Counting the whole months that have really gone by
Turning a month number into a calendar quarter
Days elapsed since the most recent birthday
A retirement date computed from a leap-day birthday
Count how many days two periods overlap
Find the working day just before a given date
The 1st of the previous month at the turn of the year
Find the last working day inside a month
Count orders in one week as the year turns over
Padding a week number so the labels sort correctly
Fiscal quarter for a book year that starts in July
Finding the quarter-end date from an invoice date
Count the holiday dates inside one date range
Count net working days after the public holidays
A delivery date for a shop that also opens on Saturday
Count how many Mondays fall inside a date range
Counting interest days on a 30-day month basis
Averaging working hours without the empty days
Holding daily overtime so it never goes negative
Adding up the hours worked beyond eight in a day
A week's pay including the overtime hours on top
Round a clock-in time up to the next 15 minutes
Round a clock-out time down to the last 15 minutes
Turning decimal hours into hours and minutes
Turning a duration in fractions of a day into minutes
Adding durations whose total runs past 24 hours
Measuring clock-in lateness in whole minutes
Count the shift hours falling in the morning block
Turning a service duration into days and hours
Matching a clock-in hour to a shift start table
Total hours on one project from a mixed daily log
Count shifts that start before six in the morning
Overtime once break and core hours come off
Rounding overtime down to the nearest half hour
Overtime at two tiered rates, first hour and the rest
Deciding the shift when clock-in sits on the threshold
Count lateness only beyond the grace period
A week of overtime from clock-in and clock-out times
Subtract only holidays that fall on working days
A due date when the business opens on Saturday too
Count the days left until the next birthday
Express a meeting length in hours and minutes
Checking a rest gap between shifts across midnight
Count night shifts whose hours cross midnight
Pulling a payday forward when it lands on a weekend
Confirm a voter is old enough on polling day
Hours left until a deadline that falls the next day

Date series 29

Date series
FormulaRelated Functions
An installment month series that crosses the year
A fortnightly series that crosses February
Workday series for a shop closed only on Sunday
A series of Mondays through one month
A monthly due-date series from the 31st
Expiry date series every 90 days
The next five working days
A payday series on the last day of the month
Payday on the 25th moved back to a working day
Finding due dates that land on a weekend
The next due date on a fixed day of the month
Working-day due date with one holiday
The start date of the next quarter
The next bond coupon date
Finding the last Friday in a given month
The tenth working day of each month for tax filing
Annual contract reviews starting on 29 February
Four weekly steps that cross into the new year
Count the Saturdays in a month for an overtime rota
Count the shop's open days left before month end
The Monday-to-Sunday week around a single date
An invoice due date nudged to a working day
Last working day of each quarter for the close
Counting back working days across a year boundary
Days in each month for a pro-rata pay calculation
Five dispatch dates, every Rabu after the order
Build six payday dates at mid-month and month end
Which public holidays stretch into a long weekend
Working out the second Monday of each month

Text 52

Text
FormulaRelated Functions
Taking a number out of mixed text
Taking the part between two hyphens
Taking the extension from a file name
Taking the user name from an email address
Comparing two texts down to their letter case
Removing extra spaces from text
Checking whether a text contains a word
Joining an address split across two lines
Joining a list into a single line
Counting words without tripping on an empty cell
Counting how many times a word appears
Trimming a title that is too long
Masking part of a phone number
Inserting a rounded number into a sentence
Padding a code with leading zeros to 5 digits
Turning Indonesian-style number text into a number
Split off an email domain without counting letters
Collapsing double spaces without losing the gap
Splitting a full name into all its parts at once
Take the code prefix that sits before the hyphen
Count how many times one word appears in a cell
Levelling a name whose capitals came out inverted
Take the third word out of an address line
Take the last word without knowing the word count
Drop the extension from a file name full of dots
Take the code wrapped in brackets inside a sentence
Count the cities inside one comma-separated list
Taking the width out of a one-line carton size
Turn the semicolons in a city list into commas
Turn an invoice's second hyphen into a slash
Strip dots from a phone number without losing the zero
Matching two codes when one has a trailing space
Add up the length of every note in one column
Capitalise a sentence's first letter, not each word
Restoring an abbreviation PROPER has lowercased
Build a mini bar chart out of revenue figures
Padding item names out to exactly 20 characters
Drop the bracketed note from an item name
Replace only the second occurrence of a separator
Clear plus, space and hyphen from a phone number
Take the second field out of a pipe-separated row
Strip the company-type prefix only when it is there
Lower stray capitals while capitalising the start
Replace a word whatever its capitalisation
Swap name and job title round for a name badge
Replace the city code inside a consignment number
Build two-letter initials from first and last name
Padding a receipt line with dots so prices line up
Breaking a serial number into three hyphenated groups
Levelling case and spaces before two codes are matched
Cutting a promo title at a word, not mid-word
Join an address when some columns are still empty

Financial 46

Financial
FormulaRelated Functions
How many months until the savings target is met
Effective annual rate from a nominal rate
The real interest behind an installment figure
Working out a monthly mortgage payment
Bond price per 100 of face value
Return on cash flows with irregular dates
Internal rate of return on a project
Bond yield to maturity
Future value of savings with regular deposits
Net present value of a project
Present value of a fixed monthly rent
Straight-line depreciation of an asset
Double declining balance depreciation per year
Separating the interest portion of one installment
Separating the principal part of one payment
Remaining loan principal after five years
Break-even point in units sold
Total interest over the life of a loan
Work out plant hire paid at the start of each year
Work out a car instalment with a residual at the end
Total the interest paid over a loan's second year
Principal actually repaid during the first year
Why the opening outlay is added outside NPV
Valuing a project whose cash arrives at uneven gaps
Return on a project whose funds come from a loan
Savings that already start with an opening balance
Final capital when every year carries its own rate
Fair price of a bond whose coupon lags the market
Find the nominal annual rate from the effective one
Turning six years of growth into a yearly rate
How long money doubles at fourteen percent
Depreciating a press more heavily in its early years
Truck depreciation at a rate drawn from salvage value
First-year depreciation for an asset bought in June
Final-year depreciation that switches to straight line
Judge a project on borrowing and reinvestment rates
Present value of instalments on their real dates
Equivalent yearly growth across a six-year span
How many years it takes capital to double
Interest on a loan whose principal repays evenly
A term deposit whose rate changes four times
Present value of shop rent paid at each month's start
Work out a vehicle instalment with a balloon payment
Total the interest paid across one whole year
Compound a principal over seven years
The minimum deposit from an affordable instalment

Geometry 29

Geometry
FormulaRelated Functions
The angle between two points with ATAN2
Distance between two stakes from plan coordinates
Circumference of a round pool from its radius
Pipe cross-section area from its diameter
Surface area of a wooden crate to be painted
Area of triangular land from base and height
Triangular land area from its three side lengths
Trapezoid land area from two parallel sides
Arc length of a bend from its central angle
Ladder length from its height and foot distance
Roof pitch angle from height and span
Tower height from an elevation angle in the field
Converting an angle from degrees to radians
Cylindrical tank volume from diameter and height
Area of a ring-shaped paved path around a pond
Distance between two racks at different heights
The volume of a cone-shaped pile of sand
The volume of a spherical tank given its diameter
Perimeter of a garden with two semicircular ends
Parallelogram area from two sides and their angle
Two blocked markers measured from one survey point
The largest angle of a triangle from its three sides
Land area from the coordinates of four corner pegs
A pole's offset from the line between two survey pegs
Compass bearing of two points, 0 to 360 degrees
Area of a pitched roof from its floor plan
Count the tiles to buy for one room
Wall area to paint once the openings come off
Jakarta to Surabaya from latitude and longitude

Workbook 27

Workbook
FormulaRelated Functions
Count invitees missing from the attendance list
Look up a price in two lists stacked first
Count the rows that differ between two copies
Count the rows of two blocks stacked together
Count unique customers from two monthly sheets
Sum one city from two sheets at once
Top revenue owner across two sheets
Combined average of two classes of different size
Take the sheet name from a copied formula reference
Split the cell address out of a sheet reference
Count sheet names that need quotes inside a formula
Flagging sheet names past the 31-character limit
Flagging sheet names that use forbidden characters
Count formulas that shift when a sheet is renamed
Find the address of a stock cell from an item code
Find the last filled cell when a column has gaps
Turn a column number into its column letter
Turning a two-letter column label into its number
Lock a price range address with dollar signs
Count headed columns when a spacer column is blank
Count rows that are entirely empty inside the data
Check whether an export fits on one worksheet
Check whether two blocks can be stacked into one
Count a range's rows even when some are empty
Build a cell address from its row and column number
A cell's column number, not a range's width
A name's position in the data, not on the sheet

Internet 39

Internet
FormulaRelated Functions
Taking the suffix of a domain name
Taking the domain name out of an address
Taking URL parameters without the fragment
Reading the value of one parameter in a URL
Removing the www prefix from a domain name
Counting addresses that use https
Counting the parameters in one URL
Building a search address from a keyword
Flag emails that lack exactly one at sign
Count the distinct organisations in an email list
Restore a keyword whose letters are percent-encoded
Building a mailto link with the subject already filled
Count how many characters a URL has encoded
Leave a base address with no query and no fragment
Take the file name off the end of a download link
Count links that really use a secure connection
Count emails whose domain is exactly the office one
Counting distinct people in a messy list of emails
Join a base address to a path without a double slash
Counting e-mail addresses whose domain has a dot
Turning a file name with spaces into a valid download link
Count links broken by a space in the middle
Spot an email whose domain is not on the official list
Remove the protocol prefix from mixed addresses
Extract a web address path without domain or query
Take the page name at the end, query dropped first
Cut off a fragment without leaving its hash behind
Drop one trailing slash from an address, not every one
Pull the domain name out of an email address
Tidy a person's name out of their email user name
Assembling an office e-mail from a full name
Take the subdomain name out of a web address
Count the path segments after an address host
Checking whether a link really belongs to the domain
Replace an old domain but leave the file name alone
Appending a tracking parameter without breaking the URL
Read a page number when parameter names overlap
Count links whose host really is that one domain
Encoding a search term so it is safe inside a URL

Names 36

Names
FormulaRelated Functions
Building initials from a full name
Split the last name out of a full name
Split the first name out of a full name
Reversing the first and last name order
Joining a name from three columns
Normalizing capitalization in names
Tidying only the names typed entirely in capitals
Making the surname stand out on an attendance list
Building a username from an initial and a surname
Count the surplus spaces inside a pasted name
Count name rows whose spacing still needs tidying
A first name that does not fail on a single name
Building a Bapak or Ibu salutation from a first name
Tidy a reversed name with a space before its comma
Shorten a middle name to one dotted letter
Matching two names that differ in case and in spacing
Count unique names once the stray spaces are cleaned
Take a first name even when a title stands in front
Printing a name card in caps with no stray spaces
Making sure a name fits 20 characters once trimmed
Joining a name list into one tidy comma-separated line
Drop trailing qualifications however many there are
Drop leading titles when there is more than one
Take the last name when a name runs past two words
Take the middle name out of a three-word name
Build dotted initials for any number of name words
Take the first name from a reversed name list
Building a work email from the first and last name
Count people whose name carries a middle name
Sorting a list by last name rather than by first name
Count people whose last name is Pratama
Match two lists whose name formats differ
Taking a surname when someone has only one name
Separating every degree suffix into its own column
Strip a title in front and letters behind in one go
Abbreviating the last name, not the middle one

Percentage 32

Percentage
FormulaRelated Functions
Final price after a two-stage discount
Finding the original price before a discount
Extracting the PPN from a gross price
Working out tiered commission layer by layer
Compound annual growth rate (CAGR)
Computing profit margin against the selling price
Working out markup against cost price
Raising a price by a given percentage
Percentage of students passing out of all takers
Percentage sales growth between two months
Calculating the percentage of target achieved
Each branch's share of the total
The gap between two percentages in points
Adding PPN to a base price
Reducing a price by a discount percentage
One branch's share of all the branches' sales
Revenue growth measured against last year's figure
Percent change when last year was still a loss
Combine two percentage changes that follow each other
The rise needed to recover from a drop in revenue
What share of revenue the top three products bring
A city's share of revenue, not its share of invoices
One branch's share of the whole revenue growth
The effective tax rate, not the top bracket rate
PPN rounded on every row, not once at the end
Cash received after PPN and the PPh 23 deduction
Turning a markup on cost into a margin on selling price
The margin left once the customer discount is given
How far volume must rise to cover a discount
A bonus for hitting exactly one hundred percent
How many percent a branch sits above the average
Compare two months that have different day counts

Random 40

Random
FormulaRelated Functions
Shuffling the order of a participant list
Taking several sample rows at random
Random number between zero and one
Random numbers with two decimal places
Splitting participants into random groups
Random whole number within a set range
A fixed-size block of random numbers
Picking one random name from a list
Simulating six hundred dice rolls
A draw that gives every participant a chance
Making sure the highest number can still be drawn
Random sales targets that are always multiples of five
How many groups for 17 people with room for 5
Schedule random inspections that miss the weekend
Random start times always on a 15-minute mark
Split 14 people into four evenly balanced groups
Random dates that never spill into the following month
Limiting a prize draw to the members of one division
Making sure the running order really changes each show
Shuffle queue numbers 1 to 20 without losing any
Split a budget at random but keep the total fixed
Splitting attendees into three sessions of unequal size
Testing a die roll average against its expected value
Testing a randomiser by how often each die face lands
Drawing three different winners from ten entrants
Drawing by the number of coupons, not by the row count
Drawing a fresh winner without repeating last month
Shuffle rows without splitting a name from its division
Drawing one whole random customer row, not three cells
Draw exactly 5% of invoice rows as an audit sample
Draw two people per division, not six random names
Handing out random queue numbers with no repeats
Drawing gift pairs where nobody gets their own name
Picking a top-scoring winner with no risk of a tie
Pick sample invoice numbers that certainly exist
Sieving entrants on at least, rather than more than
Three winners and two reserves, all of them different
Handing out 100 four-digit tickets that all differ
A gift exchange draw where nobody draws their own name
Split entrants into equal-sized groups at random

Ranges 34

Ranges
FormulaRelated Functions
One branch's share of a locked total
How many rows a dynamic range now covers
Checking a range's coverage before naming it
One range named twice in a single formula
A fixed rate kept in one locked cell
Naming the address of the highest-value cell
Finding the address of the last filled cell
Taking a block's last row with a negative index
Taking one column out of a block of scores
Flipping the rows and columns of a block
Joining two blocks split by a header row
Shifting a reference by rows and columns
How many cells are filled in a two-dimensional block
Counting the cells where two ranges overlap
A cell's row and column number on sheet and table
Choosing the column to sum by a letter in a cell
Averaging three days around one date
A total range that lengthens as rows are added
Measuring a range: how many rows, how many columns
The last balance recorded in a column full of gaps
Find the last filled row number despite the gaps
Summing to the last week even when a week is blank
The last day on which a sale actually happened
Read the last month a branch has reported in
Add up the last three deposits, not the first three
Averaging four months as new rows keep being added
Totalling income without the two opening-book rows
Show page two of a list, five names at a time
Summing three months back to the month chosen in a cell
Count the unfilled days up to the last entry made
Last month's usage from cumulative meter readings
Sum whichever month column one offset cell selects
Averaging the last three filled months around the gaps
Pull an item's latest purchase price from its history

Tables 38

Tables
FormulaRelated Functions
A total row that counts numbers only
A total row holding an average, not a sum
Count data rows without the header row
Filter first, then sum what the filter kept
Summing a column found by its heading name
Sum the data without summing the total row too
Find a column header written twice
A column total that stays right as rows are added
Find master items missing from the delivery note
Count orders that never showed up in the bank file
Vendor serial numbers missing from the warehouse
Find the column headings missing from a delivery
Check required columns before merging two files
Count distinct attendees across two shared sessions
Adding two tables whose columns sit in a different order
Count orders that have a match in the dispatch log
Finding duplicate customer codes in a lookup table
Flagging an invoice code missing from the customer table
Totalling delivery notes for registered items only
Merge two counter queues into one ordered list
Goods whose book stock differs from the physical count
Matching delivery codes down to their letter case
Check heading order before stacking two tables
Look up a price by column name, not column number
Add a branch column by its heading, not its letter
Reading a cell from its row and column headings
Add up the last month's column without naming it
Summing a table body without its Total row and column
Filtering invoices on two conditions at the same time
A stand-in sentence when the filter finds nothing
Count distinct customers in a filtered branch list
Take the last price from a column with blank rows
Find the highest-value item in one branch alone
Add up values only for codes sharing a prefix
Averaging branch deposits without the Total row
Checking whether the Total row matches the table body
Count one branch's orders whose tracking is blank
Add up with the conditions in a separate block

Errors 29

Errors
FormulaRelated Functions
Replacing VLOOKUP #N/A with a code message
Count how many codes failed to be found
Telling IFNA apart from IFERROR on #DIV/0!
Telling ISERR from ISERROR on #N/A
Identify an error type by its ERROR.TYPE number
Why #NAME? comes from a misspelled name
Why #NUM! appears on the root of a negative
Why #VALUE! appears on numbers mixed with text
Preventing #DIV/0! by checking the divisor first
Replacing a name lookup #N/A with a sentence
Catching #DIV/0! from an average with no data
Flagging buyers who are not members yet
Catching #DIV/0! while the divisor is still zero
Testing whether a lookup failed
Separating an unregistered code from broken data
Tally every error cell, not just the not-found ones
Naming the kind of error, not just flagging that one exists
Add up a column when one of its cells is an error
Averaging a column where some rows hold errors
Marking a branch not yet producing without a zero
Hold the error trap back so a formula typo shows
Reading a name from a code in the right-hand column
Holding a total back while the data still holds errors
Summing a weight column when one cell holds text
Looking up a price when the code hides a trailing space
What percentage of the data pulls actually worked
Finding the topmost error row, whatever its kind
Tell an unweighed parcel from one weighing zero
A combined average from totals, not from a ratio column

Miscellaneous 46

Miscellaneous
FormulaRelated Functions
Reading a Roman numeral as an ordinary number
Reading a signed binary register as decimal
Comparing two classes' scores with a t-test
Telling combinations apart from permutations
Building an evenly spaced series from three cells
GCD and LCM of three roll lengths
Adding two phasors and measuring the magnitude
Counting how many plate numbers are odd
Probability of a weight below one limit
Probability of at most two defective items
Measuring the spread of five daily output figures
Writing a straight-line equation and its forecast
Writing a device address as hexadecimal
Converting shipment weight from grams to ounces
Writing the founding year in Roman numerals
Measuring how closely two columns move together
Converting a body temperature from Celsius to Fahrenheit
Converting a suitcase side from inches to centimetres
Converting baggage weight from pounds to kilograms
Merging a height in feet and inches into total inches
Warehouse space diagonal from its three edges
Straight-line distance between two map points
Work out a ladder's angle against the floor
Area of a hexagonal sign from its side length
Count the largest notes needed to give change
Split a shared bill rounding up to the next hundred
Round a parcel weight up to the next half kilogram
Rounding every row against rounding the total once
Deciding pass or reject from a two-sided tolerance
Count bars off tolerance in either direction
Checking a column is all numbers before it is summed
Estimating download time from a plan's Mbps figure
Turning miles per gallon into litres per 100 km
Converting a room's area from square metres to square feet
Disk capacity from packaging gigabytes to gibibytes
Turning overtime hours and minutes into decimal hours
Charging freight on volumetric weight when it is larger
Compare two printing years written in Roman numerals
Add up page counts written in Roman numerals
Writing a plaque year in the most compact Roman form
Read a shelf number from a base-36 code
Make base-36 locker codes always three digits wide
Read a below-zero temperature from a hex log value
Writing a network octet as an eight-bit binary number
Find the network address from address and mask
Read the red channel out of a hex colour code