Author Archive

Over Easter, while we were all busy eating our Easter Eggs, at the Chandoo.org/Forums, Slk213 was worried about how to retrieve the Nth number from a row of data which had gaps in it.

Today in Formula Forensics we will look at how to solve this problem.

Today Faseeh, from the Chandoo.org Forum, joins Chandoo.org as a contributor with his first post.
Faseeh, helps find a solution to Maljzm’s problem of looking up time values from a list and comparing them to determine if a value meets some criteria.

“I was trying to calculate the average DJIA # by month. I had the data by day so tried various combos (none of which worked) (:-”

Learn how to help Suzanne average the DJIA on a particular day and learn about a new Excel Function all in one post.

Cornelia wanted to cumulatively increases prices to a lot of products.
See how it was accomplished using an Array Formula to assist.

Did you know you can apply any text effect to a single character or group of text characters within a cell ?

This post will show you 2 quick tips to assist you in this technique.

“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.

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.

;
Today we look at a very neat way of doing a complex Nested If or Vlookup style problem with a simple but beautiful Sumproduct based formula.

Lykes asked Formula Forensics “How does a formula work?” Lykes has been using the formula without understanding it.
The formula counts how many times the letters from a list of letters occur in a list of words.
Today we look at a Lykes question.

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.

In the past here at Chandoo.org and at many many other sites, people have asked the question
“How can I display a number multiplied or divided by 10, 100, 1000, 1000000 etc, but still have the cell maintain the original number for use in subsequent calculations”.

Typically the answer has been limited to “It can’t be done” or “it can only be done in multiples of 1000”.

This post will show you how you can display numbers whilst Dividing or Multiplying the cells value by any Power of 10 !

Houston, We’ve Had a Problem!
In the initial emails requesting a solution to yesterday’s Formula Forensics, Chandoo’s solution, although Technically correct, Didn’t work ?
This post looks at the problem and what was wrong with the data causing the error.

Today in Formula Forensics we look at how to Calculate the Maximum value from a list subject to other fields meeting certain criteria:

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

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.