Excel 2016 has introduced a new type of IF function to simplify handling multiple conditions. It is called IFS.
Tag Archives: formula
Relative and Fixed Reference Trick in Excel
Find and Replace is your friend
When creating ranges of formulas that you want to copy down, you sometimes have a trade off in the use of fixed and relative references. If you need to create a relative reference that acts like a fixed reference you can use a trick.
How to Handle a Formatted Table Limitation
Choose from simple or advanced
Formatted Tables are great but there is an issue when it comes to copying formula that use the table names (Structured References). There are two techniques that cope with this limitation.
Reduce Excel Formula Length
Remove redundant references
When you create formulas that refer to other sheets Excel typically includes the name of the current sheet when you return to the current sheet and refer to a cell.
Count Sundays between two dates
SUMPRODUCT shows it flexibility
We’ve all heard the term “A month of Sundays” to describe a long time. Well what if you wanted to count how many Sundays between two dates?
Getting Date Headings Right
Formulas rule
If you are using date-based headings in your reporting models please consider using dates in the headings rather than text. I’ll explain why.
The NETWORKDAYS.INTL Function Has a Unique Trick
Its customisable
The NETWORKDAYS.INTL function was added in Excel 2010. It allows to calculate how may work days between two dates using non-standard weekends. Some countries don’t have Saturday/Sunday weekends.
How Many Fortnights in a Month
Great for cashflow calculations
I had a question during one of my Date and Times Webinars in February. It was about months and fortnights. I couldn’t answer it during the webinar, but I did follow up with an email with the solution. The answer follows.
SUMIFS Function Warning
Leading zeros are not handled correctly
Most people are unaware that the SUMIFS function has a serious limitation when it comes to codes with leading zeroes. This post shows you how to perform calculations involving codes with leading zeroes. This issue also affects SUMIF, COUNTIF and COUNTIFS.
Converting a Tricky Date Format
I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.
Converting a number to a text number in Excel
An easy formula
If you need to convert a number into a text number within a formula there are a couple of ways to achieve this, but one way is a lot easier.
Creating a Hyperlink in Excel Based on a Search
Teleport to a cell in a data table
Let’s say you have a table of codes and every month there are a few you want to check out. You could use a VLOOKUP to extract all the details for each code, but let’s say you want to view the codes in the table.
Totalling Tip For Excel
Make the most of SUMIF
I wanted to offer a solution to a common problem I see in Excel. It relates to creating totals in data that isn’t structured that well.
The Handy Functions in Excel – LEFT and RIGHT
Simple Defaults
The LEFT and RIGHT functions are great for extracting leading or trailing characters from a text string. Did you know their default setting is handy too?
Checkerboard Format in Excel
How to get alternate shading in cells
I was looking at a calendar and noticed it used alternately shaded cells, like a checkerboard, for all the dates and thought Excel could do that.
Handling Text and Real Numbers with VLOOKUP
Helping you work with imported data
Sometimes data that comes into Excel with code numbers formatted as text. This can stop VLOOKUP functions from working and return the dreaded #N/A error. With a couple of tweaks you can lookup both real numbers and text numbers in the one formula.
TRANSPOSE Function in Excel
Plus a trick to avoid zeroes displaying
The TRANSPOSE function is one of only a few functions that must be entered as an the array using keyboard entry Ctrl + Shift + Enter (CSE). It allows you to switch a range from going across the sheet, to go down the sheet and vice versa.
Towards a Shorter IF Function
Returning a range
Most people think that the IF function has to return a result. This leads to doing whole calculations in the true and false sections of the IF function. There is a way to create shorter functions.
Finding the Last Used Cell in an Excel Column
Array free zone
If you need a formula to identify the last used cell in a column you don’t have to use an array formula. The AGGREGATE function can calculate it for you.
Flexible Hyperlinks in Excel
Using the HYPERLINK Function
Hyperlinks are a great way to navigate around large spreadsheets. Unfortunately they each take a few clicks to create and can be easily broken. You can use a function to easily create multiple, flexible hyperlinks.
The Problem with the MROUND Function
The ROUND function rounds values to decimal places on either side of the decimal point. It is useful and popular. The MROUND function is meant to allow you more flexibility in your rounding calculations. Let’s say you want round to closest 0.05. The MROUND is meant to handle this calculation but unfortunately it provides inconsistent results.
Adding Up Values Only in Excel
Ignore formula results
Here is the problem – you have a list of values which includes sub-totals calculated using SUM functions. You only want add up the values and ignore the SUM function cells.
Sequential Numbers With a Twist
How to ignore hidden rows
You want a sequential number in a column. The challenge is, it must display as sequential even if rows are hidden or filtered. Is this possible?
Getting the Week Number From a Date in Excel
New WEEKNUM Function - ISO 8601 standard
Excel has had a week number function for many versions, but Excel 2013 added a new week number function that complies with the ISO 8601 standard week number.
Adding years or months to a date in Excel
Finding the end date can be easy
When working with loans or leases, it is common to have to add a number of years to a start date to determine the end date. An Excel function can automate that process.
Its a FACT Jack
Working out how many combinations are possible
Do you remember factorials in maths? That’s where you have, say four numbers, and you want to find out how many four number combinations you can make with those four numbers. In Maths you write it as 4!. The answer is 4 x 3 x 2 x 1 = 24.
Confirm Two Lists Match in Excel
A single formula to check two lists
If you have two lists of numbers and you need to ensure they are identical there is a simple formula that can confirm they match.
Summing every second row
No array formula required
Let’s say you have a list of values that has a set sequence. In the list which starts in row 2 you want to add up every second entry. So you want to add up the entries in row 3, row 5, row 7 etc going down the sheet. The SUMPRODUCT function to the rescue yet again.
Ordinals For Dates
1st,2nd,3rd,4th
If you want to place ordinals (st, nd, rd and th) after a number in a date. You can use the CHOOSE function to do this.
Working with Imported Dates and Times in Excel
Date data imported from other systems can include times. This can make lookup and other calculations difficult. One function can make removing or extracting time easy.