Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:00
in this. Listen, I will show you the basics off mathematical calculation in Excel and let's not beat around the bush anymore. Let's go directly to the well prepared
00:13
example off
00:15
data that needs to be calculated on. So
00:19
we have ah
00:20
Daly or buy shift the
00:24
the sales numbers from a small for interest. Or
00:29
and here we have
00:32
in this table, we have items that were sold.
00:36
We have price of each item we have cost of each item.
00:41
We have the quantity sold and what we want to calculate is revenue.
00:46
We want to calculate margin in
00:50
in money, not in percent
00:53
to see how
00:55
how much money did we make on each item.
00:59
And then we want to also calculate in percents what is the
01:06
margin slash revenue. So how much percent did B make on each of these sales?
01:11
And then we also want to make a total calculation off this
01:19
day. So we want to see how many
01:23
items Harris cell did we sell.
01:26
What is the total revenue? What is the total margin?
01:32
I'll be want to see what is the total percentage margins, less revenue.
01:38
So first quantity sold,
01:42
and we do the simple thing. We select the cells and we just take a note to some.
01:48
And what the XL doesn't. This case is automatically puts some off
01:55
selected cells,
01:57
and some is a function. It is a simple mathematical function of calculates the summer off the range of cells
02:02
that our argument off this function
02:06
so every function after the name of the function, you have the open brackets and then those whatever is necessary for this function to operate it.
02:16
In this case, the function some has just one argument, which is range
02:22
and range is,
02:23
Ah, something in excel that defines a group of cells which can be in one row, one cone or group off Rose and combs. So, for example, here we can type
02:38
some
02:42
and then say, Let's calculate. Okay, this makes no sense in this, but just for it is an example. Let's calculate all of this. So let's put all these together as the sum
02:53
and we get number of 4053 which absolutely is useless for the purpose of running business. But I just giving your example that some can also work on a group of cells which are located in more than one row or call.
03:07
So, for example, if we would
03:08
go here and then select this and say OK, this is wrong, We want this instead. So me, too. Me too.
03:17
It will give us the number 74.5. So this is how you edit sells. You just click here in the formula bar
03:24
and select what you want.
03:28
Toe change. You can even delete it by pressing backspace or delete and then add whatever you want it. So, for example, this
03:39
and we got this.
03:43
So
03:44
this is
03:45
how the some works.
03:46
Now we want to calculate the revenue and revenue
03:51
is
03:53
number of units multiplied by
03:58
price. It's very simple,
04:01
and
04:02
we percenter
04:04
and we got it. So it's 12 times 35. It's
04:09
420
04:11
now. Margin. It's like different. So it's again number of units.
04:15
But then I will be multiplied with
04:19
price minus cost.
04:23
Now it's very simple and just give you the idea so you cannot just present
04:29
press type,
04:32
um, quantity, times price minus cost,
04:38
because that will actually called multiply price by quantity and then subtract from it. Just the cost
04:46
you would get this so
04:47
convention is in excel is the same. Like when you're writing the math on your paper,
04:54
you put the brackets or parenthesis here
04:58
and you get the margin off.
05:00
What was made by selling chairs is $90
05:05
or 90
05:08
Um,
05:10
and now we want to do that for all of these cells.
05:15
So
05:17
we just click on this cell.
05:20
We find this small,
05:24
dark square,
05:25
remove or pointer till it changes shape to Black Cross,
05:30
and then we drag and drop
05:34
till the end off. Last row in Rich. We want this to be copied, dysfunction this formula,
05:43
and we get it here.
05:45
So revenue here,
05:46
two beds at the price of 9 99
05:50
is 1998.
05:56
We do the same
05:58
with margin,
06:00
and we get this
06:02
and now and this is very nice. Let's go to the home tab.
06:08
You also have also some here
06:11
because auto, some or some function, is used so frequently
06:16
that it has. People who made excel decided to just put it on the home tab because it's very useful toe have it here
06:25
because this is where you do most of your basic formula entering which doesn't require functions or if you're a little bit more advanced, you will know how to enter functions
06:35
anyway. So you won't need help of formulas
06:39
The tab in the ribbon. So we click here
06:44
and we are against, like, these cells and click here
06:48
now margins flourish. Revenue. The formula is very simple.
06:53
It was simply
06:55
click on this and the wide it with this
07:00
and the result, we get 0.214
07:02
Do wait, Take something. So, um,
07:06
it means that this is 21.42
07:11
43%
07:13
in order
07:15
to make this look like what people need to look, which is to make it
07:20
ah percentage.
07:24
You can click here
07:26
and you can just click and find the
07:31
former thing of that cell so the contents of the cell stays the same.
07:35
But how is is it viewed? The represented is changed by the type of the cell. You're the way to former that sells a click on percentage here
07:46
and we get
07:49
margin,
07:50
which is
07:53
margin divided by revenue. And we now see that on this sales we made 21.43%
08:01
and now we just in the same way like the others were. Just copy this formula in a way that it represents for every item, same calculation.
08:13
Now what we want to do here is to get the average margin slash revenue for the entire sales of the day and we click or no to some
08:22
we suddenly get 85.11% which is not correct. This is not how the margin is calculated.
08:28
So what we have to do here
08:31
is actually too
08:33
make the same calculation like we did
08:35
on the top.
08:37
And here we have a total margin.
08:41
So how much money did we make
08:43
and the total revenue? So how much did resell
08:50
and we get to the average margin off 14.74%?
08:54
And this is how you do them
08:58
basic math calculations and Matt formulas.
09:01
Now, you can do quite a lot more with this
09:05
And for example,
09:07
uh, here we can have
09:13
this.
09:15
Let's say it's a number.
09:18
And here we want also to be a number. Why?
09:22
Because sometimes the price can be for whatever reason, 34.99. You know how the stores put it like this.
09:33
So then it's um
09:35
it's a number. And if everything is rounded according toe standard rounding
09:41
a convention to two
09:45
decimal places
09:46
and this is how we get to the total calculation. So
09:52
this is how you make your basic
09:54
calculation, which is a typical for a small business
10:00
very easily in excel. So what is where is the power of this?
10:05
We can move these total sells somewhere here.
10:07
And then we can add this as long as the sales goes on. So we can we can add here,
10:15
for example, a comb.
10:18
And you see, I added the calm here, but all the calculations are the same. So let's do undo
10:24
and just look att the formulas. So let's show formulas instead of numbers
10:30
and see what happens if I insert a column here
10:35
thes change to reflect the fact that the price is no longer in column B but in Kansi so we can put here date.
10:48
Okay, So if we want to enter data now here,
10:52
I mean, of course, switch off the shore formulas
10:56
and then we go to home and I have already selected that this is a date type of cell,
11:03
and I just say, OK, This was a sale made on. Remember, 1st 19
11:11
and it gets like this because
11:16
this is the type of former we chose.
11:18
Now we can change the way this by clicking on the right click right click on cell. We pick up the format cells
11:26
and then we decide which type of fun
11:31
display off date we have. So it is going to be 14 March too well,
11:37
or is it going to be a short version
11:41
like this?
11:43
And then we can spread this
11:46
and this is it.
11:50
And then if we had to enter more data,
11:52
we can just,
11:54
for example, inserted these here.
12:00
Calculation stays the same,
12:03
but now we can
12:05
move on, enter more data, and then we have to just extend the some range
12:11
Toby
12:13
from half to two f 13.
12:18
And if you put it here
12:20
like this,
12:22
it will not change this value because we still haven't entered here. So if again, have a chair
12:28
and then the prices slightly different, because
12:33
it's on a discount on Monday. So it's Ah, 29 99
12:41
and the cost remains 27.5
12:46
between. Managed to sell
12:48
56 of them
12:52
and then we're just going to extend this all the way through here.
12:58
We get here the error, and we're going to talk about terrors later. But you see, this is division by zero. Why were you trying to calculate them?
13:07
Percent of margins, Lucia. Revenue
13:09
and revenue here is zero. Because we have no data entered, it doesn't matter at this point.
13:16
What we got here is
13:18
Ah, the calculation in this case.
13:22
So you see, here we have a smaller margins, fresh revenue.
13:26
But we have a bigger margin because we sold more chairs
13:31
and you see that this has copied
13:33
like it was in a different date. So if he wanted the same date, we click with right mouse button on these,
13:43
uh, this, uh, black square
13:46
and then we drag and drop here
13:50
and here we say we want toe copy sells. We don't want to feel Siri's,
13:54
and we get this correct.
13:58
So you have to take care some of these things. They're not automatically done the way you want them to.
14:05
But this is how the mathematical calculation is done.
14:09
Basic man met. The calculation is done in Excel

Up Next

Intro to Excel

This basic Excel training course will give you knowledge of Excel and the ability to use this software to do elementary calculations, file manipulation, data manipulation, to create charts, pivot tables and templates.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor