6 hours 3 minutes
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
data that needs to be calculated on. So
we have ah
Daly or buy shift the
the sales numbers from a small for interest. Or
and here we have
in this table, we have items that were sold.
We have price of each item we have cost of each item.
We have the quantity sold and what we want to calculate is revenue.
We want to calculate margin in
in money, not in percent
to see how
how much money did we make on each item.
And then we want to also calculate in percents what is the
margin slash revenue. So how much percent did B make on each of these sales?
And then we also want to make a total calculation off this
day. So we want to see how many
items Harris cell did we sell.
What is the total revenue? What is the total margin?
I'll be want to see what is the total percentage margins, less revenue.
So first quantity sold,
and we do the simple thing. We select the cells and we just take a note to some.
And what the XL doesn't. This case is automatically puts some off
and some is a function. It is a simple mathematical function of calculates the summer off the range of cells
that our argument off this function
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.
In this case, the function some has just one argument, which is range
and range is,
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
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
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.
So, for example, if we would
go here and then select this and say OK, this is wrong, We want this instead. So me, too. Me too.
It will give us the number 74.5. So this is how you edit sells. You just click here in the formula bar
and select what you want.
Toe change. You can even delete it by pressing backspace or delete and then add whatever you want it. So, for example, this
and we got this.
how the some works.
Now we want to calculate the revenue and revenue
number of units multiplied by
price. It's very simple,
and we got it. So it's 12 times 35. It's
now. Margin. It's like different. So it's again number of units.
But then I will be multiplied with
price minus cost.
Now it's very simple and just give you the idea so you cannot just present
um, quantity, times price minus cost,
because that will actually called multiply price by quantity and then subtract from it. Just the cost
you would get this so
convention is in excel is the same. Like when you're writing the math on your paper,
you put the brackets or parenthesis here
and you get the margin off.
What was made by selling chairs is $90
and now we want to do that for all of these cells.
we just click on this cell.
We find this small,
remove or pointer till it changes shape to Black Cross,
and then we drag and drop
till the end off. Last row in Rich. We want this to be copied, dysfunction this formula,
and we get it here.
So revenue here,
two beds at the price of 9 99
We do the same
and we get this
and now and this is very nice. Let's go to the home tab.
You also have also some here
because auto, some or some function, is used so frequently
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
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
anyway. So you won't need help of formulas
The tab in the ribbon. So we click here
and we are against, like, these cells and click here
now margins flourish. Revenue. The formula is very simple.
It was simply
click on this and the wide it with this
and the result, we get 0.214
Do wait, Take something. So, um,
it means that this is 21.42
to make this look like what people need to look, which is to make it
You can click here
and you can just click and find the
former thing of that cell so the contents of the cell stays the same.
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
and we get
margin divided by revenue. And we now see that on this sales we made 21.43%
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.
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
we suddenly get 85.11% which is not correct. This is not how the margin is calculated.
So what we have to do here
is actually too
make the same calculation like we did
on the top.
And here we have a total margin.
So how much money did we make
and the total revenue? So how much did resell
and we get to the average margin off 14.74%?
And this is how you do them
basic math calculations and Matt formulas.
Now, you can do quite a lot more with this
And for example,
uh, here we can have
Let's say it's a number.
And here we want also to be a number. Why?
Because sometimes the price can be for whatever reason, 34.99. You know how the stores put it like this.
So then it's um
it's a number. And if everything is rounded according toe standard rounding
a convention to two
and this is how we get to the total calculation. So
this is how you make your basic
calculation, which is a typical for a small business
very easily in excel. So what is where is the power of this?
We can move these total sells somewhere here.
And then we can add this as long as the sales goes on. So we can we can add here,
for example, a comb.
And you see, I added the calm here, but all the calculations are the same. So let's do undo
and just look att the formulas. So let's show formulas instead of numbers
and see what happens if I insert a column here
thes change to reflect the fact that the price is no longer in column B but in Kansi so we can put here date.
Okay, So if we want to enter data now here,
I mean, of course, switch off the shore formulas
and then we go to home and I have already selected that this is a date type of cell,
and I just say, OK, This was a sale made on. Remember, 1st 19
and it gets like this because
this is the type of former we chose.
Now we can change the way this by clicking on the right click right click on cell. We pick up the format cells
and then we decide which type of fun
display off date we have. So it is going to be 14 March too well,
or is it going to be a short version
And then we can spread this
and this is it.
And then if we had to enter more data,
we can just,
for example, inserted these here.
Calculation stays the same,
but now we can
move on, enter more data, and then we have to just extend the some range
from half to two f 13.
And if you put it here
it will not change this value because we still haven't entered here. So if again, have a chair
and then the prices slightly different, because
it's on a discount on Monday. So it's Ah, 29 99
and the cost remains 27.5
between. Managed to sell
56 of them
and then we're just going to extend this all the way through here.
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?
Percent of margins, Lucia. Revenue
and revenue here is zero. Because we have no data entered, it doesn't matter at this point.
What we got here is
Ah, the calculation in this case.
So you see, here we have a smaller margins, fresh revenue.
But we have a bigger margin because we sold more chairs
and you see that this has copied
like it was in a different date. So if he wanted the same date, we click with right mouse button on these,
uh, this, uh, black square
and then we drag and drop here
and here we say we want toe copy sells. We don't want to feel Siri's,
and we get this correct.
So you have to take care some of these things. They're not automatically done the way you want them to.
But this is how the mathematical calculation is done.
Basic man met. The calculation is done in Excel
LPI Linux Essentials
LPI Linux Essentials practice exam helps to prepare for the LPI Linux Essentials 010-160 certification ...
Being an intermediate level user of Excel can help save a user hours of work ...
9 CEU/CPE Hours Available
Certificate of Completion Offered