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”.
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.
- Who is the best seller?
- Who is the most active seller?
- Who is the most consistent seller?
- Which seller has fewest invoices?
Let’s go ahead and answer these using Excel. Shall we?
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.
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.
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?
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.
Dressing Financial Statements – What Motivated Mr. Bean to Defraud Latte?
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…