in this video we're going to go over different ways to calculate running
total we'll do formulas that will work both in Excel and Google Sheets
it's going to be exactly the same formula for both so you can just copy paste it's going to produce
the same results I'm going to use Excel for this because it's easier to zoom in and zoom out here
so let's go over calculating running total then we'll go over array methods we can use
to calculate this as an array and then we'll talk about functions like scan and reduce as we
go over these and that will get you some sort of introduction why those functions exist and how you
could use those but let's just start with basic running total so usually to calculate running
total we have two different approaches most of the time one of those is this approach to use
sum function so imagine if I did a sum function in this first line to calculate running total
that would be just this first six thousand dollars so that would be just this now I
could just do that B2 like this hit enter or I could also illustrate that as a range
of a single cell so I'm doing colon another B2 and basically we're saying it's a range of a single
cell if I'd enter that's going to work the same way now if I wanted to do this for this next line
my formula would look like this I would need to do the previous line and current line to get my total
up until here it's eight thousand dollars then if I go to the next line it's gonna be
these three and then we're gonna go like this let's just do one more that should be enough to
illustrate what's going on here like that right so that's gonna be this now let's just quickly
look at these formulas I just did to see what we have in common so I'm gonna go under formulas and
do show formulas so that I don't have to open each formula one by one so we can see what it
is that I just did now if you look here this is my first Formula that calculates for this line
This is the next Formula that calculates the next two lines then the next three lines then
the next four lines and if you look between these two formulas all of them start in B2
and then what they do they end in the current row so like this one because the current row is
this line 2 that would be B2 the next line the ending of the range goes to line three now B3
then here we go line four and then line five so basically the beginning of the range always
Remains the Same B2 the part that changes is the end of the range that goes to the current line
now the way we can accomplish this with a single formula let me go back and remove this show
formula so we can get back to our spreadsheet now we can if we wanted to use one formula so
we can copy down we could do that by going back to this formula remember the first part of the
formula the 2 B2 part was always constant the same so I'm going to go ahead and add a dollar sign for
that 2 so that 2 Remains the Same all the time now we don't care about B Parts you could add dollar
signs for this bees too or you could not add dollar signs for B's it doesn't matter because
we're not dragging formulas left and right we're dragging up and down so the only thing that's
going to change is the row number now the second part is the second two this was the second part
that used to go to the current row so it used to change to line three four five so for that reason
I'm gonna keep this open and if I just do this and just drag this formula down let's just expand this
column so we can see the whole thing that's going to produce exactly this so you can see like here I
got from B2 through B3 which is what I had here if you remember then here I got B2 through B4 which
is what I got here and and then so on b2b5 this is here then it's going to keep going until the
current line so using this formula now we can get I'm going to delete all of this our running total
so that's one approach to get running total so I'm gonna call this sum now another way to do
this is to basically say let's just grab for this first cell and take this number whatever this is
and then for the next line let's make it the previous total I have from the previous line Plus
the total I got from this line like this and if I'd enter that's going to give me that
8 000 number and if I drag this down make this bigger a little bit you'll see I get the same
results and basically what happens in the next line it keeps going to the previous total of the
previous line plus the total from this line and then as we drag the formula we get the same sort
of results so now we have two different approaches to calculate our running total now one thing
I'm going to change here in this beginning here instead of just having this B2 now notice in all
the other formulas there was always something plus B3 because we had like the total from the previous
line plus the current row now for the first line we don't have total from the previous line so as
a previous line total I'm just gonna put a zero here so I'm going to do 0 plus this number I'm
going to show you why I'm doing the zero this is for me to explain you how we're going to convert
this to an array logic but for now let's just keep it like this so now I basically got two
ways that I'm calculating my running total now I want to do these same things using array formulas
now let's start with this first one so this first one I can convert to running total using
a function called map now I've done more detailed video about map function and more detailed video
about calculating this way of running total in my Advanced array video you can watch that if you
want to get more in-depth understanding what I'm going to be doing here but what I'll do for this
one I'll just copy this formula and to convert this to an array let's actually add a column
here so it's right next to this I'm going to use map function like this not amp but map and what
I'm going to do I'm gonna just take this array of numbers that I'm going to use for this calculation
comma and then we're going to need what's called Lambda function so as a Lambda function well we'll
enter Lambda function and this is going to ask for a parameter so for a parameter I'm just going to
call this this row like that and I'm going to do a comma this could have been called anything but I'm
going to call it this row because that's logically going to make sense so after comma I'm going to
paste the sum function which is the function that I've made previously here in the first line and
then I'm going to just close parentheses for that Lambda function and again close parenthesis for
map function like so and at this point I'm going to change again this part this B2 I'm
just gonna take this copy that this row variable and replace this B2 with that this row variable
from that Lambda function I'm going to hit enter and you'll see that calculates our running total
as array function using this sum method again all of this is going to work exactly the same
way in Google Sheets so just to show this to you if I just copy the same exact formula I go to my
sheets environment here simply just paste that here same map function I'll get exactly the same
result so I got this then this plus this and so on so that's our array calculation to get those
results I'm going to go back to excel so you can see this worked again if you want to understand
more in depth what's happening here I've done Advanced array calculations videos recently you
may want to watch that to see how this actually works but for now I'm just going to keep it at
that so we'll call it running total one so now I'm gonna get to this way of calculating running
total so I'm going to hide these couple of columns here so they don't get on the way and let's now
see how we can take this method of calculating running total and convert this to an array and
to do this we can use scan function now there are two functions they kind of work similarly
one is called scan the other one is reduce again I've done a video about reduce more in-depth
explaining how this function actually operates how it works you may want to watch that if you
want to understand reduce really really well but the reason you would use one of these functions
is when you want to do something with calculation done in the previous step now notice how here what
I used to do I used to take the total from the previous step I calculated and then I add this
number on top of that and then I go to the next line I take total from the previous line again
and then add something to that line so basically we need to take the result that we calculated in
our previous step and build on top of that and this is where you start using these functions
scan and reduce when you want to do array function that does something like this takes the previous
step and works on top of that step so let's do scan function to see how we're going to build
this using scan function so I'm going to go ahead and do equal scan open parentheses and here we're
going to have something that's called initial value so that initial value is the starting value
of the value we're building on and if you remember let me just add an apostrophe here for a second
here I said initially we didn't have anything to add to so I did zero to begin with right so that
was my initial value so I take that initial 0 and then I add whatever this current line
total is the six thousand dollars and that's exactly what's gonna happen here so my initial
value is going to be right here zero for the scan function and I'm going to do a
comma and then after that we're going to have the array so we'll take this range of numbers
and then I'm going to do a comma and we're going to use a Lambda function so see it says function
which is going to be that Lambda function again so here for this Lambda function we're going to
give two parameters one is going to be that calculated total in the current line and the
second one is going to be the current line value so basically I'm just going to type some variable
names here it doesn't matter what you call them just make sure you don't call them with names
that are already available in your spreadsheet so don't call it sum because you already have a
sum function or don't call it A2 because you have a cell that's called A2 right so just some other
names no spaces no special characters should do so what I'll do usually people like to call
this like accumulator or something like that it doesn't matter I'm just going to call it total
amount comma and then as a second parameter here I'm going to call it current value
again could be called anything I'm going to do comma and our calculation is going to be if I just
do this for a second our calculation is if you look here what I kept doing I kept adding the
total from the previous line plus what we have in our current line right here I'm gonna go hit
Escape go back to this so the total from our previous line is going to be this total amount
so I'm going to take that total amount I'm going to copy and paste it here you can also type now
because this line is pretty long it goes to the next line but it's really just one line
here and then I'm gonna add whatever that current line total is so I'm going to take
that current value here so that's it that was my formula I take the previous amount
plus whatever the total is from this current line so then I'm going to close parentheses
that's going to close this Lambda function I'm going to close again to close the scan
function so at this point if I just hit enter see I got my totals some of them are not
formatted well but other than that it works just fine you can see how I get my total numbers it
works so I got this and then this which is these two combined together and then this you can see
that this works just fine similar to these results I get the same exact results only now we got
an array formula so at this point if we copied this and we go back and paste it in our Google
Sheets so you can see that it works the same way you'll see I get the same results I get my totals
so just to get back to the scan function one more time so again if you remember I gave it
initial value 0 it doesn't have to start from zero you could have started let's say initially
from thirty thousand and then you can add on top of that right and that would have worked just fine
too now in our case our initial value is actually a zero so that's what we're going to start with so
initially it's zero and then we take this array and every time this total amount is going to be
whatever the total is at that step so in our first step the total amount is just going to
be zero because we haven't done anything yet and what we do we take the current value which is the
current line number from here in this array and we take that 0 first and add that current value which
is that six thousand dollars and then we displayed at six thousand dollars now then in our next step
now we have six thousand dollars so that total amount now will be six thousand dollars in our
next step so at that point it's gonna be six thousand plus whatever the current value is in
that line which is two thousand so that's eight thousand and then it's gonna keep going like this
over and over like I said so if you want something that works with your value from the previous step
this is where you start using the scan function so that you can refer to your calculated value
from the previous step over and over and build on that value now reduce function works very similar
to this so I could just copy this and go over here someplace and paste this and instead of scan I can
simply just use reduce I'm going to keep the rest of the formula exactly the same exactly like scan
function and hit enter and you'll see that what I get here is not all of these lines I only get the
last amount so basically if you want to be able to see all the steps step by step what happens
in each step you can use scan function and with scan function you're going to see what happens
with that calculation with every step if you use reduce reduce you only get the last amount after
all of these calculations the rest of the logic and the formula is actually exactly the same so
reduce and scan are kind of the same thing except that reduce gives you the last amount only with
scan you get every single step and again reduce also works if I copy this and paste it in my
Google sheet you'll see I get exactly the same results so these functions are exactly
the same in Excel in Google Sheets now they work just fine no problem whatsoever
and there you go a couple of different ways to calculate your running total one way to use scan
function we did it here and then the other way was using map function which also worked and
basically you want to think like do I want to use this sum method to get this total or maybe you
want to use this regular method of referring to the previous calculation to get the total
and that should cover pretty much everything you need to know about calculating running total
if you like my content make sure to like and comment on videos let me know
what other videos you want to see thanks for watching and I'll see you next time