All articles in 'Formula Forensics' Category
A clever technique to simplify your long, nested IF formulas

Recently I used an elegant and smart technique to simplify lengthy nested IF formulas and made them 80% shorter. In this article, let me explain the process and share the results.
Continue »
Learn how to develop a Conditional Rank or Rankifs formula in Excel
Continue »
Use the new Textjoin() function to reverse a string of characters suing a formulaic solution.
Continue »
Learn how to convert a Roman Numeral to a Number using this nifty formula. No VBA required.
Continue »
Lets look at how to apportion sales according to multiple criteria
Continue »Formula Forensics No. 039 – Find the Cell Address for a value (2D & 3D Reverse Lookup)

Find the Cell Address for a value from a 2D or 3D Range
(2D & 3D Reverse Lookup)

Learn how to find which worksheet a max or min value occurs on using this neat formula
Continue »
A Formula based solution to extract records from a table that match a number of criteria.
Continue »What is the average speed of this road trip? [Solution & Discussion]
![What is the average speed of this road trip? [Solution & Discussion]](https://img.chandoo.org/hw/time-per-50-mile-laps.png)
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 Tables
Continue »Calculating Maximum Change [solutions & discussion]
![Calculating Maximum Change [solutions & discussion]](https://img.chandoo.org/hw/maximum-change-problem-solutions-and-video.jpg)
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?
Formula Forensics No. 035 Average the last 3 values greater than 0

Learn a technique to Average the last 3 values from a range where the values are greater than 0
Continue »Formula Forensics No. 34. Extract words from a cell, where they occur in a list of words.

Use Excel to extract matching words from a list of words from a cell containing a sentence into individual cells in the order they exist
Continue »
Today, Formula Forensics examines 3 techniques to determine an interpolated value along a curve.
Continue »