All articles in 'Formula Forensics' Category
A Formula based solution to extract records from a table that match a number of criteria.Continue »
Last week, we had an interesting homework problem – What is the average speed of this road trip?
We received more than 150 answers. But to my surprise, 57 of them are wrong. So today, lets learn how to calculate the average speed correct way.Continue »
How to Count and Sum data from Filtered TablesContinue »
Last Friday, we had a fun little Excel challenge – Calculate Maximum Change. More than 170 people commented and shared their solutions to this problem.
And the best part?
The best part is the variety of solutions & thinking displayed by our community. So if you are one of those 170, puff your chest & pat yourself on the back. Go ahead, I will wait.
Today, lets take a look at some of these awesome formulas and understand how they work. Read on and watch the video you below to gain few awesomeness pounds.Continue »
In this episode of Formula Forensics, lets talk about how to build a model where costs vary per year and age. In this example, we are talking about a plantation project. The costs & yields of plants vary by the year they are planted in and the age of plants. In this case, how do we calculate the total costs or returns with formulas?
Learn a technique to Average the last 3 values from a range where the values are greater than 0Continue »
Use Excel to extract matching words from a list of words from a cell containing a sentence into individual cells in the order they existContinue »
Today, Formula Forensics examines 3 techniques to determine an interpolated value along a curve.Continue »
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.Continue »
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.Continue »
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.Continue »
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 formulaContinue »