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/formula-forensics/

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 4th 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 Non-Contiguous Data	Create Dynamic Charts with Non-Contiguous 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	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.