• 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


7 views0 comments

Recent Posts

See All