The SUMPRODUCT function can be used to multiply an array of cells and then sum the total of those calculations. To demonstrate, firstly here is an example of the long way of doing it, without SUMPRODUCT: To calculate total number of chocolate bars sold, each row is calculated one-by-one and then all Read More …

# Category: Formulas

## Calculating percentages in Excel

Some basic examples of how to calculate percentages using Excel formulas. Percentages as a proportion The formula in D3 is: =B3/C3 Calculate percentages backwards to find a starting value The formula in D3 is: =B3/(1+C3) Percentage increases and decreases The formula in D3 is: =(C3-B3)/C3 Although both differences are based on the Read More …

## Date and time functions in Excel – An overview

There are a wide number of date and time functions in Excel, here is a brief tutorial on some of the most common ones with examples of how you might use them. Covered in this post: TODAY() NOW() DATE() DATEVALUE() EDATE() WEEKDAY() EOMONTH() DATEDIF() NETWORKDAYS() HOUR() MINUTE() SECOND() TIME() TODAY() This Read More …

## Exclusive Or formulas with the XOR function

Most frequent Excel users will be familiar with both the AND and OR functions, but here’s a quick recap just in case. Both AND and OR are Boolean functions, which means they will return either TRUE or FALSE based on the set of conditions you pass them. The below table shows the top Read More …

## Why is my Excel formula being treated as text?

Despite the = equals sign being at the beginning, is Excel still not reading your formula as a formula? This can happen occasionally. The reason is usually that the destination cell (in this case A3) has previously been explicitly formatted as text. Then when the formula is manually typed in, Excel continues to Read More …

## Date differences using the DATEDIF function

The DATEDIF function allows you to represent the difference between two dates in various formats. It has three arguments for you to enter; the start date, end date and units you are counting in. E.g. =DATEDIF(A1,B1,”D”) will return the difference in days between the values in A1 and B1. Here are some examples Read More …

## Rounding up and down in Excel

There are some simple formulas in Excel for rounding your results to the nearest decimal, and I’ll also cover the functions which enable you to round to the nearest multiple. ROUND, ROUNDUP and ROUNDDOWN round to a specified number of decimal places. In the below example we are firstly rounding to the nearest whole number, then Read More …

## Why are my Excel columns showing as numbers and not letters?

This is a common query that comes up, when an Excel option has been tweaked or you’re using someone else’s computer and the column headers are set as 1, 2, 3 instead of A, B, C. This doesn’t do any harm, but does make writing formulas a bit more confusing. Assuming you want to change Read More …

## Excel Formula Auditing overview

There are a number of in-built Excel features for auditing your formulas. You can find them in the Formula Auditing section within the Formulas menu. They can be particularly useful if you’re trying to understand a spreadsheet you’ve inherited from someone else, or you’re returning to something you worked on a while ago. Here is Read More …

## Excel formula for a Distinct Count

Firstly, if all you want to do is filter out duplicates you can use the Advanced Filter tool to do so. Go to the Data tab, Sort & Filter, Advanced and select the ‘Unique records only’ tick box. However, if you want to return the distinct count of a range then you Read More …