All articles in 'Formula Forensics' Category

There are many web pages devoted to Excel Charts and there are many web pages devoted to Creating Dynamic Ranges, but a lot of these suffer when the data is poorly organised or non-contiguous.

Today we look at a Technique for Creating Dynamic Charts with Non-Contiguous Data.

Today lets tackle a very familiar problem. You have a bunch of very long, complicated file names & paths. Your boss wants a list of files extracted from these paths, like below:

Of course nothing is impossible. You just need correct ingredients. I cannot help you with a strong cup of coffee, so go and get it. I will wait…

Back already? well, lets start the formula magic then.

Your the production manager and have a need to schedule uneven resource across uneven requirements, how can you do that?
Well today we’ll look at Resource Allocation and Scheduling using Excel to do the hard work

Today at Formula Forensics, Guest author Sajan shows us how to extract a sorted, unique list of items, displaying the most frequently occurring items first, while restricting the output based on some additional criteria.

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.

We know that using VLOOKUP, we can find a value corresponding to a given item. For example Sales of x. But what if you have multiple sales for each item and you want the last value?

Today lets understand how to find the last date of an activity, given data like above.

Like everything else in Excel, there are multiple ways to finding last date. If cats can use computers, they would hate Excel. You see, Excel is overflowing with unlimited ways to skin a cat.

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 ?

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.

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.