There is a classic Financial Modeling problem in Excel. You are checking values in each month and you end up with a row of zero and one values. How can you find the last month where you have a one?
Dan explains the classic technique of a Helper Row to identify the last one and then INDEX and MATCH.
Bill uses the Last to First Search Mode in XLOOKUP.
But then the shortest method - MAXIFS as suggested by Diarmid Early.
Table of Contents
(0:00) Welcome and the problem
(2:54) Solving with a helper row
(6:00) Using XLOOKUP
(7:47) Using MAXIFS
Dan explains the classic technique of a Helper Row to identify the last one and then INDEX and MATCH.
Bill uses the Last to First Search Mode in XLOOKUP.
But then the shortest method - MAXIFS as suggested by Diarmid Early.
Table of Contents
(0:00) Welcome and the problem
(2:54) Solving with a helper row
(6:00) Using XLOOKUP
(7:47) Using MAXIFS
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2445. Find the last one in a row of zeros and ones.
With my special guest today, Dan Mayo. Welcome back to the MrExcel Netcast.
Dan, it's great to have you.
You were just featured in the last episode 2444 where we were trying to search backwards or reverse a text string.
And while I was talking to you about that, you came up with this interesting problem.
Which is also searching kind of, it's sort of searching from the end of a list as well.
Tell me about this situation here. Yeah, Bill, thank you for having me back.
It's been eight years since I think I did a video full video with you.
Yeah, that was that was the shuffle a deck of cards right?
Yeah, that was indeed yeah which I was just a fancy way of saying, you know, random numbers without duplicates.
So anyway this problem we're looking at today is a pretty common one in financial modeling, which is my world of using Excel.
Essentially, you're having tests that return true or false or zero and one and you know it's not a continuous string. It's some “yes” and some “no”.
And we want to find the last instance of say, the one.
It happens a lot in financial modeling. The test could be anything.
What we're looking at today’s example is just “when i's the cash flow for a period negative?” Just to keep it straightforward to focus on the formulas.
As I was just saying to you a few minutes ago, if the question was find the FIRST instance of a one, it's quite simple.
A simple MATCH function will do it with the last parameter or zero for an exact lookup and that will pick up the first one.
But if we want the last example of a one and particularly it's some one's and some zeros and some one's and some zeros again rather than a continuous block of ones.
We need a different solution.
And so today we get a look at a few different ways to do it. That's cool.
Now it's funny, probably about two weeks ago I had a video where I had to find the only one.
There was only one and everything else was an error.
So it was a one single number in a sea of errors and I was able to use the MATCH looking for a very large number.
But that's not going to work, that trick won't work here, because we have multiple ones.
And so if I tried that same method with the binary search and it's going to it's not going to work at all.
Absolutely.
And as you know, when you do approximate lookups where the number that's not in the set when it's not ordered, you get all sorts of unexpected results. OK, so we have three different methods today.
The the traditional method we're going to talk about first.
And then I'm going to show a faster method thanks to Microsoft 365.
And then what we think, what we both think is probably the clever way to go.
All right, so… So the traditional way is basically to use a helper row. We like doing that in financial modeling.
There's no bonus points for doing something in the most condensed set of cells possible.
We we like to focus on clarity. Ease of reviewing for reviewer and for a user.
So the helper row - we just need something that again will return ones and zeros.
But is only get a return a single one in the instance we want.
There's a few different ways to do this.
They all involve looking forward In financial modeling, we like one one formula per row that we can drag across.
So we look for something we can write in D13 and then drag it across the row as a consistent formula.
And when I say it needs to look forward, which is a little bit of a no-no, we avoid it where we can, but when there's no other option, that's what we do.
So forward looking, as part of the formula in column D we are looking at column E and all the way out to the end.
In this case the data ends in column O and we actually write the formula to anchor it in column P.
We do that just so when we drag it across to including column O, Excel doesn't move the dollar sign as then our auditing tools flag it as a different formula in that last column. Perfect, OK.
And as you can see, the formula is just simply looking at two conditions.
We need a one in the current period and we need all the future periods to be 0.
Yeah, there's a lot of ways he could do that.
I just like a simple AND function and then a one times at the start to turn the True/False result from the AND into a one or a zero.
Nice that makes sense. Makes sense. Alright good, so now you have this single one.
Yes, now we have that single one.
It's just a much simpler problem of finding you where is the one in a string where everything else is 0.
Index match is how we train our brains to to to be the first solution we always think in a problem.
And so there that's just a very simple standard index match.
VLOOKUP or HLOOKUP in this case could do the same. LOOKUP, XLOOKUP and so on.
And just below, I show you a probably a more straightforward way, which is SUMIF.
The SUMIF only works if what we've got in row four is numerical.
Whereas the Index/Match of course will work if there's text labels in row four as well.
OK.
But being financial modelling its typically a date stream we have up the top, which is of course numerical.
And that's typically what we're looking to get as an answer is a single value from that data.
So really what you're getting is you're getting zero times this date, zero times this date, zero times this date, all the way out until one times this date, and then since there's only one 1.
That's the answer that we're getting. That's exactly right.
That is exactly what the SUMIF is doing. SUMPRODUCT of course would have worked as well.
With a secondary argument being row 13 = 1.
But Yep, SUMIF it's simple, perfect, OK, great.
So now thanks to Microsoft 365, this became dramatically simpler because XLOOKUP or XMATCH now has the ability, with this search mode argument, to search from the end. So we have four ones there.
I've got rid of the helper row, we're just trying to figure out where the ones are in this range.
And I can tell the XLOOKUP to look for that one from the end.
Which will return, whatever is in this column, essentially.
So =XLOOKUP. And looking for a one.
Where am I looking for it? I'm looking for it in that range right there.
I'm not copying this anywhere so I don't have to press F4.
What do I want to return? I want to return the dates up here in row four.
Again, no F4. And then I'm watching the little arguments there.
If not found. Well, I know it's going to be found.
So I'm going to leave that blank. Match mode.
We're doing an exact match, which is the default, so I leave that blank.
And then here.
Here we are out in the sixth argument, the search mode, and we're going to search last to first, so a negative one.
And negative one will have that search right to left.
In this case, since it's a horizontal array and returns the September 30th, 2021.
So I'm super happy with that because it's really simple and I can explain it.
But of course the problem is.
Problem is not everyone has access to this, especially when you're writing models for clients.
Yeah, you never know who's going to use it from the client’s and who else they need to share it with.
And so as professional financial modelers we are still not at the point yet where we like to put XMATCH or XLOOKUP in our external models. Gotcha, OK, but there's a third method, right?
There is a third method and I won't take credit for this.
A colleague of ours, Diarmid Early, who I would hope many of you viewers will be familiar with.
He used this in a model on a completely different topic that he sent me and I saw it and I thought oh, that's actually a really good way to solve this problem.
So no helper row, and arguably a more straightforward formula to write than what you just did with XLOOKUP or XMATCH, which is equivalent.
And so here it's just looking at the whole row of ones and zeros.
And it's using that essentially as a condition. Being the if condition around a Max function.
So we're looking, you know, if we did MAX of row four, well then we got to get the last date.
31 December 2021. MAXIFS, which is like the MAX function.
But it says instead of including every cell within the range as part of that MAX we can condition it on something.
And and so here we put in as our condition or we need row eight to equal 1.
And so as you can see the syntax there that row 4 is the range we want to get something returned from.
A criteria range where our ones and zeroes is row 8.
And then what criteria do we need – One.
And that's all there's dot dot dot you can see in the syntax because we could put in second and third conditions if needed but, but here is only one condition. That makes it really simple doesn’t it?
Yeah it does. And so now.
The MAX says I'm going to ignore January 2021 and I'm going to then include February and March.
Ignore April, May. Include June and so on and so on.
And so then it's only taking the Max of four different values in this case And September is the last.
Obviously this needs what is in row four to be sort of sequentially increasing as numbers.
But. again, in a financial modeling context this is always what we get to see.
We always had models with one time period per column and they're always running earliest to latest, left to right. There you go. Alright that that works great.
And again, it has to be dates or numbers up here that are there sequentially increasing.
If it was text like A, B, C, D, E. MAXIFS is not going to deal with that, is it?
I don't think so.
Of course not because Max couldn't take that right? Yeah, of course, right?
So and this will have slightly more accessibility than the XLOOKUP because people using Excel 2019 as the standalone product would have MAXIFS, but they don't have XLOOKUP.
Or people who run Excel 265 but yet to get the updates right now would have this as well right?
This goes back, I think if I recall right, to February of 2017 So a lot more people would have MAXIFS.
At this point, if you're back in Excel 2016, then of course you're still going to have to with the traditional method. it is.
I mean, we not going to talk about it now, but if you're on Excel 2016 you can use MAX, pen bracket, IF open bracket and then enter it with Ctrl+Shift+Enter.
Oh yeah, and it's a slight tweaking of the syntax.
You basically need the if condition to return the word False.
You type in the word False as the Value_If_False argument.
And that's because the MAX and the MIN functions when they're looking at a range of cells, where some are numbers and sSome are the word False, they will just ignore the False. Oh yeah, you can do that too.
But that's another video.
And then I'm thinking of other - like we could use the new FILTER function.
But if people don't have XLOOKUP, they're not going to have FILTER, right? More people would have XLOOKUP than FILTER.
I know I got XLOOKUP before I got the dynamic arrays.
That's right. Alright, that is great.
So Dan, since eight years ago I met you at the Model Off World Championships in New York City.
I did in almost nine years ago, nine years ago, 2012, right?
And I was fascinated.
You know, I follow you on Twitter and I saw that you recently a few years ago. were at a Championship table in poker tournaments.
Tell us tell us about it. Tell us about your poker.
Is that a side gig for you or? Yes.
Obviously still my main source of income still comes from a financial modeling consulting.
The poker has got more serious.
That was at the Aussie Millions, which is like a big poker festival that is on every January down in Melbourne.
It's not as big as the World Series of Poker that goes on in Vegas once a year.
But it's it's still a pretty good series.
And yeah, I won a couple of tournaments down there. One in in January 2019, and one in January 2020.
And for me, many people don't know this. They're only familiar with No Limit Hold'em.
There's actually about, you know, 8, 9, 10 plus different games of poker.
They're not just no-limit hold-em.
A lot of them are limit varieties and a lot of them are stud varieties or draw varieties, and so on.
And that that's the kind of poker I prefer.
And so the two tournaments I won were both in non-no-limit hold-em events. They were in what we call mixed game events.
For me that makes poker a lot more interesting.
And and yeah like to do it whenever I get the chance.
But with you know the recent pandemic, live poker has been harder to find. It's it's coming back now.
Yeah, the World Series is just wrapping up in Las Vegas right now.
I couldn't make it this year but I hope to be over there in June next year.
Wow, that would be that would be great just to to say that I know someone that made it to the Championship table, right? How cool is that?
Yes and I use Excel a lot to analyze the game outside of the table.
You know it's a very numbers-based game to study, and Excel is a great help with that.
Oh, that's a great use for Excel right there. That is great.
Alright, Dan, thanks for joining us today.
Hey, if you like these videos, please down below. Like, Subscribe, Ring the bell.
Feel free to post any questions or comments down in the comments below.
Thanks to Dan for joining us to talk about these three different ways.
We'll see you next time for another netcast from MrExcel. Let's hear you, Nancy.
With my special guest today, Dan Mayo. Welcome back to the MrExcel Netcast.
Dan, it's great to have you.
You were just featured in the last episode 2444 where we were trying to search backwards or reverse a text string.
And while I was talking to you about that, you came up with this interesting problem.
Which is also searching kind of, it's sort of searching from the end of a list as well.
Tell me about this situation here. Yeah, Bill, thank you for having me back.
It's been eight years since I think I did a video full video with you.
Yeah, that was that was the shuffle a deck of cards right?
Yeah, that was indeed yeah which I was just a fancy way of saying, you know, random numbers without duplicates.
So anyway this problem we're looking at today is a pretty common one in financial modeling, which is my world of using Excel.
Essentially, you're having tests that return true or false or zero and one and you know it's not a continuous string. It's some “yes” and some “no”.
And we want to find the last instance of say, the one.
It happens a lot in financial modeling. The test could be anything.
What we're looking at today’s example is just “when i's the cash flow for a period negative?” Just to keep it straightforward to focus on the formulas.
As I was just saying to you a few minutes ago, if the question was find the FIRST instance of a one, it's quite simple.
A simple MATCH function will do it with the last parameter or zero for an exact lookup and that will pick up the first one.
But if we want the last example of a one and particularly it's some one's and some zeros and some one's and some zeros again rather than a continuous block of ones.
We need a different solution.
And so today we get a look at a few different ways to do it. That's cool.
Now it's funny, probably about two weeks ago I had a video where I had to find the only one.
There was only one and everything else was an error.
So it was a one single number in a sea of errors and I was able to use the MATCH looking for a very large number.
But that's not going to work, that trick won't work here, because we have multiple ones.
And so if I tried that same method with the binary search and it's going to it's not going to work at all.
Absolutely.
And as you know, when you do approximate lookups where the number that's not in the set when it's not ordered, you get all sorts of unexpected results. OK, so we have three different methods today.
The the traditional method we're going to talk about first.
And then I'm going to show a faster method thanks to Microsoft 365.
And then what we think, what we both think is probably the clever way to go.
All right, so… So the traditional way is basically to use a helper row. We like doing that in financial modeling.
There's no bonus points for doing something in the most condensed set of cells possible.
We we like to focus on clarity. Ease of reviewing for reviewer and for a user.
So the helper row - we just need something that again will return ones and zeros.
But is only get a return a single one in the instance we want.
There's a few different ways to do this.
They all involve looking forward In financial modeling, we like one one formula per row that we can drag across.
So we look for something we can write in D13 and then drag it across the row as a consistent formula.
And when I say it needs to look forward, which is a little bit of a no-no, we avoid it where we can, but when there's no other option, that's what we do.
So forward looking, as part of the formula in column D we are looking at column E and all the way out to the end.
In this case the data ends in column O and we actually write the formula to anchor it in column P.
We do that just so when we drag it across to including column O, Excel doesn't move the dollar sign as then our auditing tools flag it as a different formula in that last column. Perfect, OK.
And as you can see, the formula is just simply looking at two conditions.
We need a one in the current period and we need all the future periods to be 0.
Yeah, there's a lot of ways he could do that.
I just like a simple AND function and then a one times at the start to turn the True/False result from the AND into a one or a zero.
Nice that makes sense. Makes sense. Alright good, so now you have this single one.
Yes, now we have that single one.
It's just a much simpler problem of finding you where is the one in a string where everything else is 0.
Index match is how we train our brains to to to be the first solution we always think in a problem.
And so there that's just a very simple standard index match.
VLOOKUP or HLOOKUP in this case could do the same. LOOKUP, XLOOKUP and so on.
And just below, I show you a probably a more straightforward way, which is SUMIF.
The SUMIF only works if what we've got in row four is numerical.
Whereas the Index/Match of course will work if there's text labels in row four as well.
OK.
But being financial modelling its typically a date stream we have up the top, which is of course numerical.
And that's typically what we're looking to get as an answer is a single value from that data.
So really what you're getting is you're getting zero times this date, zero times this date, zero times this date, all the way out until one times this date, and then since there's only one 1.
That's the answer that we're getting. That's exactly right.
That is exactly what the SUMIF is doing. SUMPRODUCT of course would have worked as well.
With a secondary argument being row 13 = 1.
But Yep, SUMIF it's simple, perfect, OK, great.
So now thanks to Microsoft 365, this became dramatically simpler because XLOOKUP or XMATCH now has the ability, with this search mode argument, to search from the end. So we have four ones there.
I've got rid of the helper row, we're just trying to figure out where the ones are in this range.
And I can tell the XLOOKUP to look for that one from the end.
Which will return, whatever is in this column, essentially.
So =XLOOKUP. And looking for a one.
Where am I looking for it? I'm looking for it in that range right there.
I'm not copying this anywhere so I don't have to press F4.
What do I want to return? I want to return the dates up here in row four.
Again, no F4. And then I'm watching the little arguments there.
If not found. Well, I know it's going to be found.
So I'm going to leave that blank. Match mode.
We're doing an exact match, which is the default, so I leave that blank.
And then here.
Here we are out in the sixth argument, the search mode, and we're going to search last to first, so a negative one.
And negative one will have that search right to left.
In this case, since it's a horizontal array and returns the September 30th, 2021.
So I'm super happy with that because it's really simple and I can explain it.
But of course the problem is.
Problem is not everyone has access to this, especially when you're writing models for clients.
Yeah, you never know who's going to use it from the client’s and who else they need to share it with.
And so as professional financial modelers we are still not at the point yet where we like to put XMATCH or XLOOKUP in our external models. Gotcha, OK, but there's a third method, right?
There is a third method and I won't take credit for this.
A colleague of ours, Diarmid Early, who I would hope many of you viewers will be familiar with.
He used this in a model on a completely different topic that he sent me and I saw it and I thought oh, that's actually a really good way to solve this problem.
So no helper row, and arguably a more straightforward formula to write than what you just did with XLOOKUP or XMATCH, which is equivalent.
And so here it's just looking at the whole row of ones and zeros.
And it's using that essentially as a condition. Being the if condition around a Max function.
So we're looking, you know, if we did MAX of row four, well then we got to get the last date.
31 December 2021. MAXIFS, which is like the MAX function.
But it says instead of including every cell within the range as part of that MAX we can condition it on something.
And and so here we put in as our condition or we need row eight to equal 1.
And so as you can see the syntax there that row 4 is the range we want to get something returned from.
A criteria range where our ones and zeroes is row 8.
And then what criteria do we need – One.
And that's all there's dot dot dot you can see in the syntax because we could put in second and third conditions if needed but, but here is only one condition. That makes it really simple doesn’t it?
Yeah it does. And so now.
The MAX says I'm going to ignore January 2021 and I'm going to then include February and March.
Ignore April, May. Include June and so on and so on.
And so then it's only taking the Max of four different values in this case And September is the last.
Obviously this needs what is in row four to be sort of sequentially increasing as numbers.
But. again, in a financial modeling context this is always what we get to see.
We always had models with one time period per column and they're always running earliest to latest, left to right. There you go. Alright that that works great.
And again, it has to be dates or numbers up here that are there sequentially increasing.
If it was text like A, B, C, D, E. MAXIFS is not going to deal with that, is it?
I don't think so.
Of course not because Max couldn't take that right? Yeah, of course, right?
So and this will have slightly more accessibility than the XLOOKUP because people using Excel 2019 as the standalone product would have MAXIFS, but they don't have XLOOKUP.
Or people who run Excel 265 but yet to get the updates right now would have this as well right?
This goes back, I think if I recall right, to February of 2017 So a lot more people would have MAXIFS.
At this point, if you're back in Excel 2016, then of course you're still going to have to with the traditional method. it is.
I mean, we not going to talk about it now, but if you're on Excel 2016 you can use MAX, pen bracket, IF open bracket and then enter it with Ctrl+Shift+Enter.
Oh yeah, and it's a slight tweaking of the syntax.
You basically need the if condition to return the word False.
You type in the word False as the Value_If_False argument.
And that's because the MAX and the MIN functions when they're looking at a range of cells, where some are numbers and sSome are the word False, they will just ignore the False. Oh yeah, you can do that too.
But that's another video.
And then I'm thinking of other - like we could use the new FILTER function.
But if people don't have XLOOKUP, they're not going to have FILTER, right? More people would have XLOOKUP than FILTER.
I know I got XLOOKUP before I got the dynamic arrays.
That's right. Alright, that is great.
So Dan, since eight years ago I met you at the Model Off World Championships in New York City.
I did in almost nine years ago, nine years ago, 2012, right?
And I was fascinated.
You know, I follow you on Twitter and I saw that you recently a few years ago. were at a Championship table in poker tournaments.
Tell us tell us about it. Tell us about your poker.
Is that a side gig for you or? Yes.
Obviously still my main source of income still comes from a financial modeling consulting.
The poker has got more serious.
That was at the Aussie Millions, which is like a big poker festival that is on every January down in Melbourne.
It's not as big as the World Series of Poker that goes on in Vegas once a year.
But it's it's still a pretty good series.
And yeah, I won a couple of tournaments down there. One in in January 2019, and one in January 2020.
And for me, many people don't know this. They're only familiar with No Limit Hold'em.
There's actually about, you know, 8, 9, 10 plus different games of poker.
They're not just no-limit hold-em.
A lot of them are limit varieties and a lot of them are stud varieties or draw varieties, and so on.
And that that's the kind of poker I prefer.
And so the two tournaments I won were both in non-no-limit hold-em events. They were in what we call mixed game events.
For me that makes poker a lot more interesting.
And and yeah like to do it whenever I get the chance.
But with you know the recent pandemic, live poker has been harder to find. It's it's coming back now.
Yeah, the World Series is just wrapping up in Las Vegas right now.
I couldn't make it this year but I hope to be over there in June next year.
Wow, that would be that would be great just to to say that I know someone that made it to the Championship table, right? How cool is that?
Yes and I use Excel a lot to analyze the game outside of the table.
You know it's a very numbers-based game to study, and Excel is a great help with that.
Oh, that's a great use for Excel right there. That is great.
Alright, Dan, thanks for joining us today.
Hey, if you like these videos, please down below. Like, Subscribe, Ring the bell.
Feel free to post any questions or comments down in the comments below.
Thanks to Dan for joining us to talk about these three different ways.
We'll see you next time for another netcast from MrExcel. Let's hear you, Nancy.
