All articles with 'max()' Tag

VLOOKUP the last value

Published on Aug 11, 2015 in Excel Howtos, Learn Excel
VLOOKUP the last value

VLOOKUP is one of the most useful Excel functions. So much so that I even wrote a book about it. But it has one serious limitation.

It looks up the first occurrence and returns corresponding data.

What if you want to find the last value?

Say, for example, you are looking at a task assignment list and want to know what is the last task assigned to employee Emp13?

We want to extract the task “Make amazing workbook”. Of course our good old VLOOKUP stops once it finds Emp13 and returns the answer as “Create intuitive workbook”.

Continue »

Who is the most consistent seller? [BYOD]

Published on Feb 18, 2015 in Analytics, Excel Howtos, Pivot Tables & Charts
Who is the most consistent seller? [BYOD]

Who is the most consistent of all?

Imagine you are a category manager at a large e-commerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Above is a snapshot of such invoices.

Looking at this list, you have a few questions.

  1. Who is the best seller?
  2. Who is the most active seller?
  3. Who is the most consistent seller?
  4. Which seller has fewest invoices?

Let’s go ahead and answer these using Excel. Shall we?

Continue »

3D Max Formula for Excel

Published on Sep 9, 2014 in Excel Howtos, Learn Excel
3D Max Formula for Excel

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.

Continue »

Calculating Maximum Change [solutions & discussion]

Published on Mar 26, 2014 in Excel Howtos, Formula Forensics
Calculating Maximum Change [solutions & discussion]

Last Friday, we had a fun little Excel challenge – Calculate Maximum Change. More than 170 people commented and shared their solutions to this problem.

And the best part?

The best part is the variety of solutions & thinking displayed by our community. So if you are one of those 170, puff your chest & pat yourself on the back. Go ahead, I will wait.

Today, lets take a look at some of these awesome formulas and understand how they work. Read on and watch the video you below to gain few awesomeness pounds.

Continue »

Calculate maximum change [homework]

Published on Mar 21, 2014 in Formula Challenges
Calculate maximum change [homework]

Today, lets see how good your formula skills are.

Calculate maximum change

Can you calculate what is the maximum change in product sales between 2 months for above data?

Continue »

Highlight best week & month in a trend chart [tutorials]

Published on Dec 12, 2012 in Charts and Graphs
Highlight best week & month in a trend chart [tutorials]

When analyzing business data like sales, shop visits or productivity, one of the questions managers always ask is,

What is the best month / week for this data?

To answer this question, we need to make a chart that looks like above. Today, lets learn how to highlight portions of such charts that correspond to best week or best month.

Continue »

Dressing Financial Statements – What Motivated Mr. Bean to Defraud Latte?

Published on Dec 11, 2012 in Financial Modeling

Did you know What Happened at Last Coffee Day?
Mr. Bean “dressed up” the financial statements and was caught in the fraud. But he was the CEO of Latte! So why did he commit fraud in his own company??

Any Guess?

Take a cappuccino and I will give you a hint – How was Mr. Bean’s Bonus to be decided?

Read on to know more…

Continue »

Find the last date of an activity

Published on Jul 3, 2012 in Formula Forensics, Learn Excel
Find the last date of an activity

We know that using VLOOKUP, we can find a value corresponding to a given item. For example Sales of x. But what if you have multiple sales for each item and you want the last value?

Today lets understand how to find the last date of an activity, given data like above.

Like everything else in Excel, there are multiple ways to finding last date. If cats can use computers, they would hate Excel. You see, Excel is overflowing with unlimited ways to skin a cat.

Continue »

Use MAX to find latest date in a list [Quick tip]

Published on Jun 14, 2012 in Excel Howtos

Here is a quick tip that I learned while conducting training classes in Australia. If you have several dates in a range and you want to find out what the latest date is, just use MAX, like: =MAX(A1:A10) would give you the latest date. A Question…, Assuming you have some dates (not necessarily sorted) in […]

Continue »

Formula Forensic 020. Bhavik’s Monthly Workingdays Formula

Published on May 3, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensic 020. Bhavik’s Monthly Workingdays Formula

Bhavik’s has presented us with a neat formula to determine how many working days are in a month.
This functionality has a wide range of uses in accounting, payroll, staff scheduling, accommodation planning & reporting and general scheduling activities.

Today in Formula Forensics we pull Bhavik’s Formula apart and see what is going on inside and the offer some extensions to increase the functionality of his formula.

Continue »

Cleaning Up Imported Data – A Recent Case Study

Published on Jan 25, 2012 in Excel Howtos, Huis, Posts by Hui
Cleaning Up Imported Data – A Recent Case Study

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.

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 »

Using an Array Formula to Find and Count the Maximum Text Occurrences in a Range

Using an Array Formula to Find and Count the Maximum Text Occurrences in a Range

Chandoo is off on Holidays and once again thrown me the keys to Chandoo.org.

So this week we’re going to:

  • Pull apart an Array Formula and see what’s under the hood;
  • Have a look at a technique for quickly developing Custom Number Formats &
  • Look at how we can use HTML Codes in posts at Chandoo.org to spice up your questions and answers.

Today is Tarun’s Problem

“I have got multiple names in each row and would like to have what name is repeated maximum number of times and how many times?”

Today we pull apart an answer to this question and see how it works.

Continue »

Simple Excel Formula to Calculate All-time High, Trailing 12 Month High Values [Quick Tip]

Published on Feb 10, 2011 in Learn Excel
Simple Excel Formula to Calculate All-time High, Trailing 12 Month High Values [Quick Tip]

It is not too sunny here, but I am going to put on my business man hat. At the end of each month, I ask myself if my business (chandoo.org that is) has performed better or worse. One simple way is to look at previous month’s numbers and then I know how good the latest […]

Continue »

Scheduling Variable Feed Sources

Published on Nov 18, 2010 in Excel Howtos, Posts by Hui
Scheduling Variable Feed Sources

In many industries, bulk commodities are received or delivered in batches or parcels of various quantities and with various properties.
This post will look at one option for scheduling these commodities within Excel.

Continue »