in this video we'll go over 5 Advanced Excel formulas to make you an expert user let's go
coming in at number five we've got the offset match formula and here's the scenario for it
and you can download this Excel file in the description for free as well so you can follow
along so you can see here that we're starting up a new project with the revenues the costs
over here and the profit and so we're not really sure how this project is going to move over time
whether it's going to do better next year and so on and so we've actually gone ahead and set up a
few different Revenue assumptions to account for that so you have a worst case a base and the best
case over here and based on that we would like to see how our financials are going to vary so when
when we change this base case over here to a worst we'd like to see how all our numbers are going to
change now to set this up first let's work on the revenue line up here and then we'll use the offset
match formula down below so first up for the revenue of the following year it's simply going
to be equals to the previous years multiplied by in Brackets 1 plus the the growth rate over here
let's say we go we're just gonna put it over here for now because this is what's going to
be dynamically updated close those brackets and hit enter we're just going to move this across so
shift right arrow and hit Ctrl R now that we have the income statement set up we can get working on
the selected growth rate so basically depending on what this is we should get the worst case the
base or the best and so to do that we're gonna use first the offset formula so we'll go equals offset
and you can see the description here it's basically going to look into a certain
reference and from there you can specify it to go up by a certain number of rows down
Etc so we'll hit offset for now hit the top key the reference for us is actually the same cell
itself so it's going to be D12 comma the rows here that's where we're gonna add the match function
hit the match there and you can see this is actually going to give us the relative position of
an item in an array so we'll specify all of that hit the top key there and towards the local value
so what we're interested in is really what case somebody puts up here we'll hit the F4 key that's
going to lock it for us so whenever we copy this across it's gonna stay remained it's gonna stay
in the cell instead of moving along as well comma the lookup array is we wanted to find it within
this whole range over here so Ctrl shift down arrow we'll select those over here hit the F4 key
comma and the match type we want it to be an exact match so we'll put a zero in there close
those brackets we gotta hit a comma one more time and then close the brackets again this time for
the offset so we hit enter there that should give us the one percent we're just gonna go
shift right arrow to drag this across and then Ctrl r now you can see how all the numbers are
actually Auto populating if I go ahead and change this from the worst case to something better
like say I go to the the base case you can see how our numbers are improving and so is
our profitability and same thing goes with the best case next up at number four we'll cover
some text functions so we'll go control page down and over here you can see that we're selling all
of these different products so all of these are really fashion items and we have the price for
them what we want to do is increase the price by them by 10 percent maybe to adjust for inflation
or something like that so I would simply go equals the price and multiply that by 1.1 and hit enter
but the problem here is that we get this value sign and the reason for it is that these prices
over here are actually formatted as text and so we need to do a few different adjustments through
formulas to eventually get to an answer there so first things first you'll notice that we have this
column here when it really should be a DOT so what we're going to have to do is use the substitute
formula to change the comma to a DOT so we'll go equals substitute hit the top key there and the
text is this one we're interested in and so within the alt text what we want to switch is that comma
so I'm just going to put it in quotations put a comma in there close the quotations hit a comma
to get to the new text now and within the new text again quotations and I'm gonna put a dot close the
quotations comma and this we don't really need so I'm just going to close it ahead of that actually
now hit enter there and you'll see that the number is actually now changed to a DOT which is how it
should be but we're still missing the fact that we need to change it to a number because right now
it's still as a text so at the very front of the formula I'm just going to put the value function
and you can see here that it converts a text ring that represents a number to a number so
that's what we want we'll hit the top key there and at the very end over here I'm just going to
close the brackets and multiply by 1.1 so we account for the price increase of 10 there
hidden through there from here we can just drag this down so double click over there that should
move accordingly getting numbers and text like this is actually quite common especially in CSV
files and if you want to learn more about Excel you can consider taking our Excel for business
and finance course where we teach everything we know about Excel specifically for people either
looking to break into a business or a finance role or those in it trying to level up their Excel game
and what makes this course special is that it's all applied to the real world while we still cover
the theoretical lessons like formatting formulas and charts we also offer case studies that
simulate the type of work you might be assigned in your day-to-day ranging from Financial modeling
cleaning a data set and presenting some visual insights on top of that we also offer several
other courses including PowerPoint valuation and more so if you're interested in checking it out
you can go to the link in the description below all right now moving on to number three and we've
got date functions over here so go to control page down and you can see here that we've got
the sales commissions by all of these employees and how much they've made across these months now
what we're missing here is to put it in quarters because that's typically how they get paid so
we need to find out what quarter this month is in and so for this we can just do first equals month
which is going to help us determine the actual month of the date hit enter there so now we know
that it's going to be in month one that being said this obviously isn't all that helpful
for finding a quarter because we already know January is month one from here what we what we
can do is do something like a round up which is going to round up the number to a one but we're
actually gonna divide it by three so one over three is gonna be rounded up to one which makes
sense because it's gonna be in the first quarter however when we reach April it's gonna be four
over three that's gonna be rounded up to two for Quarter Two which makes sense so I'll go around
you can see the round up here as the third one hit the top key and so for the number
we're okay with it being the month itself comma there and we're gonna for the number of
digits we're gonna put zero here but actually just before that we need to multiply this by
actually just we need to divide this by three so I'm just going to close the brackets again there
and divide by three so this is going to be one over three rounded up which is gonna equal one
for after that zero there just as a number of digits we'll close the brackets and hit enter
for the month of January nothing's actually changed here but if I press Ctrl C and move
this across to April here you can see that it goes up to a two as it should because it's actually the
second quarter and now to I guess reformat this a bit we need to change it into a quarter format
somehow so what we're gonna do here is at the very beginning I'm just gonna go um quotations so just
like so put a q here close the quotations there and then I'm gonna do an ampere sign which is
gonna basically link it to the formula just hit enter there to see what that looks like and you
can see that it now says q1 which is great now we also we'd also need to add something like the year
at the very end so we'll go inside the formula towards the end ampere sign and what I'm gonna
do here is first put in quotations just a dash to add a bit of Separation close the quotations again
ampersine and this time we want the year so put the year formula we want to select the
year that's going to be inside this date close those brackets and hit enter there now you can
see it's q1 2022 as I drag this across so shift right arrow take control r that
should move accordingly and you can see how that's dynamically updated all across these time periods
awesome now moving on to the last two and first we've got the X lookup and the wild card I know
this sounds a bit confusing so let's take a look go to control page down here and you can see the
scenario where we've got the name of a company and we want to find out the sales amount so in
this case we want to find out for Nike but if we look closely at Nike it's actually called Nike
Inc and so because of that a regular X lookup or a vlookup is not going to work because we don't have
the exact name over here so for the sale amount what we can do instead is go equals xlookup hit
the top key there and the local value for us isn't just Nike because that wouldn't quite work in that
it's not identical we're actually gonna put Nike and an ampere sign there and then in quotations
we're just gonna add this asterisk close the quotations and comma there the lookup Curry is
going to be all of the companies here and so what the asterisk is gonna do is it's gonna say okay
it's it's Nike and there's something else after it which we don't really Define we just say that
there should be something after as well there hit the comma key and the return what we're interested
as the output is going to be the sale amount for us so Ctrl shift down there comma if not found
we don't really want anything there so we'll put a comma again and for the match mode here
what we're gonna want is the Wild Card character much which is the very last one there the two so
just put a two in there close those brackets and hit enter there now you can see that it's
being able to detect Nike Inc even though it's just called Nike same thing goes if I type say
Amazon hit enter there it's called Amazon UK but it's still able to detect it at this exact amount
and finally we're at the last function which is a sort and filter so we'll go control page down over
here to look at the example and more specifically we want to find out the employee's salaries for
those that are in the finance department and that are in New York City so over here we have all of
the different data and first we're just going to use the filter formula so we'll go equals filter
hit the top key there and the array for us so all the area we're interested in is all this
data set so Ctrl shift down Ctrl shift right comma and here's where we're going to specify
all the different conditions so in Brackets here first we're gonna specify that they need to be
in the finance department so I'm just gonna Ctrl shift down here and select the finance department
equal that more specifically to finance so in quotations I'm gonna say Finance
close the quotations there close the brackets and the second condition has to do with New
York city so what we're gonna do here to add a second condition is actually put an asterisk
open up the brackets and see all of these locations here Ctrl shift down should be
equal to in quotations again don't forget that New York City make sure you you spell it the
exact same if not it's not going to be able to detect it close those brackets and close the
brackets again and hit enter there and now you can see that the filter function is gonna find
the employees in finance in New York City and how much they're making now based on this you can see
that it's not really an order so one thing we can add is a sort of sorts that's gonna tell us that
hey let's put this say from highest to lowest in terms of salaries so at the very beginning here of
the filter I'm just gonna put sort press the top key there we're fine with this being the array
but in the sort index comma this is basically from left to right we're interested in salaries
as the Sorting condition you could say so the index so we'll put a 4 there because it's the
fourth one the salary is comma the story there we want it in descending order so from highest
to lowest we're just going to close those brackets and hit enter there now you can see that Sebastian
battle seems to be the one that earns the highest salary and then it goes down from there for more
on Excel check out this video over here going over the top 10 formulas or this link over here
to check out our Excel course hit the like and then subscribe and I'll catch you in the next one