Formula Forensics – Homepage
Formula Forensics finally has a home!
Formula Forensics started in Oct 2011 when Chandoo was on Holidays a Work Trip and asked me (Hui) to write a few guests posts during the week.
In one of the posts I documented a formula that had been recently posted in the Chandoo.org Forums and Formula Forensics was born.
Formula Forensics has now grown to 47 posts and is mostly written by Hui with the help of 3 guest authors, Luke, Faseeh and Sajan.
You can get a list of all the Formula Forensics posts by going to: http://chandoo.org/wp/category/formulaforensics/
Below is a table listing all the Formula Forensics posts and a brief description of each.
No. 
Title 
Description 
1 
Using an Array to Find and Count the occurrences of a word  Find which Text value in a row occurs the most and how many times it occurs. 
2 
Joyce’s Question  Count the number of occurrences of words in a range when they’re in cell with other text 
3 
Lukes Reward  Extract a list from a larger list with criteria and no blanks using formula 
3 b  Lukes Reward Part II  Extract records from a list that match multiple criteria 
4 
Fred’s Problem  How to use Sumproduct to multiply and sum values in alternate rows 
5 
Zebra and Checker Boards  How does Zebra Stripe and Checkerboard Formatting work in Conditional Formatting 
6 
Palindromes  Check if the value in a cell is a palindrome. 
7 
Sumproduct  Learn how Sumproduct Works. 
7 b 
Finding Friday the 13th using Excel  Finding Friday the 13th using Excel 
8 
Elkhan’s MaxIf  Learn how to perform a Maxif equivalent function 
9 
Pradhishnair’s Chainage  Check if a range of values overlaps multiple other ranges of values 
10 
Count How Many Times a List of Values Occurs in a Range  Count How Many Times a List of Values Occurs in a Range 
11 
Lyke’s Formula  Find out how many times each record in a list contains an entry from another list of single characters. 
12 
A Neat Formula  A neat way to do multiple nested If’s without using If() 
13 
On Vacation (EHA Showcase)  FF is on vacation and we A Showcase the output of the Excel Hero Academy 
14 
Faseeh’s Formula  Retrieve a list from a larger list without the blank values 
15 
Cornelia’s Price Rises  Apply a % increase to a list of values 
16 
Suzannes DJIA Average  How to do an Averageif function before Averageifs was invented 
17 
A Solution to Maljzm’s problem  How to do a two stage lookup 
18 
Retrieving the Nth number from a Range with Gaps  Retrieve the Nth number from a list with gaps in it 
19 
Extract Time from Strings  Extract Time from Strings 
20 
Bhavik’s Monthly Workingdays Formula  Calculate the number of Working days available in a month 
21 
Extract the 4^{th} Slash  Find the position of the Nth slash in a text string 
22 
Adding up the Odd Numbers between 1 and 100  Add up the Odd or Even numbers between two numbers. 
23 
Counting and Adding Values in Filtered Tables with Criteria  Count and Sum a Filtered List according to Criteria 
23 b 
Find the last date of an Activity  Find the last date of an Activity 
24 
Is my Number a Prime ?  Check if a number is a Prime Number. 
25 
Count Unique Values in a Range  Learn how to count unique values in a range. 
26 
Highlight Only Duplicate Entries  Identify duplicates using a Formula 
27 
Remove Leading Zeroes  Remove leading Zeroes from text 
28 
It’s Just a Jump to the Left  How to VLookup to the left 
29 
SumIf with Inconsistent Column Layouts  Using Sumproduct on uneven column’s 
30 
Extracting a Sorted, Unique List, Grouped by Frequency of Occurrence  Extracting a Sorted, Unique List, Grouped by Frequency of Occurrence 
31 
Production Scheduling using Excel  Learn how to Create Dynamic Schedules 
31 b 
Extract file name from full path using formulas  Extract file name from full path using formulas 
32 
Create Dynamic Charts with NonContiguous Data  Create Dynamic Charts with NonContiguous Data 
33 
Interpolation  How to interpolate values within a range 
34  Extract words from a cell  Extract words from a cell, where they occur in a list of words 
35  Average the last 3 Values greater than 0  Average the last 3 Values greater than 0 
36  Calculating Costs that Vary by Year and Age 
Calculating Costs that Vary by Year and Age 
37  How to Count and Sum a Filtered Data Table  How to Count and Sum a Filtered Data Table 
38 
Find Which Worksheet a Max or Min Value is located on 

39  2D & 3D Reverse Lookups  Find the cell Address of a value from a 2D or 3D range 
40  Apportion Sales with Multiple Criteria  Apportion Sales according to Multiple Criteria 
41  Convert Roman Numerals to Numbers  Convert Roman Numerals to Numbers 
42  Reverse a Text String using a Formula  Reverse a Text String using a Formula 
43  Conditional Rank, Rankifs  Learn how to do Conditional Rank or Rankifs 
Formula Forensics Needs Your Help
We need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share like above, try putting pen to paper and draft up a Post like one of the posts listed above; or
If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.