Author Archive

How do we perform a Sumif on seemingly randomly spaced columns?
Today in Formula Forensics we look at a solution to do just that.

VLookup is often seen as the poor cousin in the lookup function family and often gets overlooked when it comes to looking up values due to a common misconception that Vlookup doesn’t or can’t lookup values to the left of the reference value.
Today at Formula Forensics we will explode this myth and see how to make VLookup, lookup to the Left and we’ll explain how it works.

Today in Formula Forensics we look at how to strip away leading zeroes from strings with this simple array formula

Today Formula Forensics looks at a formula that Highlights Only the Duplicate Entries in a range.

Need to count how many unique values occur in a range?
Learn how to do this and how the formula works right here.

Is my number a Prime Number?
What is a Prime Number?

Today in Formula Forensics we answer both questions.

Have you ever wanted to make your own Custom Chart type ?
MarnieB was asked by her boss to make a Spoke Chart !

This post will explain how you can make a custom Excel Spoke Chart and introduce you to techniques that can be used to make other custom chart types.

Today at Formula Forensics, we look at how to Count and Sum data using Criteria on Filtered data sets.

How can we Add Up the Odd numbers between 1 and 100 ?

Chandoo arrives in Perth, Australia.

Do you want to catchup with Chandoo (and Hui) for a Sundowner in Perth ?
Read here for details.

No, Not that Slash !

How do I find the 4th Slash in a text string?
Today at Formula Forensics we take a look at 4 different methods.

Pivot tables are a great way of summarising and consolidating data to produce summary reports.

One of the main limitations of Pivot tables is that they don’t natively return Text values.

This post looks at a method to work around this without the use of VBA.

Bhavik’s has presented us with a neat formula to determine how many working days are in a month.
This functionality has a wide range of uses in accounting, payroll, staff scheduling, accommodation planning & reporting and general scheduling activities.

Today in Formula Forensics we pull Bhavik’s Formula apart and see what is going on inside and the offer some extensions to increase the functionality of his formula.

I have imported some data that comes in as a number that I need to convert to h:mm. The data string will be either 1,3,4,5,6 integers long and looks like this…

Help