Excel Formulas Archives • Page 6 of 7 • My Online Training Hub

June 6, 2012

Secret Excel DATEDIF Function

Want to calculate the number of months between two dates? One little known way is to use the secret Excel

May 29, 2012

Excel COUNT Coloured Cells

From time to time I get asked; “how can I count cells formatted in a particular colour”. Aka 'color' if

May 24, 2012

Find the Last Value in a Column

The other day a member emailed me the following question: “I created a check book in Excel 2010 and I

May 2, 2012

Excel 3D SUMIF Across Multiple Worksheets

No, no, no, no, no I’m not talking about the latest 3D animated movie. I’m talking about how you can

April 19, 2012

Excel RANK, RANK.AVG and RANK.EQ Functions

No prizes for guessing what the Excel RANK functions do and I’ll cover them in a moment. But first you

April 10, 2012

Invest $x at 4.75% for 5 Years to Make $15,000

I received an email from Stacey a few weeks ago asking: “Which formula would I use to find the initial

March 27, 2012

Excel EOMONTH Function

Continuing on from last week’s EDATE function, today we’re looking at the Excel EOMONTH function. If you use Excel to

March 20, 2012

Excel EDATE Function

If you need to calculate due dates for projects or payments then Excel’s EDATE function will make life easy for

March 13, 2012

Excel MIN IF Array Formula

A few weeks ago Karine asked me if there was a way she could find the minimum value for

March 7, 2012

Calculate How Long to Make $1m with NPER

James wrote to me and asked: if he buys shares worth $2000 and gets 4% return, how many times does

March 1, 2012

SUMIFS Formula Referencing Cells Containing Dates

Formulas containing dates and time in Excel can be frustrating if you don’t understand how they work. And even if

February 22, 2012

Excel Evaluate Formula Tool

From time to time I get asked the question “what order do formulas evaluate?” The acronym BEDMAS can help you

February 15, 2012

VLOOKUP Multiple Criteria

I had an email from Bobcat today asking how to lookup data that is spread across multiple columns. Table

February 8, 2012

Excel Data Validation With Dependent Lists

Using Data Validation to restrict what gets entered in a cell or range of cells is great for standardising your

January 27, 2012

Excel AVERAGE, AVERAGEIF and AVERAGEIFS

Let’s say that a baby learning to crawl is an analogy for learning the AVERAGE function in Excel. Then learning

January 18, 2012

ROUNDUP and ROUNDDOWN with a Twist

Last week I had a question from Dan that I’d never come across before. Dan has a dress shop with

January 12, 2012

INDEX MATCH With a Twist

My Toughest Excel Challenge So Far! Recently a member contacted me with a VLOOKUP question, but when I further understood

December 14, 2011

Excel INDIRECT Function

The Excel INDIRECT Function has many applications and perhaps its simplest is to fix a range of cells you want

December 7, 2011

Excel VLOOKUP Multiple Values

For some of us we use the VLOOKUP function all the time and for the most part is does exactly

November 30, 2011

Excel LARGE and EOMONTH Array Function

I’m working on a dashboard for a client and I’ve set it up so that they can choose which month

November 23, 2011

Cool INDEX Function Trick

What say you want to find the percentage increase or decrease for the last value in a list compared to

November 16, 2011

Excel Average Top 3 Values

You may already know you can find the 3rd highest value using the LARGE function, but what if you want

September 28, 2011

Excel TIME Function

The Excel TIME function combines separate hour, minute and second values and converts them into a time serial number. If

September 21, 2011

Excel CONVERT Functions

With a family of 4 fussy eaters to feed (well actually, 3 fussy eaters and myself) I do a lot

September 7, 2011

Easy Excel Database Functions

Excel Database functions have been around for donkey's years so it's surprising that not many people know about them. They

August 31, 2011

Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

Wildcards *?~....what am I talking about? And no, it's not an expletive. Let’s start with an example. COUNTIF using Wildcards

August 24, 2011

Excel SEARCH and You Will FIND

In Excel there are two almost identical functions; SEARCH and FIND. The SEARCH and FIND functions allow you to find

July 13, 2011

Excel SUBSTITUTE Function Trick

Each time I write a tutorial I use the Google Keyword tool to find search phrases that are relevant. I

July 6, 2011

Excel COUNT, COUNTA and COUNTBLANK Functions

If you just want a quick count of the number of items in a list or a range of cells

June 22, 2011

Excel VLOOKUP with Dynamic Column Reference

If you ever work with large tables of data and you want to insert a VLOOKUP formula that dynamically updates