Functions

Easily Trace Formulas Back How to Find Formula Precedents By Jeff Robson Sometimes it's the simple things in life that are often the best. Well, here's a really simple thing that can make your life as a formula tracker a LOT easier. If you want to trace a formula back and go to the cell(s) that make [...]

Multi-line VLOOKUP I have a data base that has five separate rows for each item. I can use VLOOKUP to find the first row's details, but how do I access the details in the other four lines? By Neale Blackwood This is an ideal situation to use the INDEX / MATCH function combination. These [...]

Random Numbers Is it possible to generate random numbers in Excel? By Neale Blackwood Yes. Often it is useful, when setting up a spreadsheet, to use random numbers before you start using "live" numbers. There are two functions that will generate random numbers. The first is the RAND function. It produces a random number [...]

SUM with Wildcards I frequently use the SUMIF and SUMIFS functions. Is there a way to sum based on a part of a code? The codes maybe ABC123, ABC999 or BBB111 but I want to add up all the codes starting with ABC. By Neale Blackwood Both these functions can perform sums based on [...]

Using the AND + OR functions I struggle to use the AND and OR functions. Could you explain how they should be used? By Neale Blackwood AND and OR functions are typically used with the IF function. They allow you to examine multiple logical tests in a single function rather than use numerous IF [...]

Function wizard Using Functions for the first time By Neale Blackwood, 1 Mar 09 When you first start to use a new function it can take a while to get use to the syntax (layout) of the function. Excel assists you with the Insert Function button (the fx button between the name box and the formula bar, above [...]

Extracting character Extract all but the last 2 characters By Neale Blackwood You can do this using two text functions. The following function will work on any length text string. It is extracting the text from cell A1. =LEFT(A1,LEN(A1)-2) The LEFT function extracts a number of characters from the left of a string of [...]

View range names See range names on the sheet By Neale Blackwood If you frequently use range names, then seeing where they are on the sheet can be helpful. There is a feature in Excel that allows you to do just that. On a sheet with range names change your Zoom percentage to less [...]

Days into the year Using the DATE function By Neale Blackwood Ever needed to find how may days have passed into a year? For any date in A1 this formula will tell you how many days have passed. =A1-DATE(YEAR(A1),1,0) When you use the 0 on the end of a DATE function it calculates the [...]

Calculating with month Using the DATE function By Neale Blackwood Do you need to increment dates by whole months? For example do you need to add 1 month to 15/5/2009 to arrive at 15/6/2009? You could use the DATE function, but there is an easier function, EDATE, that is designed for this task. In [...]