Search

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

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.

Get FREE Excel & Power-BI Newsletter

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