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 this:
[Note: thanks to SDK who asked this question in a comment]
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.
Method 1: Using LOOKUP formula
Assuming the data is in range,
- Dates in $C$3:$F$3
- x marks in C4:F4
We can use =LOOKUP(“y”, C4:F4, $C$3:$F$3) to find the last date.
Why find y? Simple, since our data has “x” against date & name combinations, we just find the next letter (y). So LOOKUP formula stops looking after finding the last x. You see, LOOKUP formula assumes the list is sorted, so if it cannot find a match (in our case – y), it would return the closest match (ie, the last x).
Related: Comprehensive guide to Excel VLOOKUP & other LOOKUP formulas.
Method 2: Using MAX & SUMPRODUCT
I am like Gus Portokalos in My Big Fat Greek Wedding movie. SUMPRODUCT is my Windex. I use it for tough data, complex questions, sores, minor ailments & occasional car dents.
In this case, a formula like =SUMPRODUCT(MAX(($C$3:$F$3)*(C4:F4=”x”))) would do the job.
To be honest, just MAX(($C$3:$F$3)*(C4:F4=”x”)) would do too, but then you have to CTRL+Shift+Enter it.
How does it work? Since dates are just numbers, we take the dates & multiply them wherever there is x. So, it would be,
{41091,41092,41093,41094}*{TRUE,TRUE,FALSE,FALSE}
Which will be,
{41091,41092,0,0}
Then we find the maximum of this, which is 41092 (the number corresponding to 2-jul-2012).
Since this is an array operation, we can either CTRL+Shift+Enter it or wrap it inside SUMPRODUCT, like the G(r)eeks do. And that gives the answer.
More on this: Writing MAXIF formula in Excel, Introduction to SUMPRODUCT
Download Example Workbook
Click here to download example workbook. I have a bonus homework form you in there. Go ahead and solve it.
Your Homework
Time for a quiz. How would you find last date for a given name, if your data is like this?
Go ahead and share your answer.
6 Responses to “Nest Egg Calculator using Power BI”
Wow! What a Powerful article!
Hello Chandoo Sir
your file does not work with Excel 2016.
how can I try my hands on this powerful nest egg file ?
thanks
Ravi Santwani
@Ravi... this is a Power BI workbook. You need Power BI Desktop to view it. See the below tutorial to understand what Power BI is:
https://chandoo.org/wp/introduction-to-power-bi/
As always, superb article Chandoo... 🙂
Just one minor issue:
While following your steps and replicating this calculator in PowerBI, I found that the Growth Pct Parameters should be set as "Decimal number" not "Whole Number"
OR
we have to make corresponding adjustments in the Forecast formulas (i.e. divide by 100) to get accurate results.
You are right. I used whole number but modified the auto created harvester measure with /100 at end. Sorry I did not mention it in the tutorial.
Instead of
[Growth Pct 1 Value]/12
the monthly rate has to be
(1+[Growth Pct 1 Value])^(1/12)-1
It's a slight difference but in 30 years the future value will be $100k less.