- Joanne @ Paramount
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 |