fbpx
Search
Close this search box.

All articles in 'Formula Forensics' Category

Formula Forensic No 019. Converting uneven Text Strings to Time

Published on Apr 19, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensic No 019. Converting uneven Text Strings to Time

I have imported some data that comes in as a number that I need to convert to h:mm. The data string will be either 1,3,4,5,6 integers long and looks like this…

Help

Continue »

Formula Forensics 018. Retrieving the Nth number from a Range which has Gaps.

Published on Apr 12, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 018. Retrieving the Nth number from a Range which has Gaps.

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.

Continue »

Formula Forensics No. 017 – A Solution to Maljzm’s problem.

Published on Apr 5, 2012 in Formula Forensics, Posts by Faseeh
Formula Forensics No. 017 – A Solution to Maljzm’s 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.

Continue »

Formula Forensics 016. Suzannes DJIA Average

Published on Mar 29, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 016. Suzannes DJIA Average

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

Continue »

Formula Forensic No. 015 – Cornelia’s Price Rises

Published on Mar 8, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensic No. 015 – Cornelia’s Price Rises

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

Continue »

Formula Forensic 014 – Faseeh’s Formula

Published on Mar 1, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensic 014 – Faseeh’s Formula

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

Continue »

Formula Forensics 013. – On Vacation

Published on Feb 21, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 013. – On Vacation

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.

Continue »

Formula Forensics 012. – A Neat Formula

Published on Feb 16, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui

Chandoo.org;
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.

Continue »

Formula Forensics 011. Lykes Formula

Published on Feb 9, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 011. Lykes 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.

Continue »

Formula Forensics No. 010 Count How Many Times a List of Values Occurs in a Range

Published on Feb 1, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 010 Count How Many Times a List of Values Occurs in a Range

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.

Continue »

Formula Forensics No. 008 – Elkhan’s MaxIf

Published on Jan 24, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 008 – Elkhan’s MaxIf

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

Continue »

Formula Forensics. 009 – Pradhishnair’s Chainage Problem

Published on Jan 17, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics. 009 – Pradhishnair’s Chainage Problem

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

Continue »

Finding Friday the 13th using Excel (and learning cool formulas along way)

Published on Jan 13, 2012 in Formula Forensics, Learn Excel
Finding Friday the 13th using Excel (and learning cool formulas along way)

Not that I have friggatriskaidekaphobia or anything. But since today is Friday & 13th, lets put our Excel skills to test and find out when the next Friday the 13th is going to be.

Continue »

Formula Forensics. No 007 – Sumproduct

Published on Dec 21, 2011 in Formula Forensics, Huis, Learn Excel, Posts by Hui
Formula Forensics. No 007 – Sumproduct

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.

Continue »

Formula Forensics 006. Palindromes

Published on Dec 15, 2011 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 006. Palindromes

Palindrome: – A palindrome is a word, phrase, number, or other sequence of units that can be read the same way in either direction, with general allowances for adjustments to punctuation and word dividers (“Wikipedia”).

This week in Formula Forensics we’re having a look at determining if a cell contains a palindrome.

Continue »