Search

# All articles with 'Formula Forensics' Tag

## A clever technique to simplify your long, nested IF formulas

Published on Apr 11, 2023 in Formula Forensics, Learn Excel

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 »

## Formula Forensics 040 – Apportioning Sales by Criteria

Published on Feb 15, 2016 in Excel Howtos, Formula Forensics, Huis, Posts by Hui

Lets look at how to apportion sales according to multiple criteria

Continue »

## Formula Forensics No. 003b – Lukes Reward – Part II

Published on Nov 10, 2014 in Formula Forensics, Huis, Posts by Hui

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]

Published on Aug 18, 2014 in Excel Howtos, Formula Forensics

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 »

## Calculating Maximum Change [solutions & discussion]

Published on Mar 26, 2014 in Excel Howtos, Formula Forensics

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 »

## Extract file name from full path using formulas

Published on Oct 23, 2012 in Excel Howtos, Formula Forensics

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 »

## Formula Forensics No. 031 – Production Scheduling using Excel

Published on Oct 11, 2012 in Excel Howtos, Formula Forensics, Posts by Sajan

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

Continue »

## Find the last date of an activity

Published on Jul 3, 2012 in Formula Forensics, Learn Excel

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.

Continue »

## Formula Forensics 023. Count and Sum a Filtered List according to Criteria

Published on Jun 7, 2012 in Formula Forensics, Huis, Posts by Hui

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

Continue »

## Formula Forensic No 019. Converting uneven Text Strings to Time

Published on Apr 19, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui

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

Continue »

## Formula Forensic 014 – Faseeh’s Formula

Published on Mar 1, 2012 in Formula Forensics, Huis, Posts by Hui

“I have a large list which includes blanks, I want to retrieve the list without the blanks”
Faseeh, responded with a neat array formula.

Today Formula Forensics pulls Faseeh’s Formula apart to see what makes it tick.

Continue »

## Formula Forensics 013. – On Vacation

Published on Feb 21, 2012 in Formula Forensics, Huis, Posts by Hui

Formula Forensics is on Vacation.

Formula Forensics is actually on a working Holiday and has visited the Excel Hero Academy this week.
So today in Formula Forensics we will showcase some of the work that have been derived from the learning’s at the Excel Hero Academy.

Continue »

## Formula Forensics No. 010 Count How Many Times a List of Values Occurs in a Range

Published on Feb 1, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui

Count How Many Times a List of Values Occurs in a Range
(or How Can I Simplify My Formula)

Today in Formula Forensics we look at how to count how many times a range of values occurs within a Range of cells and in the process simplify a very nasty formula.

Continue »

## Formula Forensics. 009 – Pradhishnair’s Chainage Problem

Published on Jan 17, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui

A common Forum Post question and one that Chandoo has written about a few times is, Does my data overlap with another range?

This week Formula Forensics examines Pradhishnair’s Overlapping Chaninage Problem where he wants to know if two values overlap with a range of other values

Continue »

## Formula Forensics. No 007 – Sumproduct

Published on Dec 21, 2011 in Formula Forensics, Huis, Learn Excel, Posts by Hui

One of the most asked questions within the posts and Forums at Chandoo.org is, How Does Sumproduct work ?

Today in Formula Forensics we take at look at just that with a few worked examples.

Continue »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.