Skip to content

LAB / FREE TOOL

Describe it.
Get a formula that's been tested.

Excel, Google Sheets or SQL. Paste a few rows and every answer is run on your data before you see it. Nothing leaves your browser.

Your data

Stays in your browser. Nothing is sent anywhere.

What do you want to work out?

Recipes

The requests people make most, answered by code in an instant. Pick one and choose your columns; nothing is sent anywhere.

Every recipe, with a worked example

Totals

Total of a column where a condition is met Total where two conditions are met Count rows where a condition is met Average of a column where a condition is met Largest or smallest value where a condition is met Total for each group (e.g. sales per region)Count the different values in a column Total between two dates Total for each month Running total Each row as a percent of the total Rank rows by a value Top N rows by a value Count rows for each group Average for each group Largest value for each group Smallest value for each group Total for each pair of groups Total where a column is one of two values Count rows where two conditions are met Average where two conditions are met Total of a whole column Average of a whole column Largest or smallest value in a column Count all rows Count filled-in cells Count empty cells Count rows above the average Median of a column Standard deviation of a column Weighted average Total of the top N values Each row as a percent of its group Rank rows within each group Difference from the average Change from the previous row Percent change from the previous row Moving average Total for this month Total for the last N days Total for each year Count rows for each month

Lookups

Look up a value from another column Look up a value (older Excel: INDEX/MATCH)Look up with two conditions Show only the rows that meet a condition Sort rows by a column VLOOKUP (the column to search must be on the left)Look up the last match, not the first Find the row with the largest value Find the row with the smallest value The Nth largest value Look up the nearest lower value (tiers, bands)Check if a value exists in a column Keep the latest row for each group

Text

Combine two columns into one Text before a separator (e.g. first name)Text after the last separator (e.g. surname, domain)Clean up text (spaces, capitals)Check if text contains a word First N characters Last N characters Length of the text Count the words in a cell Replace text Remove a word or character Keep only the numbers in text Text between two markers Add leading zeros Join a whole column into one cell Capitalise the first letter only Initials from a full name Build an email address from a name Check if text starts with something

Dates

Days between two dates (or since a date)Add months to a date Month name, weekday or year from a date Age or years since a date Working days between two dates Quarter from a date Week number from a date Last day of the month First day of the month Add days to a date Add years to a date Check if a date falls on a weekend Flag overdue dates Months between two dates Show a date in a readable format Fiscal year from a date Latest or earliest date in a column

Cleanup

Find duplicates Remove duplicates (keep the first)Find empty or missing values Label rows with IF / CASE Compare two columns Replace empty cells with a value Turn text into a number Round to a number of decimals Round to the nearest 5, 10 or 100 Sort numbers into Low, Medium and High Show negative numbers as zero Divide without errors Percent change between two columns Number the rows Flag the first time a value appears Count how many times each value appears Count the duplicates in a column Remove rows with an empty cell List the different values, sorted

Bring us the problem. We'll work through the system.

Your next
hard problem.↗