Useful Formulae for your Excel Spreadsheets

We often get asked ‘is there a list somewhere of formulas?’. Well, the answer is that if you check the FX button at the start of the formula bar in Excel, you can access every single formula possible. However, we guess that probably 95% of them we will probably never use as they are fairly niche. Do any of these sound useful for your data…..?!

BINOMDIST – returns the individual term binomial distribution probability

CHISQDIST – returns the left-tailed probability of the chi-squared distribution

HARMEAN – returns the harmonic mean of a data set of positive numbers: the reciprocal of the arithmetic mean of reciprocals

Well, exactly!

So, we’ve put together a list of a few formulas we often use along with a note of how you could use them. Let us know if there are any other favourites you use!

Formula	Definition	Possible uses
​Average	Returns the average of a range of numbers	​Average sales, average salary, average cost
Max	Return the largest number in a range. Returns the latest date in a range	Find out the last date a customer ordered from you, last staff appraisal date. Largest salary. Largest order value
Min	Return the smallest number in a range. Returns the earliest date in a range	Find out the first date a customer ordered from you, first staff appraisal date. Lowest salary. Smallest order value
Count	Counts how many cells contain a number	How many orders have been placed
Counta	​Counts how many cells contain a value ie text or numbers	Count a list of names
Vlookup	​Find a value from the main table in another table	Find the salesperson’s name from a separate list. Mileage claims eg a journey to London is always 75 miles
If	Evaluates a rule and returns one of two outcomes	Stock control – flag up when the minimum stock level has been reached
​Countif	​Counts how many items in a list that have a specific value	eg. number of people who work in a specific department
Countblank	​How many cells are empty	​Check for missing data
​Sumif	Returns the total (sum) of the cells which meet a specific criteria	​What are the total salaries for a specific department
Concatenate	Joins two or more text cells into a new cell	​Combine first and surnames into a new cell
Left	Returns the first/first two/first three etc characters from a text string	Use the results to define what type of product/customer/dept the code relates to
Today	Returns the current date (automatically updates)	Working out how long since/until a date
​Month/day/year	Breaks down the selected date to extract the month/day/year	Identify all staff who joined in a specific year or month
weeknum	What is the week number we are currently in
Now()	​What is the current date and time (auto updates)	​Working out how long since/until a date/time
​networkdays	How many working days between two dates	How many more days do you have before order delivery
​weekday	What is the day of the week for the specified date	TIP: Use this with a vlookup to return the day in a word format rather than a number eg Tuesday rather than 2
workday	What will the date be in x number of days’ time	How many more working days do we have before delivery
trim	Remove excess spaces from a text string	Useful in conjunction with a vlookup
Upper/proper/lower	​Change the case of text	​To tidy up your data