All articles with 'row()' Tag

Excel formula to get a match from a given value in excel when you have start and end values in lookup table. This technique is useful for looking up a matching price from a pricing tier table or date in a range of start and end dates.

Use the new Textjoin() function to reverse a string of characters suing a formulaic solution.

Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like above.

And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.

Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).

So how would you write the lookup formula?

Learn how to convert a Roman Numeral to a Number using this nifty formula. No VBA required.

Find the Cell Address for a value from a 2D or 3D Range
(2D & 3D Reverse Lookup)

We all know about the MAX formula. But do you know about 3D Max?

Sounds intriguing? Read on.

Lets say you are the sales analyst at ACME Inc. Your job involves drinking copious amounts of coffee, creating awesome reports & helping ACME Inc. beat competition.

For one of the reports, you need to find out the maximum transactions by any customer across months.

But there is a twist in the story.

Your data is not in one sheet. It is in multiple sheets, one per month.

How to Count and Sum data from Filtered Tables

Here is a New year gift to all our readers – free 2014 Excel Calendar & daily planner Template.

This calender has,

• One page full calendar with notes, in 4 different color schemes
• Daily event planner & tracker
• 1 Mini calendar
• Monthly calendar (prints to 12 pages)
• Works for any year, just change year in Full tab.

Use Excel to extract matching words from a list of words from a cell containing a sentence into individual cells in the order they exist

Welcome back to yet another gripping episode of “When good formulas go GREAT”. We’re working up the singles charts to the number one hit “Formula Challenge 001 – Return everything in string after first block of numbers” By way of a quick refresher, this formula challenge calls for a formula to return a substring from […]

Today we look at the third place winner from our inaugural Formula Challenge, a neat solution provided by Sajan.

This new series introduces a new author to Chandoo.org, Jeff.
Jeff will pose a number of Excel based Formula Challenges and then explain how some of the solutions work in a manner similar to the Formula Forensics series.

In this first post of the series we look at how to extract a string from the right of a block of Numbers in a larger string.

Here is a New year gift to all our readers – free 2013 Excel Calendar Template.

This calender has,

• One page full calendar with notes, in 4 different color schemes
• Daily event planner & tracker
• 1 Mini calendar
• Monthly calendar (prints to 12 pages)
• Works for any year, just change year in Full tab.

There are many web pages devoted to Excel Charts and there are many web pages devoted to Creating Dynamic Ranges, but a lot of these suffer when the data is poorly organised or non-contiguous.

Today we look at a Technique for Creating Dynamic Charts with Non-Contiguous Data.

Today at Formula Forensics, Guest author Sajan shows us how to extract a sorted, unique list of items, displaying the most frequently occurring items first, while restricting the output based on some additional criteria.