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 43 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

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

9

Pradhishnair’s Chainage Check if a range of values overlaps multiple other ranges of values

8

Elkhan’s MaxIf Learn how to perform a Maxif equivalent function

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
3 b Lukes Reward Part II Extract records from a list that match multiple criteria
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

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.

 

Your email address is safe with us. Our policies

Comments are closed.