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

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

June 15, 2011

Excel Array Formula

Knowing Excel array formulas will catapult you to ‘Guru Status’ in your office and I'll do my best to make

June 1, 2011

Excel TRIM Function Removes Spaces From Text

If you import data from another program it often comes in with additional spaces either at the beginning, end or

May 25, 2011

Excel UPPER LOWER and PROPER Functions

Sometimes when you import text from another source it may not be formatted in the case you want. For example

May 11, 2011

Excel CEILING and FLOOR Functions

In Australia we did away with 1 and 2 cent coins years ago. So all prices, where paid in cash,

April 30, 2011

Excel CHOOSE Function

The Excel CHOOSE function is not much use on its own, but when you nest it with other functions it

April 20, 2011

Excel MIN MAX SMALL and LARGE Functions

The MIN, MAX, SMALL and LARGE functions in Excel are fairly straight forward, but I’d like to show you a