Introduction to IT and Cybersecurity

### 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

Milan Cetic

Instructor