Transcripts
1. Introduction: Have you ever ignore
something that you use on a daily basis and
took it for granted? Have you ever felt that
commonplace and ever present pink don't require
further introspection. I did so a while back
while in college. I didn't make an effort to learn the nuances of Microsoft Excel. One of the most versatile and useful softwares in the world by focus was on learning more advanced softwares
for working with data. Sql are Python's data, etc. As a result, by knowledge of
Excel was woefully limited. As soon as I entered
in this play, I realized my
limitations in Excel. Excel is widely used
in industry and being adept at it is a must. Realizing my mistake, I said about learning Excel in depth. I don't want you to make the
same mistakes as he does. I created this course, which is meant to provide you with a thorough knowledge
of Excel applications. Hi, my name is
Ricky light heavy, and I am currently
a PhD student in Business Administration at
the University of London, Eye Research,
entrepreneurial finance, and use Excel on a daily
basis for some applications. Despite knowing more
advanced software such as R and Python, Excel, I feel is comparable to many softwares and there lies its unique value
in this course, I'll teach you applications and users of Excel from writing conditional statements
to conducting statistical analysis to
conducting water for dialysis, VLookups, data visualization, pivot tables and other
applications of Excel, which a lot of people
don't know about due to the general notion that Excel is just meant for B6
spreadsheet applications. So I request you to give me your full attention and take this course seriously
very seamlessly. Enjoy learning.
2. Excel Walkthrough: Tutorial entity, we're gonna do a quick walk-through
of excellent, I'm gonna explain the ribbon
and what different tabs and the ribbon mean and
what they can do. And I'm going to explain some other basic
things about Excel. So the first thing
we're gonna do is we're going to highlight
the workspace. The workspace is rather huge. It's comprises of
different cells. And the workspace,
as you can see, it's highlighted right now. I'm going to color
this in yellow for your benefit so that you get an idea about
what the workspaces. So the workspace is contained in the sheet. Please
follow my cursor. It has a round green
circle around it. And the circle is right
now pointing at the sheet. So a workspace is contained
in the worksheet. And here in the ribbon on top, please follow my cursor. I'm running my cursor over the different ribbon options
you have the home option, it'll hopefully appears
to have Cartier copy. You have the font type, you have the font size,
bold, italics, underline. You have fill color, you have font color. You have different formatting
options, alignment options. Then you have merging options. You can increase or
decrease indent, you can do conditional
formatting. You can format as a table, you can sort data, you can analyze data,
you can filter data. That is what the home
option is all about. The home tab is all about. Then the Insert tab has
some of the options. You're going to
insert a pivot table. You can insert a table, you can insert pictures, shapes, icons, you
can insert blot. So you have recommended charts
over here, for instance, you can insert different types of jobs right now there are no jobs recommended
because I don't have to data in the Excel sheet. You have bar plots, etc. You have different options
for different plots. And if you take a look at
the shapes you can enter, I'm going to enter
a box over here, and you can use that, do that
using the Insert option. So that is the box. And you can obviously you
can change the fill color. You can do a lot of variety of different
things with that. You can also enter us
straight line or an arrow. I'm going to enter an arrow. So I'm entering an arrow over
here down below the box, so you can do that tooth, then you have the draw option. In the ribbon in drop shouldn't. You can draw something, you can use a pen
to draw something. So I'm drawing a
squiggle over here. So that is what you can
draw using the draw option. It's useful if you're trying
to annotate something. You're the page layout option. In the page layout option, you can specify margins, orientation of the page, size of the page, etc. Any of the formulas option
in this Formulas tab, you have all different
types of functions. All different types
of mathematical functions have AutoSum function, sum, average, count numbers, max, min, et cetera. You will have recently used functions that
you've used before. Then. You have logical functions
are logical tests. If, if error, et
cetera, and et cetera. Then you have the text
options to work with text. Then you have date and time, then you have lookup reference, malfunctions, math and
trigonometric functions, which include functions such as, let's say a cos sine Theta, cos x, etcetera, etcetera. Then you're the data option. And in the deed option you
can get data to query. Then you have, again, do stocks, currency data is embodied off and you can work with
stocks and currency data. You can sort data in ascending
and descending order. You can filter data, you can then do whatever
analysis, et cetera. And finally, the other
important part of the ribbon and other
important tab in the ribbon is a tell me dad, and that's for help. So that is what the entire
ribbon is all about. Now, we're going to take a
look at the formula bar. If you notice the top bar
over which I'm hovering my cursor with the
green ring around it. This is known as the formula bar in this value type
in formula, right? This is where you
type in formula. Now, I'm gonna go back
to the draw option and uncheck the pen because
the pen was checked. And then I'm gonna go back to the home oxygen or
rather Home tab. And I'm going to
select the cell. Now let's say we want to enter
a formula into this cell. Now, we'll enter a basic formula for demonstration purposes. We'll enter a formula which calculates the summer
four plus five. So what you have to do
in this formula bar, and as you can see, the
formula bar over yet, it's at the top of the workspace and my cursor
is hovering over it. You can see the space in
the formula bar within the confines of my green cursor or the green ring
around my cursor. So you want to find out
the sum of four plus five. You write it in the formula bar, you click enter,
and as you can see, the result is displayed here
in the formula bar is quite important because most of
the things we do in Excel, it brutalizes the formula bar. The formula bar, as you can see, has the equals to four plus
five formula highlighted in gray inside the green
ring around my cursor. That's all for the walkthrough.
Thank you so much.
3. Cell Referencing, Work Sheets, and Adding or Deleting worksheets: Etc, some technical stuff or rather let me
put it this way, some other Excel jargon. So the spreadsheet that you see on your screen is known
as the workspace, and it's contained
in sheet number one. Now, I'm highlighting the
areas part of the area in light yellow so that you know what a
workspace looks like. This workspace has
different cells. I'm highlighting one of
the cells in orange. The orange cell over here, the reference number, the
reference numbers E1. So it's contained in
column a and row one. So if you want to
find the seller or if you want to reference the
cell from another cell, then you have to use
the reference A1. E stands for column
and one stands for the row number or
column number grownup. But that is how you
reference a cell. And now you're the
sheet over here. If you look at the bottom-left
corner of your screen, there's a tab called sheet, control-click or right-click it. I'm going to do that. And you have this pop-up menu where
you have different options. You can insert sheets,
you can delete, you can rename sheet. Let's rename the sheet. Let's call it test. She does now renamed as test. Now you're going to add
a sheet. You can add achieved by control-click
or right-click. Right. You can control click on the sheet that is
already present. And then you can select
the Insert Sheet option. And this will
insert a new sheet. Let's rename this sheet. And I'm going to rename it as just being now you
can delete the sheet. Let's delete the initial sheet. Yeah, then you should cheat and deleted by control-click
or right-click. The pop-up menu appears
and you can delete this sheet and click on delete. And you can also copy
or move the sheet. And you can copy the sheet, do the same workbook, the workbook reference to the Excel file that
you're working on. As you can see, a copy of
Desk has been created. So that's all about sheets. Thank you so much.
4. Absolute and Relative Referencing: Hey everyone, welcome to
a new lecture on Excel. And today we're going
to take a look at absolute and relative
referencing. What is that? Let's say we have a value in the orange cell, which is 35. Let's say we have a value in
the blue cell, which is 20. What is the sum of 35.20? Do the sum, we have to click on another cell where we're
going to store the sum. And then we're going to type
equal to on in that cell. Then we're going to select
the orange cell, A1. Then we're going to
use the plus symbol. Then we're going to
select the blue cell B2. We're going to click Enter. The sum is stored in
the yellow cell right? Now suppose we have
more data than 35.20. Suppose we have theta,
which is like this. Ready 31230. So we have data in the cells. I'm coloring in red. The data we, and we want to
find out the sums be A2, B2, B3, A3, right? So we want to find out
what is the sum between A2 and B2. What do we do then? Well, we can write the form ligand or we
can copy the formula and paste it into the cell. I'm coloring in light blue
over here, right? So C2. How do we do that? We
click on the cell C1, or the cell that is
colored in yellow. And then we go to the
bottom-right corner, click on the square root symbol, and we drag the
selected downwards. So as you can see over here, the formula has been
copied into C2 and C3. What is the formula for c2? I'll change the color
of the cell and make it light purple. The formula and
C2 is A2 plus B2, as you can see Excel that
this automatically, right? So in the first cell C1, or this first cell
containing the sums of the columns E and B, you had a formula
that was A1 plus B1. And highlighting it in
gray in the formula bar. Just look at the
formula bar over here. I'm highlighting it
in gray, A1 plus B1. So you have the formula
for the cell C1, C1 equals T1 plus V1. When you copy that formula and pasted it to the
cells underneath it, to C2 and C3. The cell references
automatically change to A2 plus B2 and B3 plus b3. Excel does this automatically. Suppose you want to copy and
paste it somewhere else. Let's paste it over here. The sum is zero, the sum in
the sea blue cell is zero, y is zero because the
formula is not referenced. Absolute manner is referenced in a relative manner because once you copied the cell and
paste the formula in F3, what it did is it took
the sum of D3 and E3. I'm highlighting
the cells in green. So once you've copied
the formula in cell C1, which is A1 plus B1, just follow the formula
in the formula bar it and pasted it in cell F3, the cell referencing change. The how can you fix that? Well, you can fix relative
referencing problems by using absolute referencing.
So how do you do that? Instead of having A1 plus B1? Let's use $1 symbol
before a with states that the sum will be calculated
for cells in column a. And let's use the dollar
symbol before B and column B. So Psalms will be calculated for the cells in column
a and column B. So we are using
absolute referencing. The sums in cells C2
and C3 remain the same. C2 and C3 cell C2 and C3 are
being highlighted purple. So this sums and C2 and
C3 remain the same. And what will be the sum in F3 into the cell that is colored
in yellow at the moment. Let me change the color
here for your convenience. Let's make it light green. Let's make it some other color. Let's make deep blue, right? And I've changed the
font color to red. So the sum in F3
is now 40 again, because we have referenced it and be in an
absolute fashion. So this tells Excel
that wherever you move, you whichever cell you
input the formula into it. And B are absolutely referenced and the sum
will be calculated for a and B and not
some other column. Now, if you want to make certain that the rows are
absolutely reference, do you add dollar
symbols before the rows? Now what does that do? Now let's copy cell C1 and let's paste it
down in cell B9. I'll change the
color to light blue. As you can see here, even though I pasted the
formula into cell E9, which is far away from
where the formula was, since I've absolutely rapid. And to look at the referencing, just look at the formula bar. I'm highlighting the
formula bar content in light gray as I have
absolutely referenced. The cells drop shipped around. This tells Excel that
summer has to be calculated for cells A1 and B1
and not any other son. And if we copy the
formula into C2 and C3, then as you can see, the formula does not change. And cell C1 and C2 have the
values in orange over here. As you can see, this
formula remains. Dollar sign $1 sign $1
sign B, dollar sign one. Write. The formula,
does not change. That is absolute. Referencing by using
$1 sign years, telling Excel that wherever
the formula shifted, the initial formula or the initial cells being
used to calculate a certain number or some other operation
will remain static. And if you do not add the
dollar signs or you do not add dollar signs before a certain row or
a certain column, then that tells Excel that
the formula is dynamics. So when you copy the formula and paste
it into another cell, the formula will
change automatically if you are using
relative referencing. If you're using
absolute referencing, then because of
the dollar signs, Excel interprets the
formula as static. And even if you copy the
formula into another cell, the formula remains the same. That's all. Thank you so much.
5. Insert rows, columns, and cells: Hey everyone, welcome to
a new Excel tutorial. And today we're going to
take a look at how to insert columns, rows, and cells. So that will add this
dataset accompany idea of sales for the company in milling setup the status of the company, whether it is profitable
or unprofitable. Let's say I want to insert
a new column which stores values about the fixed
asset of the company, fixed assets of the company. That can be factories, that can be warehouses
for all space, et cetera. I want to insert this column
beside sales, column. Beside the sales
column, what do I do? I click on the column
besides the sales column, sales in millions column, which is column number C. And then I control-click
or right-click. So I'm going to control-click
or right-click, and I'm going to insert, a new column has been inserted. Similarly, a row
can be inserted. Let's say I want to
insert a row between company number three
and number four. So what do I do? I'm going to click on row four, control-click or
right-click, and I'm going to insert a new row. So row has been inserted
between company 3M, Company for, let's say I
want to insert a cell, I'm going to click
on any random cell over yet less than going to click on cell number B3. So this cell going to
highlight it in orange. And I'm going to right-click
or control-click. Then I'm going to insert. And I can shift the
cell to the right. I can ship the cell down. I can add an entire row, I can add an entire column less, ship the cell down. And I have a new cell over here. And likewise, you
can ship the cell downright inserted and dy rho less than
certain entire row. Insert, insert an entire row, and now you have an entire
row that has been inserted. So that's all for this lecture. Thank you so much.
6. Enter Values into Cells: Hey everyone, welcome to
a new tutorial on Excel. And today we're going to
take a look at how to input values into cells. So the best way to do this is
to type on the cell, right? So I clicked on the cell
and typed on it. Right. Now I'll just color the cell. I've colored in yellow. And just take a look
at how I do this. I type on the cell, I click on the cell
and type on the cell. Or you can use the bar over
here to type in values. And by bar, I'm referring to the area of the screen which
I'm highlighting in green. Just follow my cursor. I'm highlighting
this area in gray. So you can use this
bar to input cells, input values into cells. So you can use the equal to
command and input values. So let's say we want to
input a value in the cell. A4 is the column name, and four is the row name or row number is the column number. Four is the row number. So what I'm doing over here is I'm going to the input bar, typing in equal to and
then entering a value. That is how you input
values into cells.
7. Pasting Cells and Values: Hey everyone, welcome
to a new lecture on Excel and Geneva going to take a look at how
to best cells. So what you can do is
you can right-click or control-click if you're using a map and you can copy yourself
or you can cut ourselves. And yet you can paste it using the right-click or
control-click in Mac. And as you can see,
I've pasted cell A1 into cell A9. Let's
paste it somewhere else. Let's paste it. Over here. I'm coloring
the cell in yellow, or let me use another
color, orange. I'm going to paste
it over here using the Beast special command. So let's first copy the value. And then we're gonna go over
here and Paste Special. And we can paste the value. In the next lecture,
I'm going to talk about absolute and relative
referencing. And that's very important
when you're pasting cells. But let's say if you want
to paste the same value, 35 in all, all the rules underneath G1 or the row m,
highlighting an orange. If you want to paste the
same value in the rules underneath this particular cell, what you can do is
you can click on the click on the
bottom right corner of your cell and drag
it downwards is fine. The square little
oxygen over here, as you can see, there's a
square option over here. There's a square. You click on the square
and pull it down. And you can copy all the values into the cells underneath
this particular cell. And you can do it
horizontally too. So you just click on the cell, go to the bottom right corner, click on the square button, and then drag the selector to the point up to which you
want to copy the value. Thank you so much, That's
all for this lecture.
8. Importing files into Excel: Hey everyone, welcome to a
new tutorial on excellent. Today we're going to
take a look at how we can import data. So to do that, we
have to go to file. If you follow me on the screen. And if you can follow my cursor, go to File, go to Open. Click on Open. Then using open, go to your search window. And from there you
can choose a file. And as you can see, I
have inputted a file, or rather I've opened
a file in Excel. And this is a biolab opened. It has ID, sales and price. I'm highlighting the
entire area in yellow. So that is how you insert
a file in the excellent, How do you see if you
go to Excel or rather you go to the File option
in Excel and click on Save. And that is how you
save files and Excel.
9. Power Query and cleaning up data: Hey everyone, welcome to
your new Excel tutorial. And today we're going
to take a look at importing data and
cleaning up data. So how do you import data? Well, you can go to the File
option over here and import. But another way of importing
data is using Power Query. So we're going to
use power Query to embody that I'm
going to import from file by data sets are stored
in a folder in my computer. So I'm going to
import from there. And I'll go to Import
Excel workbooks. So I have data on
sales figures of some automobile companies and
the automobile companies, our Tesla, Ford, and Chrysler. The data is stored in Sheet1 of that
particular word book. And I have the revenue and
billion for desolate border will have the revenue in billions for Ford
in quarter two, quarter three for
Chrysler in waterfall, and other cost and bullied
for each company and the date the company was established and the
dated became profitable. So let's load this data. Now, what can you
do with this data, as you can see in
the first column, whether labels that
are being displayed, you have the filter
option over here. So let's use the
filter option first. Let's read your sort
in descending order. As you can see, the data has been sorted in
descending order. Now let's filter some data. Let's see how the filter works. We are going to filter data
based on some logical rule. You can filter by color. If the cell color is red, you can filter that data. If it's yellow, it
can filter the data. Let's use the logical rules to filter some data if the
data is greater than 12, in this case, sorry, let me go back. Let's go to the
revenue in billions. So if the revenue
is greater than 12, then you're going
to filter the data. Now, as you can see over here, Tesla had a revenue of billions
and it has been printed. So Tesla had a revenue
of 12 billion. Highlighting this solid
orange, add your field, delete all data that is
greater than 12, right? So Tesla is dropped
from the dataset. Let's use another filter. Let's go back to the
original dataset. Let's remove the highlight. Let's use another filter. If cost is less than 12, then we eat data. So if the cost and billions
is less than adult, then we keep that particular
company in the dataset. Now what we can do is
we can change this if the cost of billions
is greater than 12. Dead, we keep the
companies in the dataset, as you can see, grasslands, the cost of 16000000003rd
has a cost of 23 billion. So they are in the dataset. You can add AND, or, OR operators do this. You can have two rules. Let's add two rules. So if this case, as you can see, cost is greater than dove
and less than 13 dead. You'll keep the or
less than 20 lesson. As you can see in this case, delta has a cost
and billions of 23. So if we apply the
previous rule, greater than 12 or
lesser than 30, then the only data point
that we'll rebate, or greater than 12,
less of a grade D. Let's use 20. The only observation that we'll rebate is priceless
because Chrysler has a cost that is greater than dove and lesser than
20. Let's use our. Now over here, you
have two rules. If cost is greater than two
or if cost is less than 20. As you can see, the
cost of Chrysler, the cost and billions of
Chrysler is greater than total cost and billions of dirt as greater than total cost. And billion of Ford is
12, and Tesla is one. But they are also less than 2012 and
water is less than 20. So all the four observations
were made like this. You can filter data.
What else can you do? As you can see over here, we have not defined
what sort of data these observations are for
is just what is quarter? Quarter is type data. So let's divide. It is time. Quanta is time data. Let's define it as day. Follow my cursor, go to the bar where the options
for defining datasets are. They defined it as tight. Right? Now. You have
revenue in billions. You can define it as currency, or you can divide
a desk. I counted. The dollar sign is appearing
before it costing billions. You can define it as a counting. The data established, you
can define as short date. Deed profitable. We can define us sharp D. Now as you can see over here, we have something in this
dataset that looks B. That is, someone has
made the mistake of associating the company
name with the company id. So the company name
and the company id are present in one column. Because over here
as you can see, the company id is followed by the company name with
a dash in between. So you have the company id, which is 546 for Chrysler, followed by a dash, followed by the name of the
company, chrysler. We want this in
separate columns. How do we do this? Let's first select this. Now, we are going to go to data. And what we're gonna do
is we're gonna go to the option called
text to columns. As you can see over
here, the dataset, the column has been important and we're going
to click on Delimited. So how are these
observations delimited? What sort of characters or dead between different
information? So the first piece of
information is company idea. The second piece of
information is company Dave, and there's a dash between it, so it's delimited with a dash. Now what we're gonna do is
we're going to go click Next. And as you can see, the dash has been used to break the data
into two columns. So Excel is reading the dash and assigning values before
the dashed one column and after the dash
to another column. So let's click on Next. The destination is column G. Column G B1. Be mindful of this, you'd learn what to replace data that is already
there, your dataset. This has to be done
in a new column. Destination is G1 or
the type of data. What is the data format? The data format is text. And let's click on
the Advanced option. You can specify if you want to separate based on
decimal operators or 1,000 operators, et cetera. But we don't have anything of that sort over here.
Click on finish. As you can see over here, the company ID has been
as big a one column. The dashes have become
another column. The name of the companies
have become another column. Let's delete the column
with the dashes, control-click or right-click
and delete this. And color. As you can see over here, which contains the
name of the company, cars, car companies,
most to the left. Now we're going to change
the name of this column. We have ugly car companies. And we're going to deem the column right
to the right of it, which can taste the
company IDs company. So your company id
of car companies, name of card companies, and you have everything
else that you require.
10. Conditional Formating: Hey everyone, welcome to a new lecture today
we're going to take a look at conditional
formatting. How do you format cell colors, et cetera, based
on some condition. What we can do is we can
select the cells over here and go to Conditional Formatting under
the Home ribbon. And we can see that we want to highlight cells based on
certain rules, right? Let's say we want to
highlight cells that have values greater than hundred. As you can see over here, all the string
variables have been highlighted as in pink. The value 105 has been
highlighted in pink. Let's go back. Let's just look at the
prices of product columns. Let's say we want to highlight
all values less than 75. In green. As you can see, the values less than 75 have
been highlighted. You can play around
with this and you can play around with
the formatting styles so that it becomes
easier for you to represent data properly. Thank you so much.
11. Basic Operations in Excel: Hey everyone, welcome to
a new session on Excel. And in this tutorial,
we'll take a look at some basic operations that
can be done in Excel. Now you have this data set
where you have a product ID, which is the idea of
different products. You have the manufacturing
cost of the advertising costs. As you can see, manufacturing
cost and advertising costs, the labels are too big for the cell and they are
not fitting into the cell. So what you can do
over here is you can go to the EBC option
under the Home ribbon. Then you can wrap text, just follow my cursor. And what I'm doing
on the screen. You can wrap text
and this is wrapped the manufacturing cost, labor. And I'll do the same thing
for advertising costs. Let's say we want to
find out the total cost, which is manufacturing
cost-plus advertising costs. We want to find it alpha
product once we have to add, basically we have to add manufacturing cost
for product, 1.2, advertising cost for product while I'm highlighting
the cells into, so you type in equal to, in the formula bar
or into the cell. You click on manufacturing
cost for product one. And then you add advertising
cost per product one to it. You click on both
cells basically. So you click on B2,
first syllable, B2, which is the manufacturing
cost for product one. Then you insert the
addition symbol. Then you click on cell C2, which contains the advertising
cost for product one. And you're the sum of the cost. But if you want to find
out the sum of all, some of costs for
all the products. Just click on the cell. Then go to the
bottom-right corner, click on the plus symbol
and drag down the selector. Since the referencing
over here is relative, the formula will update itself. And as you can see,
by clicking on the formula, the formula, it's automatically updating
itself from cell B2 in the next row or in the
cell underneath the cell, which does the total
cost for product one. The formula is V3 plus C3 in the cellar need that
the formula as before, placebo, the seller need
that is B phi plus d phi, B and C are the costs. So it's column number
B contains the costs. Column number C contains
the advertising costs, the manufacturing costs
and advertising costs. That is how you do summation. Suppose you want to find the difference between
the two products. Then you do the same process. You click on manufacturing cost, you insert the minus symbol, and then you click on advertising toss and you
have the difference. You want to find
the product, you click on manufacturing cost per product when you
insert the product, or rather the multiplication
symbol or the star, click on rising cost per
product to the product, you will find out the
quotient or soap. You divide advertising costs
by manufacturing costs, or let's say manufacturing
cost by advertising costs. Because we have been
doing that before, right? When manufacturing
cost goes first, the manufacturing
cost for product one is in cell B2 is ten. And you insert the
division symbol, which is the backslash. And you click on the advertising
cost for product one, which is in cell c two, and you get the quotient, right? So that is how you do
basic operations in Excel.
12. Formulas in Excel: Hey everyone,
welcome to your new tutorial on Excel entity. We're going to take
a look at how we can insert popular chooses cell, choose any cell where you
would insert the formula. Click on this cell and
click on equal two. Or you can go to the
Formula bar over here and click on equal two. Now let's say you
want to find the sum of all the prices of
different products. So in this dataset we have
product id, sales volume, and the price of different
products, $10, $12, $15 credit, $3, they give dollar. You want
to find the sun. So type in some. Excel has a huge library
of different functions. So you need to know which
function you want to use. But the way to type
in formulas is does. So basically you
type in equal to, then you type in the formula ni, and then you pass the arguments
to the function, right? So you want to find the sum of all the prices of products. So select all the prices and
close the bracket and enter. Click Enter, hit Enter. Then you have the sum
of different products. Now let's say we
want to find the sum of the sales of
different products. What we can do is instead
of finding the sum again, we can just copy and paste
the formula into the cell. I'm highlighting in orange, red. In the cell. I'm highlighting
right now in yellow, you have the sum
of the prices of different products in the cell, which I'm highlighting
in orange, you have the sum of the sales volume or
different products. Now why was this possible? How did the formula updated? It updated because I've
used absolute references, I've used relative referencing. So the first formula was
some of C2 colon C6. So the colon symbolizes from
which cell to which sell. Your selection ranges
from radius, right? If your formula says that your cells
ranged from C2 to C6, the colon signifies that
it ranges from C2 to C6. And when I copied and pasted this formula, and
how did I do that? I, when I clicked on the cell, I went to the top, or rather the bottom
right corner. Click on the plus symbol and drag the selected
to the cell. To the left of the
cell I copied, right? That is how the
formula was copied. The formula automatically
updated itself, as you can see in the cell that I'm highlighting
right now in blue. The formula is now
sum of B2 colon B6. So B2 colon B6
represents the range I am highlighting
in yellow, right? So the first formula represented the range I'm
highlighting in orange. And once I copied the
formula and I can do this by manually copying it
to, I can just click, right-click or
control-click copy those, and paste the
formula in the cell or to the left of
the cell, right? So basically the
formula or the objects, if you've got the
formula and if you're not using absolute referencing, the formula updates
the cell numbers. By itself. Now, if I was using
absolute referencing, so I like dollar
symbols but before C, dollar symbol, before to
like dollar symbol before C. And I'll add $1 symbol
between two, between six. So now I'm using absolute
referencing, right? Let's copy the formula. Right-click or control-click. Click on Copy. Click on the cell where you
want to paste it, which is the cell I'm going to highlight in green right now. And I'll keep changing the highlighting for
your convenience. Click on the cell, Control, click or right-click based on whichever computer use
it, and then paste. Copy, paste. As you can see, now, the formula does not apply to update the cell numbers, right? Because I'm using
absolute referencing. Now, the formula is
being copied as it was in the copy itself. So the formula is
not auto updating, so that is what you do with
formulas. Thank you so much.
13. Sorting and Filtering: Hey everyone, welcome to
a new tutorial on Excel. And today we're going to
take a look at sorting. Let's say we want to sort advertising costs
in ascending order. For that, we'll go to
the Data tab and then we'll click on Sort
smallest to largest. So you can use the sort
function sort option to use a sort option and you
can expand selection or you can continue with
current selection if you expense election, the sorting will be extended
to other columns too. So if you're sorting, Let's
say advertising costs, based on the sorting
of advertising costs, the other columns will be sorted to advertising cost is
sorted in ascending order. Then accordingly,
manufacturing cost and product ID will be sorted along with advertising
costs, right? So this is how it looks, right? So click on Sort and you
can sort by some value, Product ID, manufacturing
cost or advertising costs. Let's say we're going to sort column advertising
costs and we're going to sort smallest to largest. Then click on, Okay. Then as you can see, the advertising cost
column has been cited. So you start with $10 million, then you have $12 million, $15 million for the
$3,000,000.90, $8 million. And the product ID
simultaneously has changed. The sequence of the product
ID has changed accordingly. So based on the sorting
or advertising costs, the sequence of
the product ID and manufacturing costs have
changed accordingly. If you did not do
expense election, how would it look like, right? Let's say we continue with
current selection and we thought we sort the values
largest to smallest. Since we did not expand
the selection of sorting, what happened was,
as you can see, the other two colors,
manufacturing cost and product ID
cost, when sorted, along with advertising costs to the products sequences
did not change. The manufacturing cost
sequence does not change, and the entire dataset
became jumbled, essentially because product
three does not have an advertising cost
of 19 million. What was the advertising
cost of product three? Let's undo what we did. The advertising cost
for product three was $10 billion because we did not expand the
selection while sorting. And I'm going to highlight
the average cost in yellow because we did not
expand the selection while sorting the entire data set begin jumbled
and became a mess. Now, this is how you sought, if you want to sort, let's say in descending
order, select the column. Then click on sought, click on expense election, click on Largest to
Smallest over here in the order function in
the other option rather. And you can solve this
to now let's say you want to sort based on
color of the cell. One cell is colored yellow. What do we do then? We'll flip all the salt
option under the Data tab. We'll go to the Data tab
and select the cells. But click on the Data tab. We'll do Expand selection. And instead of values
under the salt on option, we're going to
select cell color. And we're going to sort
by the colored gold. One of the cells is
colored in gold, right? So as you can see, the first
value is the value of this, the cell that is
colored in gold. And the rest of the values
belong to cells that are, that have no fill or no color. So that is how the
sorting was done. And since I expanded selection, the other two columns are
sorted simultaneously. Now, another thing
we can do under the data ribbon
is filtered data. We can filter data using Excel. Now, what do we mean
by filtering data? Well, when you click
on filter data, you have this downward
facing button symbol that appears in the column. And then we have to click this. Now we can sort the
data in ascending or descending in the filter option, or we can filter data. You can filter by color, we
can filter by cell color. So you can filter by goal. What do we do with God? When we click on filter
with cell color, what it does is it removes all the other cells that do not have this particular cell color. So only the cell that
has a golden cell color remains in the spreadsheet. Now, let's say you want to do some other sort of
filtering, right? And we clicked on novel.
So only the cells that have no fill or no cell color remained
in the selection. Now let's go and filter on the basis of if a value is equal to
a certain value, right? So if the value is equal to 15, Let's say it remains
in the selection. Else it is out of the selection. As you can see over here, only the advertising cost that has a value of
$15 million remains in the selection and the
associated manufacturing cost and product ID remains
in the selection. Or you can change the
option over here. Under the filter option, you can filter values that
do not equal to 15, right? And as you can see over here, values that do not
equal to 15, that is, advertising cost
for product 3425, etcetera, remain
in the selection because these values
are not equal to 15. So you can play
around with this. Let's say we want to
sort values that are between ten and drove. These values will remain
in the selection. The rest will be deleted
from the selection. Let's take a look at the data. As you can see, the values
10.12 rebate in the selection. The rest are deleted
from the selection. So this is what you can do with the Sort and Filter options. Thank you so much. I'll
see you in the next year.
14. Statistical Functions: Hey everyone, welcome
to a new tutorial. Excellent. Today we're
going to take a look at some basic functions,
statistical functions, right? So let's first find out how you can find out the sum of
different values, right? Let's say you want
to find out the sum of the prices of products. Insert equal to into the cell. Then insert some which is
the name of the function, and then select the range which the sum function will use to find out the sum
of the function. The sum is 166. Let's
say we want to find, want to find out the
average of the prices, the products or the mean. Use the average function. Reduce average function, and let's see what
value it returns. It returns a value of 33 point to highlighting it in blue. Let's say we want to
find out the median. Click on median, select the number range.
The median is 15. Let's say we want to
find out the mode. Select the nominator
inch, close the bracket. There is no mode,
as you can see, no number is repeating
itself twice, and that is what a mode is. If a number repeats itself more than once, that
becomes a mode. Let's now find out the
standard deviation, or rather the variance first. Variance of this dataset, or the radius of the price
of products is 1634, 0.2. Let's now find out the
standard deviation. Select the number range. Click Enter, and you have a
standard deviation of 40.4. Like this, you can
find out the maximum number in the number range, which is 105 over here. That is the maximum number. I'm highlighting it in
blue. And as you can see, the maximum number is five. I'm highlighting it in orange. And you can find the
minimum number two. Select the range,
click on enter. The minimum number is ten. So those are some basic
statistical functions in Excel. Thank you so much.
15. Rounding Values: Hey everyone, Today
we're going to take a look at the round function. Now let's say the prices of products are not whole numbers. They say they have decimals, 313, like this, all the
prices have decimals. We can round this up. We have to use the
round function. Click on equal two
in the blank cell, type and round or
typing part of it. And Excel will automatically
true up the function round. We have to select the number
that we want to round. Let's select the
number in cell C2. Unless it, we're
going to round it to one digit after
the decimal point. So insert one. And as you can see,
it has been rounded. Now we can use the
round down function. And what the round
down function does is it rounds down the value of the number to the whole
number below it, right? So the number is C2 and the number digit
is, let's say one. As you can see,
it has round down the value to the number
below it, right? Or let's say we want to
have a whole number. So the number, we change the number of digits
after decimal 0.20. And as you can see, the
route down function as round the value down to ten. The price of the products. For product ID3 was 10.313 and the round down function as rounded it down
to ten because we specify that we do not want any digits
after decimal point. And then we have the
roundup function. And the numbers
selected is 10.313. I'm highlighting it in orange. We're going to round it up
to the next whole number. So the number of decimal points, because rather
than numbers after the digits after
decimal point is zero. And it has rounded the number 211, highlighting it in orange. That is all about
the round function.
16. Autosum: Hey everyone, welcome to
a new Excel tutorial. And today I'm going to
demonstrate the AutoSum function. So we have a dataset over here. We have ID of companies. We have sales volume in
units for the companies. We have price of product for
the companies and we have the status of the company as profitable or
unprofitable, etc. What we want to do is
we want to find out the sum off sales volumes. And we can use the
AutoSum to do this. Just click on the cell below the range which contains
the sales volume. So in this case the cell is B7. Click on AutoSum and
you have the sum. Let's say you don't
want the sum, you want the average
of sales volume. Go to the AutoSum tab
in the formula ribbon. So you have to go to, usually excel is in the
home ribbon or Home tab. You have to go to the
Formula tab, go to AutoSum, click on the downward bracket, and then select whichever
function you need to select. Let's say we select average and automatically
autosome will take the range above the cell as the range which will be used
to calculate the average. So we have the average
Israeli politics less. Confirm this. So average of this ranges
pretty politics, yeah. So everything is being
calculated correctly. So that's what the
AutoSum function does. Thank you so much.
17. Sumif, Averageif: Hey everyone, welcome to
a new Excel tutorial. And today we're going to
take a look at the summary of average if counter functions. So what do these functions do? These functions basically
we'll find the sum or find an average of fine the count only if a certain
criteria is met. So how do we write
these functions? Let's stay. We're going to type
in the function in cell B8 and we want to find out the sum of sales volumes for only
profitable companies. So total sales volumes
for profitable company. So let's type in equal to, let's type in some. If somebody function appears
in the drop-down menu. Let's choose the range where we're going to check if the company is
profitable or loss. So this range doors whether the company is
profitable or not. So the labels are stored
in the status variable and the ranges D2 to D6. And the criteria is prof tables. And we're going to find the sum of sales volumes
for profitable company. Unless click Enter. And this sales volume of
profitable companies is 46. Let's check this product. Company number one
is profitable. I'm highlighting it in orange. Company number three
is profitable. I'm highlighting it in orange. Company number five
is profitable, I'm highlighting it in orange. The total sales for these three companies
are ten plus 12 plus 24. So you have ten, you have 12. And we have 2410 plus
12 plus 24 is 46. So the formula is working right? Let's see. Another version of
this which is average. If the average if function
will only find the average of sales volume if a
certain criteria is met. Let's say in this case, we're going to find out the average sales volumes in units for unprofitable
companies. So the range which stores the status of the
company has the company is profitable or
unprofitable is D12, D6. And we're searching
for on Ross bit able. The sales volumes are stored in the variable are in
the range B2 to B6. Let's click Enter and
the average is 2,828.5. So there are two companies
which are unprofitable. The first one has a
sales volume of 23. The second one has a
sales volume of 34. So the total sales volume is of unprofitable
companies is 37 plus 20, which is 5757/2 is the
mean and the mean is 28.5. 28.5 into two is 57. So that is how you work. The Sabbath average
IF functions, these are conditional
statements, the dig into account a certain variable and will
only show the Psalms, the total subs that
average all the counts of variables which are meeting
a certain condition. If the condition of
being profitable is met, then calculate the sums. If the condition of being unprofitable is met
by the company, then Excel will
automatically calculate the average of these
companies like this. You have some that
average, COUNTIF, etc. This is how these
functions work. Thank you so much.
18. COUNTIF: Hey everyone, welcome
to a new tutorial. Today we're going to take a
look at the COUNTIF function. Suppose you have data where
you have some categories. Now let's look at this
data set that we have. You have company IDs, you have sales in
millions for the company, and you have the
scatter of the company, whether it is profitable
or unprofitable. Now let's say you're trying
to find out how many of these companies are profitable and how many are unprofitable? Well, here, you can
tell with the naked eye that do companies are profitable and three
companies are unprofitable. But in reality, you will have huge data sets and
becomes difficult to find out which category a particular
observation belongs to, a particular company or a
particular product belongs to. This product may be, let's say, iPhone, iPad, etc. And you're trying to find out if the product is
selling, are not selling. It may be a company, which is the case over here. In which case you're
trying to find out whether the company is
profitable or unprofitable. So how do you do that? How do
you calculate the number of unprofitable of profitable
companies that are there? Or how do you calculate
the category, the count of different
observations belonging to a certain category. How do you do that? You
use the COUNTIF function. You can do this using
if statements but becomes complicated count
if it's an easier method. So choose a cell
to that empty cell where you got to store the results of the
COUNTIF function. I'm choosing celibacy aid
that have been equal to, then type in count. If the function appears
in the drop-down menu, then you have to
specify the range. The range contains
all the categories, but different observations
for different companies, for different
products, et cetera. The range over here is the
scatters of the company. And the range is
between C2 to C6. We are ignoring the label of
this particular variable. The label is status. So for this column, label a status, but we're
ignoring that. The range is between C2 to C6. And this range
contains information on whether the company is
profitable or unprofitable. So we have the range null. Then we have to
specify the criteria. So we are, we have to specify which particular
categories count. We want. Harmony, profitable
companies are there, let's say less
specify the criteria. You have to specify the criteria within
quotes. Profitability. I want to find out how many profitable companies out there. As you can see,
the result is two. And I'm highlighting this in
orange with the naked eye. You can tell that there are
two profitable companies. I'm highlighting that in orange. Let's say you want to
find out the number of unprofitable companies
that are there. I'll copy and paste this
formula into the next cell. The problem over here is not referenced this using
absolute reference. So the references
changed, the cells moved. So I'll change the
reference and I'll type in on profitable over here. Let's see how many unprofitable
companies are there? There are three
unprofitable companies, I'm highlighting in blue, and unprofitable companies with the naked eye you can see, and you can see that
there are three. So that's what coded
functions are all about.
19. Dealing with missing values: Hey everyone, welcome to
a new Excel tutorial. In this tutorial,
I'm going to show you what to do with blank data. Let's say we have this
dataset over here, which has the quarterly performance of
different companies. You have four quarters
and the performance of all companies across
different quarters. So we're not tracking the performance of companies
for all the quarters, right? You have revenue in billions of those companies in that
particular quarter, you have costed billions. And you have the
deed, the company was established and the date
it became profitable. Let's introduce some blank cells over here are other
less, remove some data. So we can call
this missing data. So some of the data is missing. For instance, for bottle
world and card company Tesla, the cost in billions is basic for quarter three
and car company toyota, the revenue in
billions is basically, let's highlight the
cells in yellow. So these cells that are highlighted in yellow
have missing values. What do you do? We go to? Filter. First, select the cells. Select the entire range you can. And the after clicking on the Filter button
in the Data ribbon. So what I did was quite simple. I went to the data ribbon and clicked on the
Filter button. And the button with
the button was introduced to all the
different columns. And now we can filter
revenue in billions, right? So I'm gonna filter
revenue in billions first. So which column? The column I'm
highlighting right now, I put a filter out
the sig value. I want to remove the
missing value from our analysis because they
can be really annoying. So I'm going to
uncheck the blank. The blank won't be
shown in the data. The blank will be
removed from the data. And when you remove the blank from the data or the missing value
from the data, the entire observation, the
entire row will be removed. That is how you remove data. So we have removed
one blank data. Now let's go to costed billions, the columns highlighted greater. Let's again go click, click on the Filter button
and less object blacks. And as you can see now, we don't have black data. The blank data was removed. The entire observations
comprising of Chicago Police was removed, and the blacks are
no longer there. Thank you so much.
20. Dealing with strings in Excel: Hey everyone, welcome to a
new tutorial on excellent. Today we're going to take a look at some string operations. So for that, I have introduced a new
column to my dataset. And this column
contains product names or names or iPhone, iPad, iPod, MacBook, Air pods. Let's first take a look
at the Len function. Click on a, click
on an empty cell, insert equal to type
length. Length. Length is basically
the length function. It gives you the length of
string in terms of character. So how many characters
are there in the spring? So select cell D2 over here, which contains the
string iPhone. Click on Enter. As you can see,
iPhone six characters or letters expand the selection. Double-click on the
right bottom corner where you can see
the square symbol, this small square button and double-click on an
iPad has four characters. I bought his book, characters
that book seven, etc. Now we're going
to take a look at the right, left
and big function. While the write
function does is it takes a string and it'll return characters at a certain number of positions from the right. So let's say we want
characters which is, which are two positions
from the right. We all want all the
characters which are two positions from the
end of the string, right? So we want two characters which are from the end of the string, two positions from the
end of the string. So iPhone has six characters, and the last two
characters are in. The right function is returning any two characters from the
end of the string because we have specified that we want to characters from the
end of the string. Let's use the left function. Left function, what it
does is it's similar to write over here in the formula there to specify how
many characters you want from the beginning of the string or from
the left of the string. Let's say we want three
characters from the beginning of the string. And iPad. As for characters and the
first three characters or IP. So that is what the left
function is returning. Let's use the MID function. The next function, we can specify if we want
characters for the middle of the string or
how many characters we want for the middle of
the string and from which position to
which position. Let's say we want to characters from the
middle of the street from position to position three. Okay, let's select
the text first. Position two to position three. So we're inserting
2.3 in the formula. And the result we
are getting is pod. So position two to
position three, the result we're
getting is spotless. Change the position numbers, position two to position two, we are getting PO, right. That is what the right,
left, and MID function does. That is how you deal with string on Excel. Thank you so much.
21. Changing data from string to numeric: Hey everyone, welcome to
a new Excel tutorial. And today we're going to take
a look at what we should do when we encountered the text values amongst
numeric values. Now, over here, I have a column
called cost and beliefs. Now, this dataset comprises
of different quarters and the quarterly income
in terms of revenues of different car companies
and the costs in billions of those different cargo
revenues or currency values, they are in billions. And the cost and billions. Again, our currency values
they are in billions, as you can see over here, I have the cost of billions
of phone companies. Now I'm going to click
on cell above d2, which is highlighted
in light yellow. I'm going to change
the highlight. Let's highlight
this in light blue. Now, instead of having a numeric currency
value over here, a numeric value for
cost of billions. I'm going to insert
a string value. So I'm going to
insert 1.1 will be within quotes when you put something within
codes in Excel, that means you're telling
Excel that this is a streak. So I've defined the
costed billions for Tesla as a string. Now, how do we deal with this? Because you cannot conduct
a dialysis if you have a string amongst numeric values, how
do we deal with this? Well, there's thankfully
a simple way to do this. Go to data, go to
Text, to Columns, click on Text to Columns there
to select the data range, select the cost of
billions column. So you've selected
the data rate, click on delimited, delimited, basically what it does, it checks for
different characters that might be present
between data or data. Click on Delimited, and
over here, click on other. And over here, click on
quotes as the Text qualifier. So the Text
qualifier, over here, it has to be coats
in order for Excel to remove the course of the data and define the
data as a numeric value. So the Text qualifier is
course. Click on next. Destination will be G1. Perfect. Less Lake finished. As you can see over here, the courts have been removed
and the data is now numeric. That's all. Thank you so much.
22. Concatenate, Upper or Lower Functions for string manipulation: Hey everyone, welcome to
your new Excel tutorial. And today we're going
to take a look at couple of more string functions. The first function
we're going to take a look at is upper. We're going to type in
equal to an empty cell. Then we're going to type
up what this function does is it converts all
characters to uppercase. So let's choose the cell B1, which contains the word Potter. And as you can see, border has been converted to uppercase. Now let's look at
the function lower. Again, you have to pass an argument to this function
or rather to this function. The argument we're
going to pass in this case is the cell B2, which contains the
capitalized word bottom. So every character
in potters capital, and we're going to
convert it into law. And as you can see over here, border has been
converted to lowercase. That is what the upper
and lower function do. Now, let's say we are trying
to concatenate two strings. What do we do by concatenate
or combine two strings? So we have Harry over here, which I'm highlighting
in our lives. Another color, blue. And your part over here, which I'm highlighting in
orange, are light orange. I want to combine Harry Potter. Let's go to cell C1. I'm highlighting
the cell in red. Let's say, let's
type in the formula, Let's type in the formula,
in the formula bar. Let's type in equal
to and gone get. And you have that
concatenate function. And the texts we're
going to concatenate are in cell A1, which is Harry. Then enter a comma,
enter to coach. While one open code
and one closed code, and enter the next word Potter. As you can see, you have
the word Harry Potter. Now I've entered To
course over here In this to cause the purpose
of having these two costs. And the formula is
that in these two cos, you can have some other
words, harry James. Let's type in James and less. Click Enter and you have
Harry James Potter. So if you'd not word or if
it not want another word. In this particular function, you do not want to add James
to the Harry Potter thing. Harry Potter would just
click on concatenate. B and A1 contains
the word Harry, B1 contains the word
portal. Click on Enter. And again, as you can see, you have Harry Potter
in cell number. See one. Now, if we remove the
course over here and type in hurry and
type in border. As you can see in
cell number C1, you have the word Harry
Potter without the quotes. And you can insert a space so that there's a
space between Harry Potter. And as you can see right
over here in cell number C1, which is highlighted in red, you have peri bottles. So that's all about the
concatenate function. Thank you so much.
23. If statements and Nested Ifs: Hey everyone, welcome to
a new tutorial on Excel. And today we're going to take
a look at that it's taken. So let's define
our if statement. Let's click on any random cell. I'm going to highlight
the cell in yellow. And in the cell we're
going to insert the if statement like on equal two and then type in if. So, Excel has already
an inbuilt IF function. So you click on the function. As you can see, Excel
requires the logical test, which is whether a value is
greater than, lesser than, equal, or unequal to a
certain value, it has. Then, after that, the dense
statement after that. So if a logical test is true, then what should the value
b and in which cell? And if it's false, then what should the value
b and in which cell, right? So let's say we're trying to see if a particular value is
lesser than 100, right? So let's say we are trying to see if the prices
are less than 100. Let's click on the
first price for product ID3 in the cell C2. If C2 lesser than hundred, That is our logical test. If it is lesser than 100, then the value in the
cell remains the same. So we are clicking on C2. Again, if it's less than 100, than the value in the
cell remains the same. So whatever the value
in the cell was, it remains the same. If it's greater than 100, then the value and
the cell will be 45. Now you can reference
the cell in this else part of
the if statement. Or you can reference a value. You can insert a certain
value or you can insert the value stored in
a certain cells but less, insert a value of 45 over here. As you can see in this cell, the value of the price of products or the price of the product in
this case was ten. And the if statement was if it's less than hundred, right? I'm highlighting the c2 less than 100 part of the if
statement in gray over here. So the value of the cell
remains the same, right? Less, copy and paste
these values so that we have if statement results for all the
different prices, for all the different products. So I'm going to go to the bottom right
corner of the cell, click on the square root symbol. Drag the selection downwards. And as you can see, all
the values remain the same because the prices of products
are all less than 100. Let's say we change the
price of a product to 105. So I've changed the price
of the product for 200, highlighting the cell
in light green, right? As you can see, in the cell that are clicked, and I'm highlighting
the cell in dark green, the value over here
is changed to 45. So if statement checks, if C6 less than 100, well, C6, as you can see from
the cell number over here, has a value of 105
is greater than 100. So we go to the else statement which states that if C6
is greater than 100, then the value will be 45. So the value over here has
been replaced with 45. Now let's say we
want to input or rather check if a certain
cell has a value over 100. And if it's over 100 ft, we're going to replace
that value with the value stored
in cell number G2. I'm highlighting the
cell in blue over here. And the value stored
in cell G2 is 32. Let's change the if statement. If the value in the cell
lesser than hundred, then the value in the
cell remains the same, or C2, else it becomes g2. Let's copy this new statement to all other cells by doing what? By go into the
bottom-right corner and dragging the
selection downloads. As you can see, the
value in the cell whether for which the price
of products was great, greater than hundred
is showing zero. Why is it showing zero? Because I did not use absolute referencing
in the formula. So the value and the cell G2 has to be
absolute referenced. And how do I do that? I add
dollar symbols over here. Because we do not want
this value to change. Because if G2 becomes
G6 and the formula, as was happening before, as you can see in the
formula over here, G2 became G6 because I did not. Use absolute referencing.
If that happens, then the value will obviously be zero because there's
no value stored in G6 cell highlighted in green,
there's no value store. So what I'm gonna do is I'm gonna go back to
the original form, land use, absolute referencing,
dollar and dollar. To now I'm going
to copy and paste the formula to all the cells
below this particular cell. And as you can see in the cell, I'm coloring in yellow. The value has been
replaced with the value 105 has been replaced with 32. So that is how our
if statement works. What if you want to include
two if statements, right? Let's say you want to test for some other
things in the data. And for that you need
an if statement, right? If I'm defining a new column
called a state meant. So let's write the if statement. If the logical test is if
price of product greater than, less than 100, then we're going to include
another if statement. We're going to check if sales
volume is greater than ten. Sales volume is
stored in column B. Then insert another IF command. If sales volume is
lesser than 15, let's say comma, then if
both conditions are met, that is, brighter
products is less than 100 and sales volume
is lesser than 15. Then the cell containing the price of products
for product three, in this case of a
product 4512, etc. When you copy and
paste the formulas to the other cells and
the same formulas transmitted to the other different
cells. Then what happens? Then? We're going to
see that the price of products will
remain the same. So if the price of product is less than hundred and sales
volume is lesser than 15, then price a product
remains the same. Else price of
product will be 50. Now you close this if
statement and coma. If none of these
conditions are true, the conditions that are basically that
price of product is less than 100 and volume
is lesser than 15 units. If none of these
conditions are true, then the function will
return a value of zero. Double-click on the
bottom right corner, which has a square
root symbol over here. As you can see, you're going
to double-click on it. Because by double-clicking
the formula, can we paste it to the cells underneath the cell where
the formula is defined? So as you can see in
the cell highlighted in light blue over here, the value returned by
the f statement is zero. Y is zero, because C6 or 105 is, the price of product is
greater than 100 in this case. And it does not matter what the sales volume is
because the price of the product is
greater than 100. In the cell that
I'm highlighting in light orange over here, the value returned by
the function is 50. Why is that? So? Because the price of products or C5 is 23 to 23 is less than 100. Since it's less than 100, we go to the nested
if statement. The second if statement, right, is B5 or the sales
volume lesser than 15? Well, the sales volume
is not less than 15. So basically, we go to the part of the if statement
which states that if sales volume is
not less than 15, then the function will
return a value of 50. And that is what it did. It returned a value of 50. So that is how you construct
an if statement in Excel. Tacky so much, I'll see
you in the next lecture.
24. Logical Operators in Excel: Hey everyone, welcome to
a new Excel tutorial. And today we're going to dive
deeper into if statements. We're going to take
a look at the use of logical operators in if steepens by logical
operators AND, and, OR operators, right? So you're not just dealing with greater than or equal
to, equal to etcetera. You're also dealing
with an and operator. So what do AND, and OR operators do that? Suppose you have a situation
or scenario over here. You have five
students in a class, and they have taken a
computer science class. They have taken an exam
at the end of the course, and they've taken
the theoretical exam and a practical example, the theoretical exam scores
are highlighted in yellow. The practical exam scores are
highlighted in light blue. The stipulation is, let's say, that students have to pass
both exams to pass the course. So you're trying to find
out if students will pass both exams and if they
pass both exams, that is, if they get greater than 50 in the theoretical exam
and greater than 50 in the practical exam, then they pass the exam, ends, they fail the exam. Scores range 0-150 is
the passing score. So if they get 50 in
the practical exam, and if they get 50 in the theoretical exam,
then they pass. That goes as the fee. So how do you modern
set the scenario? So if they pass the exam in the column that
I'm highlighting in, I'm highlighting in
green or light orange. Then in this column
yellow the store, their final pass or fail grade. So if they get, so let's say student one gets
50/50 in both the peoples, in both the theoretical example and in the practical exam, then he passes the course. So in the column, pass or fail when
both exams required. The one that I'm highlighting
in light orange, cream color in that
particular harlem, the final grade
will be displayed, which will be pass or fail. So how do we model this? We need an if statement. So let's click on a cell, the cell number and
taking all these D2. And let's write a formula
here, V equals two. If click on if. The logical test is that they
have to pass both exams. So score in theoretical
exert greater than 50 and scoring practical
exam greater than 50. So after typing in if, type, and click on that, and now enter the
logical operators. So the logical operators are
scoring theoretical exam greater than 50 or
greater than equal to 50. Rather scoring practical exam
greater than equal to 50. If both schools at
greater than equal to 50, then display, pass. Else display, feel and notice. Since pass and fail or springs, I'm enclosing them
in quotation marks. Click Enter. So the first student has passed. Now we're going to copy
the same formula to the other cells below the first cell that I worked on right now, as you do that, we're going to double-click on the right bottom
corner of the cell, which contains the formula. Or we can click on the right
bottom corner, the squares, small square symbol on
the right bottom corner, and drag it downwards to copy the formula, I'm
going to double-click. As you can see, two
students or pass the course and three students
have failed the course. Student to phase that goes to the three fields that
goes to the Piazza post. Now let's say you have a
teacher who's more lenient. And he says You
have to either pass the theoretical exam or pass the practical exam
to pass this course. How do you model this situation? You'll need if statements again. So let's select cell E2 and E2. We're going to
write the formula. In this case we need it at all. So either pass the
theoretical exam or pass the practical exam. And after, if I click on Add, and then you have to
type in the arguments. The first argument
is that the score in theoretical exam has to be
greater than equal to 50. Oh, what am I doing? Sorry, it's not an n is odd. In this case is odd because
we're trying to find out if the person has passed the theoretical exam
or the practical exam. If he passes the
practical or the theoretical, he gets a password. The previous gates,
you have to pass both. So we're using our
practical exam scores for student one is stored in V2, or rather theoretical
exam score for students, while it's stored, stored in v2. V2 greater than or equal to 50, or C2 greater than equal to 50. So either the practical
exam score or the theoretical exam score has
to be greater than 15, greater than equal to 50,
while the student to pass. If this happens, then you give the grade
pass to the student. Else you'd give the grid
feel to the student. Close the bracket and the
first due date has passed. As you can see, is score in
the theoretical example, 65's for the
practical exam is 56. Fine. Let's copy this formula
by double-clicking on the bottom right
corner of the square, in the bottom right
corner of the cell. And all students to pass
this pose right now. So I'm going to highlight
the entire column in, let's say like me, all students who pass, why
is that the case student who has passed his
practical exam, you got 78 in the
practical exam. Look at the cell and
clicking on yes, 78 in the practical exam and
45 in the theoretical exam. You feel the theoretical exam, but it passed the
practical exam. Student three similarly
pass the theoretical exam, but feel the practical exam, but he pass this course student for failed the theoretical exam, but the practical exam. So we pass the course, student five as the
theoretical exam and pass the practical exam, do so he passes. So everyone passes. That's how you use AND and OR operators in an if statement. Thank you so much.
25. Isnumber, Isblank, and Iserror: Hey everyone, welcome to a
new tutorial on excellent. Today we're going to take a
look at that is blank and SRS deepened and that is planned and it's error statement are very, very useful when
you're trying to find out if there's a blank in one of the cells or if there's an error or number in
one of the cells, right? So let's start with
a blank statement. What you have to do is you
have to select the cell. Where are you going
to store the results of the blank statement? And just, let's name
this column is blank. And then click on the cell. Insert equal to and type is
blank. And insert a value. Suppose you are trying
to see if there are blanks in the price of products. If we don't have
data in the price of products column, right? So click on the
value. So it's blank. C2, C2 is the value of
z to a blank click. Then the answer is false because there is no
blank over here. So let's expand the selection to the other products.
We have done this. All the values are false
because there is no black. Let's say there is a blank. As you can see over here. That is blank value
becomes true. Again, we're going
to insert a value is blank, value becomes false. As you can see in
the blue salvia. Let's take a look at is number. Are all the values under the price of products
column or lumbar. It's quite simple, right? You click on equal two. Click on Islam bar over here from the
selection of functions, and you click on the value. Now it's always true. Yes,
all the values are numbers. Now let's insert a word we hear. So as you can see in the
cell highlighted in orange, since as soon as we entered, so the number function
returns a value of false. So, so it's not a
number and we're going to return back to revert
back to a number. And the value highlighted in
orange becomes true again. And then there's the
error statement. Let's select the prices
value for this one. There is no error in either one of the
price of product values. So as you can see, this
is how the is blank, is number, is error. Functions work. Thank you so much. I'll
see you in another.
26. Data Visualization in Excel: Hey everyone, welcome to
a new Excel tutorial. And today we're
going to take a look at data visualization for that at this particular
dataset over here at the company names for
automobile companies have their revenue in billions of their
cost and billions. And if you're confused
about the symbol before the revenue numbers
is indeed repeats. I have the date established and I have the
deed profitable nerve. Remember, this is a
made-up data set. This is not real data, but anyhow this will suffice
for the demonstration. So let's go to the Insert tab. And over here, under
the insert ribbon, you have these
different options. You have column chart, you have hierarchical jagged,
which we don't need. In this case we have
statistical charts. You have live chat, you have pie chart, and you're scattered jars. Let's do a boxplot first. Just click on the
bar plot option. As you can see, it's
blank right now go to Select Data under
the Chart Design Option. And you have to
select the range. We want to plot revenue
against company names. I've selected the range
revenue and company names. The name of the chart
is revenue in billions. So I've selected that the
y values are the revenues. The revenue values
of the y values, the x values, the company names. Click on Okay, and you have a nice bar chart over here with different companies
and their revenues to it as the most revenue,
Tesla's the least, which is understandable given
the fact that electric cars haven't caught on yet in
most parts of the world. Now, let's say you want to
change how the chart looks. Go to the chart design
option of a unit. My cursor is hovering above this option and click on
any different option, which will give you
a different type of look for the chart. Now let's go to Add Chart
Element and the chart design. We can add access title, we can add access. We can add a chart title. Job title is already there. We can add data labels. We can add data
tables with Legend E. We get to add percentage, et cetera, grid lines,
legend and trend line. Let's add a trend line.
I'll add a linear trendline does not look like a good
fit, the linear trend line. But what can we do
with the trend line? Double-click on the trend line
and you get these options, you can add a
logarithmic trend line. We can add a
polynomial trend line, which looks like a
good fit actually are. What you can do is you can go to this paint bucket option, which is called fill in line, or which looks like
a paint bucket, basically this
option, click on it. You can change the
width of the light. You can change the
dash type, et cetera. You can change the
color of the line to this orange, as you can see. Now I'll do away with the slide. I'll show you another light. Another trend line. This red line is called
the linear forecasts. If you want to forecast
what another company, what sort of revenue
another company will have, you can use this forecast lead. As you can see, the Excel is trying to forecast
into the future. And Australia's tell you what sort of guard cells
another company might have. The forecast lead is
probably aren't any of the trend lines are probably good fits for
this sort of chart. So I'm going to remove this. Now you can switch
row and column. I'm not going to do
this for this chart because by in batches
switching rows and columns makes no sense
because then you'll have revenue in the x-axis. We won't do that. We're going
to change the chart type. We're gonna go to pie
chart, pecan pie. And as you can see,
you have a pie chart over here. The
legend is missing. So go to the different
chart style options. Click on one of them. And the
legend is here right now, then as you can see, Tesla,
which is represented in blue, has 12% stroke person
sales or rather CLF dwell. So I will, what I'll
do is I'll go to another chart style which
displays percentages. Yeah. So Tesla has a total revenue of 12 billion and it constitutes
11% of the total sales. Then Ford has a total
revenue of 23 billion. It constitutes 21%
of the total sales. Then Toyota hazard revenue of 42 billiard constitutes 39
per cent of the total sales. I just expand this chart. And Chrysler has a
revenue of 32 billion. It constitutes 29 per
cent of the total sales. So that's what a pie
chart tells you. It tells you the portion of the entire sales each
company represents. Let's delete this chart
for the time being. Let's go to Insert tab again, unless insert a scatter plot. Right now we have a
blind scatter plot. I'm going to change,
I'm going to select data for
the scatter plot. So the artery and just cost
in millions in revenue. So I'm going to block revenue
against cost and billions. Name of the charters. Give it a new name, type
in equal to the name bar, open God's name of Jack. Revenue versus costs. X values are costing billions. Y values are revenue
in billions. And that's all you need. And you have a scatterplot. Revenue was his cause.
X-axis is cost and billions, and y axis's revenue
in billions. Let's add the axis titles. You can add horizontal
title over here, which will be gloss. You can add. A vertical axis over here, which will be revenue. What we need now
is a trend line. So we're going to
add a trend line. Let's add a linear trendline. The linear trend
line is a good fit. Let's double-click on the
trend line and less bright, different other trend lines. They are not good fist polynomial
seems like a good fit. So let's keep the polynomial
instead of the linear. Let's go to the fill
and line option, which is represented
by the paint bucket. Let's click on it.
Let's increase the breadth of the line, and let's change the dash type. Let's change the color of the
line so that it pops out. Now, what we can do
is we can also play around with the
scatterplot data points. The data points are
represented in blue right now, they are marked in blue. I'm going to increase the
width of the data point. For that, we'll go to the
paint bucket option again, instead of the light
will go to the marker and we are going to increase the width of the lines. And I'm going to
change the color to green so that it pops out. As you can see over here,
the color has been changed, agree that the data points
are right over here. So let's click on this chart. Let's go to Add Chart Elements. Let's go to Data Labels, and let's give the data labels. The data labels, as you can see, the topmost is 42 billion, which is for Toyota. Toyota has been represented the chart represented
in the chart. Then you have 32 billions. Then you have 12
billion basically. So you can play
around with this. And as you can see, you can clearly see what does scatter
plot is telling you. You can play around with this. Now what we can do, what we can do basically, we can go to the
chart design option and we can change
the chart type. Let's do a line chart. And we have a line
chart over here. We have the different
data points. As you can see, 12 billion, 33 billion, 42
billion, 2 billion. And we have a trend line. Let's remove the trend line. And we have a line
chart right over here. We have a line chart. And we can change the way
the line chart looks. We can make it look like this. Revenue versus cost, any of the different costs
you can make it look like this by clicking on
the different options and other Chart Design tab. So that is what you can do. The rest of the jars are not important for this sort of data. You can try a different
scatter plot. The scatter plot. And you can change the
design of the scatter plot. As you can see the
scatterplot husband represented over here. We can go back to more traditional
scatter plot like this. And then click on
the data points. Increase the width. And as you can see, the
data points are now in green boxes so that they pop out from within the
entire chart space. So that is what you can do. The different things that
you can do with charts. I'll, I'll show
another type of chart. Before I end this. I'm going to insert a histogram. So what does a histogram do? Basically, a histogram is essentially a frequency
distribution. So essentially it tells you the frequency of values
in a particular rate. So let's say we have a
rich which is like this, zero to 2020 to 40 and read you cannot be disputed. It has
to be continuous. And for T2, 60. So these are the ranges
in which revenues can fall in any of the
values for revenue, how many of the companies
fall in the range zero to 20. You have one value
over yet, Tesla. Tesla is the revenue
of $12 billion. So it falls in the
range zero to 20. How many of the values fall
in the range 20 to 40? Well, Ford has a revenue
of credit 3 billion, Christ as a revenue of 32. So two of the values
fall in this range. How many of the
values fall under age 40 to 6,042 billion. So one of the values fall
and the spring there, but it keeps the type of data we're because Excel is
interpreting this as currency. Making in general. The table over here basically is what will be represented
in the histogram. But I'm going to
keep this table. Now let's go to insert
and insert the histogram. But before that we have
to select the data. So I'm going to insert a
histogram for the revenue. So the histogram will be
reinserted for the revenue. And let's click on this. Histogram has been inserted. Let me change the chart title. Click on the Chart
Title IV it revenue. Now, what can we do with this? As you can see the graph on the y-axis, you
have the frequency. On the x-axis. You have the radius,
the radius r. I'm going to expand
the chart over here. The radius r 12 to 14. Billions for the first color, when 40 to 60 billion
for the second column, click on the histogram. Now, click on the
histogram, it Double-click. What you can do with this is you can change the color
of the histogram. So I'm going to meet the first range that is
close to $40 billion. Or I'll go to bake the
second rage, greed. So we can change the colors. Now we can add borders. Now let me add a border. Let me add a deep blue
border for the first column. So you got to add a border. You can increase the width. Just follow my cursor will
be the width over here. That is what you can do. Let's see, I do not
like how the histogram looks because it has two
columns or two ranges. Let's say we want to
change those ranges. The first thing
we're going to do is we can double-click or the particular
column in the graph. Or you can write up,
right-click or control-click. I'm right-clicking
it, control clicking. And we can format the plot
area and we can follow that. We can change the
color, et cetera. Let me make the color
green for this one too. Right? So you can read the chart area. Now I've got a format
color of this one. I'm going to make it blue
so that it stands out. And I'm going to make
the other would do two. So this is how you can format the chart area and go
to Control and click, right-click whichever
option works based on your operating system. So let me click on the column. Are we here, are the
frequency column over here. And let V control-click
or right-click. And let me go to
format data series. So what I can do with
this format data series, I can change the bin width, the current bid with
this $28 billion. So well to 40 is $20,000,000,000.40 to $68
billion that we bake it. Now I have a histogram
that looks better. As you can see, most of the
values fall $22-32 billion. I can change the number
of bids to I can make it. Five does not look good. Let's revert back to three. So that is what I can do. Let me change the width to five. Again, it does not look good. It looks discreet. Let
me keep it as debt. So that is what I can do with the format
sees often though, let's say I want to display how many values
fall in which category. So by that, I mean, what is the frequency for
each category or each range? What is the frequency
for predictability? Dollars? What is the frequency
for 22 to $32,000,000,000? What is the frequency for
32 to 42 billion-dollar? Click on the columns. It the plot. Right-click or control-click. And then click on. Add data labels in
the drop-down menu. As you can see, we have
data labels over here. And we can make the
data levels orange, or we can make the
data labels red. And we can increase the
borders of the data labels. Let's make them big. The chart area orange so that
there's a good contrast. We can increase the width of the data labels so that they become bored
provident in the chart. Let me be the color yellow. So as you can see it, the rage, 12 to $22 billion, that will change the
chart color to white. As you can see, the
rage 12 to $22 billion. There's one value in the
range 22 to $32 billion. There's there are two
values in the range, 32 to $42 million. There is one value. Is it correct, As you
can see, it is correct. You can play around
with the bead width, number of bids, etc. You could do all of that. That is what a
histogram is all about.
27. Data Analysis: Summary Statistics: Hey everyone, welcome to
a new lecture on Excel. And today we're going
to take a look at the Data Analysis
ToolPak in Excel. To activate this ToolPak, the first thing we need to do
is we need to go to Tools, and then we need to
go to Excel Add-ins. Click on Excel Add-ins. Make sure that the
Analysis ToolPak and the Solver add-in
options are checked. Check the check boxes,
and then click on. Okay, and this will activate
the Data Analysis ToolPak. Now let's go to data. Let's go to Data Analysis. My cursor is on the data
analysis option over here. Click on data analysis. Over here you can do single
factor ANOVA, two-factor, ANOVA two-factor Without
Replication correlation, covariance, descriptive
statistics, exponential smoothing, F-test Fourier
analysis, histogram, moving average random number generation rank and percentile, regression sampling, et
cetera, z-test, t-test, etc. Most of these techniques are beyond the purview
of this course, beyond the scope of this course. But I'm going to cover descriptive statistics,
correlation, and regression as far as this particular
toolkit is concerned. So let's first work with
descriptive statistics. Let's click on okay. So the input range we want to find the descriptive
statistics for sales and price our
products now bear in mind. You cannot include non
numeric values in this range, so you have to
exclude the labels. If you want to
include the labels, then what you need to do
is you need to select the columns and then check
Labels in First Row. As you can see
over here, there's an option called
Labels in First Row, my cursor is on this
particular option. Click on that. Now click on
Check summary statistics. That is what we are
trying to find out. The output range should be somewhere which is far
away from your data. Let's select the output range. Now that we've done that, we're ready to find out
the summary statistics. Okay, I get it. I'm going to select
the input range again. And the output range I'm
going to select, again. Made a mistake with the
range selection before this, and I'm going to click on, Okay. And now we have the
summary statistics. Now for steels,
the mean is 20.6. I'm going to highlight these
in different colors, right? So highlighted in orange
is the mean standard error is highlighted in blue is
4.3, median is highlighted. Yellow is 23, standard
deviation is 9.70. So instead of typing in formulas and finding all these
things individually, you can just use the
Data Analysis ToolPak, sample variances 95.8. I'm going to highlight
this in purple, skewness and kurtosis. They are, they have
certain values. -1.1 to 0.20 light ranges for any full range
is maximum minus minimum. I'm highlighting this in
debri, minimum is ten. The minimum sales
volume was ten. Allied highlighting
this and see blue. Maximum is 34, highlighting
this in orange. And sum of all the sales is highlighting it in a
shade of blue and cow, despite there are five
sales observations highlighting this
an extreme light green and the price
of products have similar results for the
descriptive statistics, I'm highlighting the
entire thing in yellow. So as you can see, the mean
is 33.2 standard error, 18, standard deviation for
our sample variance once it's three
point, if you recall, we found the same
values while using the commands in a
previous lecture. Good also skew range,
minimum, maximum, etc. You can find all this using
the Data Analysis ToolPak. Thank you so much, That's
all for this lecture.
28. Data Analysis: Correlation: Hey everyone, welcome to
a new lecture on Excel. And today we're going to
take a look at correlation. So let's find the correlation
between sales and products. And what is correlation? Correlation is basically a
metric that tells you if sales and products and
price of products rather, or if two variables vary together or vary in
opposite directions. So if they vary together, then we see that they are
positively correlated. So if one variable increases with increase in
another variable, then we say they are
positively correlated. If one variable decreases with the increase in
another variable, then we say they are
negatively correlated. Correlations course vary
between minus one to one, with minus one being highly perfect,
negative correlation, zero being no correlation, one being highly positive,
perfect correlation. In general, correlation values
between 0.7 and -0.7 or other correlation
values near 0.7 or -0.7 tell us that there is a strong degree
of correlation. So there's some association between two variables over here. The two variables we're going to test our sales and
prayers of products. So when price of
products increases, the sales increase,
or the sales reduced. When price a product decreases, the sales increase
of sales reduce. We look at the correlation
coefficient and find this out. Let's go to the Data tab. Let's go to Data Analysis. Let's go to correlation. Click on Correlation,
click Okay, select the Input Range. Select the columns B
and C. Check Labels in First Row because the
first row contains illegal sales and
price of products. And output range is in
the same worksheet. Let's select output range. Click on Okay. And we have the correlation
matrix over here. As you can see, the
correlation between sales and price of
products is 0.796. So there's high
positive correlation. I'm highlighting
the cell in orange. As you can see over here, you have price of products
and you have sales. And the correlation
between sales and price of products is 0.796. So that is how you can
find out correlation between different
variables in Excel. So that's all for this
lecture. Thank you so much.
29. Data Analysis: Regression: Hey everyone, welcome to a
new tutorial on excellent. Today we're going to take a
quick look at regression. I'm not going to go deep into regression is beyond the
scope of this course. I've taught progression
to other courses. In this course, I'm just
going to tell you how to do regression using Excel and
why regression is important. Regression is important
because regression tells you if there's a relationship between
two variables. So if sales increases, or rather price of products in places, does sales increase? Seems intuitive? If price of products increases, then sales should increase. Or if the agent quizzes, then, does salary increase? Or if salary increases, then does spending increase? If salary increases, does food spending or spending
on food increases? These are the sort of
questions, regression answers. In this case, we are concerned about two variables, right? We're concerned
about two things, sales and price a product. We're trying to find
out if sales increases, when price of
products increases. And for that, we need
to go to the Data tab, go to the data analysis step. We need to go to
the Regression tab, click on OK. And the
input Y range is sales, and y is the variable
that depends on an independent way, right? So the difference between dependent and
independent variable is that when independent
variable varies, it causes some variation. Why are the dependently, but not the other way around? So if, let's say price
of products increases, then sales increases
due to some reason. But if sales increases, that doesn't mean price of products will
increase or decrease. Sales is the dependent
variable because it depends on price, a product, which is the
independent variable. So sales is the
dependent variable, price of products as the
independent variable. When price a product changes, it causes some change in sales. So sales is the
dependent variable and we are selecting
the range of sales. We are selecting the range
of sales over a year. And that is the why ridge and the X variable or the
independent variable, is the price of products. Remember, remember
a graph on a graph, your y-axis and x-axis. With changes in x, there is some change in y, there's a variation in y. So x, basically the variable that changes or
the data that changes, which causes some change in y, or the data that
is dependent on x. So y is the dependent variable, x is the independent
variable. Click on labels. We want the labels to be taken into account sales and
price of product status. And the output rate
will be in the same, very same worksheet. And that is all we need for our basic
regression analysis. We are the regression results. Two things are important
in the regression results. The R-square value,
R-squared value. I'm highlighting it
in yellow over here. Let me choose another color. I've used the yellow, plenty. Green. Yeah. So
the R-square value highlighted in green over here, as you can see, tells us
how good the model is if the model is good at predicting
some change in sales. So we're trying to
see if changes in price has some change on sales. So the R-squared value tells us if the model is good
enough to predict that, to predict the changes in sales. So the R-square value is
0.6 h per value varies 0-1. If the value is near zero, that means the
model is not good. If the value is near one, that means the model is very good. Values are always 0.63, which means the model is decent. And the next piece of
information we are interested in is the p-value. And I'm highlighting
this in orange. The p-value should
be lesser than 0.05. There's a huge amount
of statistics to learn before we completely understand
what P value signifies. But for the time being, just for demonstrating Excel, I'll just say that
p value needs to be lesser than 0.05, right? So I'm highlighting
the condition less than 0.05 in purple. If you look at the
purple cell over here, or I'll change the color to, let's say, light blue. So that it's easier for you to comprehend this and see this. So if the p-value
is less than 0.5, then you see that the prices of products has some
impact on sales. Then we look at the
coefficient which I'm highlighting in debri. Let me use a lighter
color. The coefficient, as you can see over
here, highlighted in green is 0.19 is positive. Coefficient tells us if
within the change in price, since changes in the
positive direction, or if for the change in price, sales changes in the
negative direction. So in this case, the
coefficient is positive. So this shows us that really increase price by
one unit or $1, then the seals changes by
0.19 million units sold. And if this value was negative, it would mean that if you
increase price by $1, then the sales will
change or reduce by, the sales would reduce
by 1.9 million units. So that is what the prices do, or rather the coefficients do. So if price increases, sales increases, in this case, a price increases by one unit, sales increases by
0.19 million units. So that is what regression tells us and that is how
you do regression in Excel. Thank you so much.
30. HLOOKUP: Hey everyone, welcome to
a new lecture on Excel. And today we're going to
take a look at H look-ups. We have done VLookups and today we'll take a
look at H look-ups, which is similar to VLookups. When do you use HLookup swelled by the DBA looks
kind of like this, right? So instead of having columns
with names and labels in it, so you don't have
columns with labels. In case of sort of a dataset. What you have is you
have rows with liberals. So while in the
VLookup scenario, you will, you will using
columns with labels. Over here, you have
rules with labels. So the entire dataset
is a transpose, or rather is the mirror image of what it was while
we used VLookup. And why is that? As you
can see, the first row, first row has ID, the second row has steals, the third row has price data. Now, in the previous example, we had data that
looked like this. Id to the first
column are the IDs. The second column has the sales, and the third column
adds up prices. So data looked like that. And then we used VLookup to find particular values inside
the table using references. And you're using a lookup, dumb lookup number,
whatever lookup value. In this case, since the
dataset looks different, since instead of having
columns with labels, you have rows with levels
here to use HLookup. Now, we're going to click
on a cell over here. The cell is H7 and we're going to write
the HLookup formula. Is that click on HLookup and the lookup value looking
at is two, id number two. So that is the lookup value
in the table we're looking at is the a data table that, which I'm highlighting
using the selector. And the row index
is essentially, we're trying to
find out the seals for sales for ID number to enter. False, we're not fine. Sales for ID number two. Now it has fallen to see if I made a mistake in the formula. So the formula looks like this. I'll do the formula again so that I can
demonstrate properly. So whatever you do is you
have to click on the cell, click equal to type
in H, looked up. I'll do this quickly. Hlookup. You have to select
the lookup value at searching for ID number two, you're searching for the
sales volume of idea. But to, let's say that's
your problems deeper. You're searching for the sales
volume of ID number two. You enter the ID number. You enter the entire table array which stores all the data. And then you enter
the row index number. The row index number for
sales volume is two into two. In Tacoma, then you enter false, you want an exact match. And the sales value for
ID number two is 23, the cn's volume that is. And I'm going to highlight
the cell in orange. So as you can see, 23
has been displayed in cell number h seven, and I'm highlighting
the answer in red. In the cell number h seven, 3d3 has been displayed, which is the sales value, sales volume rather
for ID number two, for product ID number to
the product ID number to the sales volume of 23. So that's all about HLookup. Thank you so much.
31. VLOOKUP: Hey everyone, welcome to
a new lecture on Excel. And today we're going to
take a look at VLookup. What does VLookup do is
basically a search tool. If you have a huge database with a lot of different
values, numbers, product IDs, than price
of product sales, volumes, et cetera, D, down a wide variety
of different things. It's sometimes very
inconvenient to search for a
particular data point, or search for a particular
observation or search for a particular product
in this huge database, it becomes like searching
for a needle in a haystack. So instead of that be a VLookup. And using VLookup, we can
specify which product ID, which identify we're
searching for and against that identifier
of product ID, we can find out
different metrics regarding that
particular product. For instance, over here
in this dataset that I'm going to highlight
in yellow over here, we have product id, sales price of products. Wouldn't it be nice if we
could just use product ID to find out the sales and price of product for
that particular product. And if we do not have to go through the
entire database for that, then it will save
us a lot of time in VLookup does just that. So how do we implement
VLookup over yet? A column which I'm marking in orange is
called VLookup ID. I have to ID the bus for products or product
to end product for. And for that, I'm
going to try to find the seals and the
price of products. And for that, we're gonna
implement the VLookup command. Select an empty cell, click or equal to type and v lookup or feel a part
of it, anything. And once you have
inputted a part of it, the entire function will pop
up, click on the function, then the lookup value we're interested in are
the value against which we're going to try to find out different information. So if you have a
product ID of two, what is the sales and price of products for that
particular product ID? So the information associated with that particular product ID, that is what we are trying
to find out, right? So the lookup value
would be two. Let's select two over
here, which is Select, which is stored in
the cell H two. We have two over here at the table area is
essentially their dy, dw is. So the entire table
containing information, so the entire table in yellow, the column index number
is basically over here. Let's say we're trying to
find out the sales, right? For product ID to the column
index number is exact match. So false. We want an exact match, we want a data point
or observation, or we want information on product id2 and we want an exact match. So
let's run this. As you can see over here. We found out the product ID, but since we
referenced it wrongly, we've referenced the
product ID column. Let's reference
the sales column. And as soon as we referenced the sales
column and what did we do? We change the column
reference Nobody two. Let's start this process again. I messed up the
product reference ID in the previous iteration. So let's look at VLookup. The reference value is
for, let's say the style. We want to find out
information about product ID for it
to be specific, we want to find out the sales
volume of product ID for. As you can see over here, the sales volume is 34 and the price of products
is 35, right? We're going to select the
entire table array over here. Then we're gonna specify
the column index number. And the column index number. We're going to use this to
search for an exact match. And as you can see, for the column index
number to the product ID, has a sales by
product ID four has a sales value of 34 million. The sales volume is 34 billion. For product ID, for
AdWords, we use VLookup. We can just send the
product ID number to Excel. And Excel will search for
the associated sales volume. Returns, the value
of the sales volume. So by changing the
column reference number, we can essentially find out
the price of products true. And the price of products is
stored in column reference, the column index number three. So the columns are, columns start from one, so a is one, B is two, C is three, So product id
is stored in column one. Sales in stores is
stored in column two, and price of products is
told in column three. So let's change the
column index number 23. And now we have the
price of the product. Let's highlight
this in deep blue. And as you can see, the
price of the product over here in the blue is also 105. So that is what VLookup does. You have to feed it
be Lookup or rather, you have to feed it
the lookup value. You have to feed
it the data table, and you have to specify
the column index number. Remember column start from one. And finally, you have to specify whether
you want an exact match. If we want an exact match, then it will find that exact. But I didn't give you
the requested value. That's all for this
particular lecture. Thank you so much.
32. Match: Hey everyone, welcome to
a new Excel tutorial. And today we're going to take a look at the match function. So what does the
match function to? Suppose you have a value, you have a sales value, such as over here, right? You have sales unit,
sales volume in units, your price, and your
ID of the product. Now suppose you have a sales volume value and
you want to see if it is present in the dataset that I'm highlighting in yellow. So if you want to see if this value is presented
the data set, you'll use a match function. What does it match function do? A match function takes a value, searches for it in an array. If there's a match,
it will return the position of the
value in that area. For instance, the areas
over here are the id, the sales volume, and the price. There are three areas. So we'll search for some value in these three
areas and it doesn't match, then the match function will return the position
of the value. So let's see if there's a
match for the value for. I'm highlighting the cell in light blue right after the
match for the value four. And we are going to
input the formula in cell H7 type in equal
to typing match. The value is in H3, which is for insert a comma, and then search for
it in the product ID. Now the product idea, it has a label which is ID. So you can include the label or you can exclude the level. In either case, the positions will be determined by how many values are
there in the array. Let's say we include
the label in the beginning for this
particular iteration of the match function. So we have included the
area which is A1 to A6, and now we want an exact match. So if there's an exact match, the match function will
return the position of four. Let's run this and the position of four has been
returned as five. I'm going to highlight
this in light orange. Is it five? In positions one, you have
the id in position two. You have one in position three, you have two in position four. You have three in position five, you have four, as you can see in the area in the idea array in position five, you have four. Now let's try to search for
some, some other value. And the other value we
are going to search for is let's say sales volume of 34. So the sales value of 34 will be entered into
the match function, the area we are going to
search for a sales volume, and I'm going to
include the label in the search and we're
looking for an exact match. So type in 0.34 appears
at position five. Sales volume. Sales volume in units is
position 110 is positioned to 23 is position 312 is
position 4.35 is position pipe. I'm highlighting it in red. So 34 occurs at position five. Now let's say we exclude
the labels, right? So I'm going to change
the formula here. The formula, as you can
see in the formula bar, is matched 34, B12, B6. Let's exclude the label. Let's, let's make B1 B2. So now the match
function will search for 34 in the Arab B2 to B6. As you can see, it has been enclosed in a
selector box, right? So let's run this function. And the position now is for position has changed
because right now, position one is it, it contains the value ten. Position two contains
the value three. I'm highlighting it in
green. Position three contains the value 12, and position four contains
the value default. I'm highlighting it in green. So position four
has the value 34. So that is what a match function
does. Thank you so much.
33. What If Analysis in Excel: Hey everyone, welcome to a
new lecture on excellent. Today we're going
to take a look at what if analysis for
this type of analysis, I have a dataset over here. You have iPhone or number of iPhones souls sold by a shop. And you have the number
of iPad sold by a sharp. So I'm highlighting the value of the number of iPhones sold by a sharp in orange and the number of bad sold by the
shop in yellow. So let's say you
have a shop and you sell iPhones and iPads
and some other things, but we're just concerned
about Apple products. You sell iPhones and iPads. So you sold 2000 iPhone's less than a
month and he sold 400. I bought a bed in a month. And the price of the
iPhone is $1,000. I'm highlighting that in green, in the price of
the iPad is $700. I'm highlighting
it in light blue. So the number of iPhone sold is highlighted in orange is $2,000. The number of iPads sold
by the shop is highlighted in light yellow, and it's 400. The price of the
iPhone is 1,000, the price of the iPad is 7,000, and the total revenue
is equal to number of iPhone sold into price of
an iPhone and number of, plus number of iPads sold
into price of the iPad. So E2 into A5, A2 is number of iPhones sold. And if five is price of
the iPhone, so A2 and A3, A5 plus you have number of
iPads sold by the sharp, which is 400 into the
price of the iPad. So the total revenue
for this door is 2,280,000 in a month. That's a pretty
well-to-do store. Now what if, let's say
there's a scenario when you sell more number of iPhones. You have a scenario
where you sell more number of iPhones, or you have a scenario
where you sell more number of iPads. What happens to
the total revenue? For this sort of question? We have to use what if
analysis and to use whatever analysis we go to the Data tab and we go
to What-If Analysis. Just follow my cursor. We go to whatever analysis. Let's use Scenario Manager. Now. We'll define a
scenario for that. Click on the plus
symbol Ella, I have, I already have a
scenario to find, but I'll define a new scenario. Click on the plus symbol. Scenario name is 4,000. I bad soul. And we'll be changing the
number of iPads sold. So I'm going to click
on the cell over here, which is B2, which is
number of iPad sold. So we're going to change
the number of high-pass or notice we are using absolute referencing over here because we do not want
the cell to move around. So we'll be changing the cell, the number of iPads sold, which are rather
we'll be changing the value of the salvage stores the data on the
number of iPads sold. So what happens if
4,000 iPads are sold? So let's click on,
Okay over here. So let's say 4,000
iPads or sold. What happens to
the total revenue? Let's find out the summary. And let's click on, Okay. And as you can see over here, the summary has been
printed in a new worksheet. So we're changing the
value of the cell B2. I'm going to highlight
this in orange. So if 14000 iPads are sold, profit made by the shop is 4,080,000 or the total revenue generated by the
sharp is 4,800,000. I'm highlighting this in green. So the total profit made
by the sharpest 4,800,000, 4,000 iPads are sold. Now, I had another
scenario over here, right? So let's take a look
at that scenario. And that scenario, what I'm doing is I'm changing the
number of iPhones sold. So I'm changing the
number of iPhone sold to 3,000 and I'm changing
the value in cell E2. If you notice the screen cell E2 is highlighted in
orange over eager. Currently without an iPad. Iphones was sold or were
sold in the previous month. But I'm trying to find out
if 3,000 iPhones are sold, then what the total
revenue will be. So let's find out the
summary and the result. Sellers sell lumber in I1, as you can see in
the highlighted cell in the spreadsheet.
Click on okay. And when you change A2, and then I'm going to
highlight the data in yellow and change cell
A2, 2000-3 thousand. In a scenario, 3,000
iPhones are sold, then the total revenue
changes do 3,280,000. The total revenue
changes to 3,280,000. I'm highlighting
the syllabus stores the total revenue in late
week, the 3,280,000. Now we can do something else. We can do another what if
analysis and this sort of what if analysis. It's called Goal Seek. So we're going to
set the value of total revenue by 1 million. So we want to find out what will be the number of
iPhones that should be sold. If the shop has to make a sharp has to have a total
revenue of 5 billion. So what number of iPhones sold will lead to a total
revenue of 5 million. And this cell we're
going to change. We're going to change
the number of iPhones sold that is
highlighted in orange. In the spreadsheet. We're going to change the
number of iPhones sold until we arrive at a total
revenue of 5 million. As you can see over here, 4,720. After a ran the
what-if analysis, the world of analysis, automatically change the value of the number of iPhones sold. So now it shows that do have
a total profit of 5 million. And I'm going to
highlight this in yellow now to make it
and bright yellow, that is to make it more legible. So to have a sale of 5 million, the number of iPhone
sold has to be 4,720. And I'm highlighting that cell which contains the value 4,720. In Skype, you 4,720 iPhones would need to
be sel sold if you want to generate a total
revenue of $5 million. So that's it about
whatever analysis. What are the analysis can
be done using solver to. In that case, Solver uses optimization
algorithms to find out different values
based on constraints. And based on if you're trying to maximize something,
minimize something, or set the value of an
operation to a certain value. So that is all about what if
analysis. Thank you so much.
34. Using Solver to Solve Equations: Hey everyone, welcome to a
new lecture on excellent. Today we're going to take a
look at the Solver Add-in. So let's say we have to
solve the following problem. You have wages per
hour per worker. For each worker is paid a certain amount
of money per hour. Let's wrap this text. Each worker is paid
a certain amount of money per hour worked, uh, one is p $2 per hour could
do is pay $3 per hour, but Guthrie is paid $5 per hour. Block of four is paid
$6 per hour. Welcome. Five is bid $7 per hour. These are the we just
per hour per worker. And you're trying to find out how many hours they
should each work so that the company
wage bill is minimum. You don t know how many
hours they should each work. You're trying to find out how many hours each should work. So you don't know this, right? You don't know this. You're trying to
find this out using some mechanism, some patient. And then you're
trying to find out all you can minimize the
total wages per worker, given wages per hour
of each worker. So you're trying to find out
number of hours each worker should work so that
the total wage bill at the company's minimum. So how do you calculate the
wage bill of the company? You need a formula here. The formula is, click
on equal to E2, which is the clock tower, into wages per hour. Hours worked into wages per hour plus hours worked into wages per hour plus hours worked into we just borrow a
look at the formula bar. I'm writing the
formula right now. Into ages per hour. There are five workers. So each worker has
a wage per hour. Each worker is going to work for a certain number of hours. And based on that, we are trying to find out the
total wages right now it's showing zero because
we don't have data on how many hours
people should work. Or less. Renamed this
particular piece of information.
Workout was right? So right now the
total wages is zero, but we are trying to find out how many hours each
worker should work so that the total wage can be all the wage
bill can be minimized. Let's go to Data tab. Let's go to Solver. I'll do this again in case you did not
see this the first time. So click on Data and
then go to solver. So the objective function, what are you trying to find out? You're trying to find
out the total wages and you're trying
to make sure that the total wages is minimized. And you're going to change. Which variables are
you going to change? Which variable cells? You're going to change the
hours worked by workers. That is what you're
going to change. Let's say there are
some constraints. The constraints are
the work hour of work. One has to be lesser
than equal to 10 h he wanted worth more than 10 h. The work
hours of work Good. Two, which is cell number 83, has to be lesser than 15 h. He won't work more than 15 h. Let's add them
because strain. And in this case we're going
to add a constraint for worker number three or cell A4. The work hour of work at three
has to be less than 5 h. He won't work more than 5 h. So we have three constraints. Let's add another one,
a final constraint. The work hour of work before, which is contained in cell
A5 has to be equal to 4 h, so he will only work for hours, not less, not more. Click on Okay. Now,
click on solving method. Let's use simplex.
You can use simplex, you can use evolutionary, you can use GRG Nonlinear. These are different
optimization algorithms. A lot go into the math behind this underlying these
optimization algorithms. Because these algorithms
are quite complex, they are taught in
operations research courses. Just use simplex. Click on Solve. Perfect. And we have the solutions to the
problem over here. Solver says that if you make
worker for work for hours, then the total wage
bill will be minimum. That is what solver says. You don't need to make the
other workers work at all. If you make workup
for work for 4 h, total wage bill will be minimum. The total wage bill in this case is highlighted in
orange over here, $24. So if you make worker
for work for hours, that is how your total wage
bill will be minimized. So you can play around
with this solver. You can add different
constraints depending on the problem. You can add different
problems to the solver. Solver will solve it for you. So you can use it for a
variety of different things. So that's all for this lecture. Thank you so much for
joining me in this course. Thank you.
35. Pivot tables and Pivot charts: Hey everyone, welcome to
a new Excel tutorial. And today we're going to
take a look at pivot tables. But what do pivot tables do? Pivot tables basically
make life easier for you. It makes life easier for you. Pivot tables are used to
summarize information. Suppose you want to
find out the average of selling prices over here
in the dataset that we have. Suppose you want to find
the inventory cost average, or suppose you want to find out how many products are
different colors are there? Suppose you want to find out the count of different
colored iPhones, iPad or Mac box, you can use a pivot table
and this will summarize information in a
nice tabulated for, and you can easily
access information and data and analysis of
information using pivot tables. So we have a data set W, we have the product types which are iPhone or iPad mac book. You have the color which is, which can be white,
black, gray, or cream. The inventory cost of keeping the iPhone or iPad back books and the inventory
or the selling price. So this is a refurbish retailer. They sell refurbished products. So we're going to use
this table as an example. The entire table that
is highlighted yellow. But let's go to Pivot Tables. And over here, as you can
see, there are two options. You have PivotTables, have
Recommended PivotTables. Let's click on
Recommended PivotTables. So in this case, Excel
will recommend what you should represent
in the pivot table. But we're not
interested in that. It has a lot of recommendations
is telling you to include inventory cost and selling price in
the pivot table, but we're not going
to go by that. And what we're gonna do is we're going to delete the sheet, contains the Recommended
Pivot table. What we'll do is we will
create our own pivot table. So the range of data is the
entire table basically. And we're going to
get the output in the data underneath the
dataset in the same worksheet. As you can see, PivotTables has appeared underneath
and neither table. So over here on the right-hand
side of the screen you can see that there is a box
called Pivot Table fields. So your field
limbs, your filter, you have columns, you have
throws and your values. What do rows do? I start with rows. Rows
basically showed is a box where you can specify which variables
you want in the rows. Do you want product type in the rows be one color
and the roads be what? Inventory in the rows, the one selling price and
the rows, columns over here. I'm clicking on the column box and the box has a
green border to it. Right now since I'm clicking it in the column box over here, you have to specify what
you want on your columns, which variable you
want on your columns. And here the values box, I need the column box in
the values box, basically, you have to specify
which values you want to be displayed
in the pivot table. Do you want count of
different variables? Do you want some of
different variables or doing an average of
different variables, what we want to display
in the pivot devo, any of the filter box
which can be used to print the data is
not very useful. What we're gonna do right now, but it is there. So let's select a
variable for the rules. Let's say we want product type and the rules different
product types. We're going to click on it and
drag it into the rows box. As you can see over here, the product types are here. So I'm going to
highlight it in yellow. The product types are here. Oops. The pivot D, we'll basically
the entire window closed. Yeah. I'm going to delete this. So we're going to have the
product types in the rows. Now this is clearer and in the columns we are
going to have color. So you have prototype in the rows and the columns
you have colored. The pivot table is
displayed over here. I'm highlighting the entire
area in light green. Light green doesn't
look good on this. Lets the light blue. Yeah. So what sort of values do you want the pivot table to display? Let's look at different iPads. The count of different colors in terms of iPads,
iPhone and MacBook. So how many white colored
iPhones are there? How many white colored
MacBooks are there? How many white colored
iPads are there? How many cream colors are there? How many white color
or gray colored? Iphone, iPad or
Mac book is there? How many gold color iPhone
or iPad mac book is there? So we're going to
click on color, drag it to the values column, as you can see over here. Now you have the count
of different colors. So there is one black iPad, one creamer iPad, one goal iPad. There is one black
iPhone, too wide iPhones. There is two. There are two gray MacBooks and there is one white MacBook. And over here in the
grand total column, you have the total
of the counts. So you have three iPads. Iphones, you have three MacBooks and two grand total
counters, nine. Now let's say we want to
find out the inventory cost. And we want to find
out this inventory costs for different products. And we ought to find out the
average inventory costs. So let's include
inventory in the rows. As you can see over here. Now you have the
inventory costs to, the inventory costs are
displayed right over here. So for iPad, the
inventory costs $100, $110, and $126 for the
three different iPads. So there are three iPads and
the inventory costs are, thus, you have three
iPhones and three MacBooks. Now, let's remove
inventory costs from the rules and less include inventory costs
in the value box. And I have inserted inventory
cost in to the value box. So I'm going to remove
colors from the column. So as you can see over here, the total inventory
cost of iPad is 336. Total inventory cost
of iPhone is 477. Total inventory cost
of MacBook is 401. Let's say we want to find out inventory costs for each
product according to color. I'm going to include
color in the values box. The count of color over here, as you can see, is 333. I'm going to include color
in the row box tool. And now, as you can see, the black iPod has an
inventory cost of 100. Cream iPod has an
inventory cost of 126, I'm highlighting it and orange. Go light pad, iPad rather, not iPod, has the inventory
cost of hundred ten. Highlighting it an orange again, black iPhone has an
inventory cost of 234. Wide iPhone either
inventory cost of 243, et cetera, et cetera. Total count is nine. And total inventory cost. I'm highlighting it in
green over here as 1214. That is the total
inventory cost. Now, let's say we
want to find out the selling price two and
the total selling price. As you can see here, the sum of selling price columns over here. And what we did was we
clicked on the selling price and brought it to
the values box. And we pulled it
into the Values box. And I'm going to collect
the column in green. So the selling price
of iPad is 16, 40, selling price of
iPhone is two-to-one. A selling price of
MacBook is 2430, and black eye pad sold for 600, cream iPad, sold for 560. Goal iPad sold for four. Black. Iphone sold for 678. White iPhone sold for 1,540. And the grand total, total selling price
is six to 18. So this is how you can
find out selling price. Let's say you want to, instead of the total selling price, you want to find out the
average selling price. So what you can do over
here is you can click on the inflammation
or ice symbol beside the selling
some of selling price variable inside
the value box. So as you can see over
here, the value box here, inside the value box you have this option called
sum of selling price. Double-click it. And you can see it's
highlighted in green over here. And besides that, there's
an i, small i symbol. It's a button of sorts. You click on that button. Instead of the summer
selling price, you find out the
average selling price. And the average of total
average selling price is 698. I'm going to
highlight it in blue. 698. The average selling
price of what the pivot table has done is it has shown selling price based
on each category. So for each category there is a separate average
selling price. So iPad has an
average selling price of Pi fourths this
coloring it in green. Iphone has an average
selling price of 739, highlighting it in yellow. And Mac book has an average
selling price of 810, highlighting it in blue. So as you can see, each category has an average selling price and the total average selling
price highlighted in blue down here, 698. So you can play around
with what you want as your metric or what sort of metric you want out
of the pivot table. If you want the sum
of selling price, you can specify that and you'll get the sum
of selling price if you want the average of selling
price or inventory costs, you'll get that average. But you have to
specify it to Excel. And the way to do
that is to click on the particular variable in the values box and click
on the eye symbol. The eye symbol, as you can see, is beside the value
in the values box, beside the name of the variables average selling price or selling price variable that's here. And beside average
selling price, you have the eye option,
which you click. And then you can
change everything. Over here. You can find out the
total sum we can paint. The county can find out the
maximum minimum product called numbers, et cetera. Now let's say we want to
find out what percentage. Let's now remove all of this, the products and everything. Before that, let me
do something else. I'm going to remove
values from the columns. And then what we have
leftover here is the rules of which comprise off different types of Apple
products and their colors. Let's say we're trying to find out which color accounts for what percentage of
the total number of Apple products sold. So what percentage is black, what percentage is Queen? What purchase percentages goal? So what I would do is I am going to drag in color into the
values bar, value box. Over here. I have the entire column
highlighted in green. So we have three iPads,
iPhones, MacBooks. I'm going to click on one. I'm going to then right-click or control-click and
summarize values by you have this
option over here. You can summarize
values by average, show values as percentage
of grand total, right? Or column total or rototiller, parent row total or
parent column total. So you can show any
particular data as a percentage of the total. So let's look at it at the
percentage of grand total, because the grand total
over here is nine. So as you can see, black iPhones, iPad
constitute 11.1, 1% of the total sales
made Creamery pascals to constitute 11.1 word per cent of the total sales
made go-live birds, god, does constitute 11.1, 1% of the total CSP. So basically, it's distributed evenly because you
have three iPads, MacBooks, three I
foods, and total sales. So what you have to do is
you have to click on a cell. You have to control
click if you're using a Mac or right-click. And then you have to go
to the summarize value as option over here,
summarize value S. And you can summarize value
as grand total column, total, row, total pair in
total paid column total, etc. You can summarize values by this is another
way to do this. Instead of using the I
button inside the value box, you can summarize values by sub. In this case, it will
not show anything useful because we don't
have the sum of colors. So we'll go back to count. So less includes selling
price in the values column, as you can see, the
selling price or back in the pivot table. So less. Somewhere I click, right-click
or control-click on one of the cells underneath
the selling price column, index selling price column. And we'll summarize
values by average. As you can see, the value has
been summarized by average. So this is another way of changing what your pivot
table is displayed. If you want the pivot
table to display averages instead of sums is the sum of total
selling price or sum of total inventory cost. You have to, you
can click on one of the cells in the people. And you can then right-click
or control-click, drag down menu or drop-down
menu will appear. And in that, you have
to be ethically girl. Summarize value by option. You're to go to the
summarize value by option, you can select sum
count average, max. Let's select MAX. And as you can see,
the max is thousands, the most price here. The most pricey
items that are sold. Cost $1,000 to the customer. So $1,000 was the
maximum selling price. So that is what you can do. That is pretty much pretty
much what this is all about, what conditional
formatting is all about. And that is pretty much what you need to know
about pivot tables. Pivot tables are a
great tool if you want to basically
summarize everything. Now let's look at pivot charts. Click on pivot charts over here, it's under the insert ribbon. Click on pivot charts. As you can see over here
in the PivotCharts, do you have Excel classifying your iPads
as black creamer goals. So there are, there, is there or the selling prices of the iPads
represented as bars. So your bread,
cream or gold iPad, your black and white,
I fully agree. And white MacBook and
the selling prices are represented as bars. So right-click on this or
control click on this. And you can change chart, tried to line chart by chart. Let's click by chat. So Pi over here, as you can see, you have the count of
different variables. So how many of the phones that you
have the count of different folds,
different Apple products. How many of the
Apple products or iPads are many black iPads? How many cream iPads
or media goal? Ipad, how many black iPhones? How many are white iPhone
somebody a green bag, books. How many are white, black box? That is something you can do. I'll go back to the bar
representations to bar column. Let's see what else we can do. So what we can do is we can include inventory
cost in this chart. Just click on inventory costs in the pivot chart Field window
and drag it into the chat. Yes, now you have inventory
cost and is being represented as gray columns. Sum of inventory costs has
a gray color over here. So basically the gray columns
that you see in this chart, our inventory costs,
Let's remove selling price and you have the inventory cost us only the
inventory cost left. So gray Mac book has an
inventory cost of around 56. Let's check this. Gray Mac book has inventory
cost of 124 plus 132 to 56. So that is what you can
do with a pivot chart. So you can, then you can go back and represent selling price. You can remove colors
from the chart. And as you can see
right now, without, if you want to remove
colors from the chart, the pivot chart is
just showing iPad, iPhone and MacBook is not showing different
colors for iPad, different colors for iPhone, different colors for MacBook. Because we have uncheck
the colors option is just showing total number of
iPads and the selling price, total number of iPhones
and the selling price, total number of MacBooks
and the selling price and total inventory
cost pipette, total inventory cost for iPhone, total inventory
cost for Mac book. That is what is showing. Let's remove product type. And it's just showing
the total selling price and the sum of inventory costs. Now, let's say we
want to find out which colored product has
the most selling price. So as we can see over here, white is the product. White colored products sell
more than any other product. White-collar product,
colored product sell more than
black, green, gold, or gray white colored products, the most selling price. And finally, after we
do one last thing, there's an option over here and just follow my
cursor over here. It's called fields,
items and sets. You can specify a new
field using this. Suppose you want to find out
the total profit, right? So you calculate a pill. So basically what you do is
you go to the field option, you click on Calculated Field, and you name the
field, total profit. And it will give the formula
for this formula is selling price minus inventory costs. Click on Okay. And you have a new field
called total profit. And now in the chart we want to see total
profit by color. As you can see, the
white colored phones, white colored phones over
here, is most profitable. People like to buy
white colored phones, iPads, MacBooks essentially. Lets see which product
is most profitable. As you can see over here. I'm going to drag and
drop the product type in do the row category. Yeah. And now we can see which
product is most profitable. So after dragging and
dropping the product type in do the row category or
the access category. As you can see, MacBook is
the most profitable, right? Mac book has the
most total profit. So that's all about
pivot tables. Thank you so much, I'll catch you in the next lecture.
Thank you so much.
36. Statistical Analysis Process: Hey everyone, welcome to
a new Excel tutorial. And today we're going to look
at how to do data analysis and what to look for
in data analysis and how to interpret data
analysis results. Now for that, I have
a dataset over here. I'm highlighting the
dataset over here in, Let's highlight it in yellow. So this is the dataset. Now we have a number of
parameters in this dataset. What is the objective
of this exercise? First of all, we are
trying to find out which article on medium.com
will become popular. And how do you
measure popularity? Well, the number of
likes, each article gets. The articles get claps or likes. And based on that, we determined which article is popular and which article
is not popular. So greater the number of
likes an article gets. It is more popular just like YouTube or any other
online content platform. So we're trying to
predict or find out what leads to popularity. We're trying to find out
what leads to popularity. What are the causes of popularity for
medium.com articles? And side-by-side, we
are trying to predict which article will
become popular based on the number of likes
the article gets. So if the article gets more
lights, it's popular, right? So we're trying to
predict what are those factors that result in an article
getting more likes? And we're trying to predict
based on those factors, which article will get, what number of likes. So the claps or like
variable over here, and I'm highlighting the
entire column in light blue, is known as the y variable, the dependent variable,
or the predicted value. What is dependent variable mean? Well, dependent
variable means that the number of clubs and
article gets in this case, which is also represented
as the y variable. The number of clubs is dependent
on some other variables. So when some other
variables Very, then they impact the number
of likes an article gets. So that is why the number of likes or claps the article gets is a dependent variable and it's dependent on some
independent variables. And what are the
independent variables? The independent variables
are titled score, title sentiment score,
length of title, content sentiment score,
duration in minutes. And the interaction term between title sentiment score and
content sentiment score. Now I'm going to go
through all of them. So now we know that lapses the dependent variable or the variable we're
trying to predict. We're trying to
predict the number of clubs and article will get. And we're trying
to find out what influences the number of claps
or likes and article gets. The first thing that may
influence the number of likes and article gets is
the title sentiment score. Now the title sentiment score is babies between minus two to two. How did I get this? I scrape the data, then I ran sentiment analysis
on this data. This is the part of one of my projects I'm
doing for my PhD. So it's a sentiment
scores vary between -2.2 with minus two being
extremely negative sentiment, plus two being extremely
positive sentiment. And anything around zero
means neutral sentiment. So what is extremely
negative sentiments? I'm doing extremely poorly. I'm extremely sad. That is negative sentiment, that is high negative sentiment
expressed through text. What is positive sentiment? I am extremely happy
I'm doing really well. That is positive sentiment or extremely positive sentiment
expressed through text. So minus two, extremely negative plus two,
it's really positive. And what is neutral
sentiment? I am okay. That is neutral sentiment. You're not expressing
extreme positive or negative emotions or sentiments. So anything around zero
is neutral sentiment. Anything or minus two
is negative sentiment. And the thing around plus
two, It's positive sentiment. So there's a scale. And title sentiment score varies between minus two to two. What is the length of the
title in number of words? How many words does
the title have? That is another
independent variable. Contents sentiment score, again, varying between -2.2 is another independent variable
duration in minutes. There's some other
independent variables. So as you can see over here, named the independent
variables as excess. That is common convention. So independent variables
are called X, X1, X2, X3. So title sentiment score is X1, length of title and
number of words is x2. Contents, sentiment
scores extreme. Let's name it X3. Duration
more minutes is x. And what we have is an
interaction term which is titled sentiment score into
contents sentiment score. What does an interaction term
to an interaction dumped? Deaths for complementarity. So for different
values of titles, sentiment score, how does
content sentiment score impact the number of likes
an article gets? Let's say title sentiments covariance between
minus two to two. So for the value of minus two
for title sentiment score, how does content
sentiment score impact the number of likes
that article gets for the value of plus two. For title sentiment score, how does content
sentiment score impact the number of likes
the article gets? So basically you're testing
if two variables impact the dependent variable in
a complimentary fashion. So do two variables increase together or
decrease together while impacting the dependent variable or the predicted variable. So again, to recap, the number of likes is the predicted variable
we're trying to predict the number of
likes an article gets. And title sentiment score, length of title contents,
sentiment score, duration, and the interaction
term title sentiments go into contents
sentiment score. The independent
variables in that the interaction term
is there because we want to see the impact of title sentiment score and content sentiment score together on the dependent variable. Y is dependent variable
or predicted variable, x is our independent variables. So now that we
understand the data, Let's first look at if the
data is normally distributed. Now what do I mean by
normal distribution? For that? We will do a histogram for the number of clubs or
lifestyle article receives. So I've selected the
number of clubs column. I'm going to insert, I'm
going to go to histogram and you go to lot of histogram. Let's change the title. Histogram or likes. As you can see over here, this data seems unbalanced. Most of the data
is around 1,200.1. And as we go further
away from that, the data becomes sparse. So most of the data is at the lower end up the
lower end of the range, or lower end of the spectrum. Most articles do not
get a lot of likes as we go away from that. Some articles, a few
articles get a lot of legs. Is this normally distributed? Now what is a normal
distribution? Normal distribution is
essentially a distribution where most of the values
are around the mean, are around the mean
of that vague. So what is the mean of claps? Let's just find out
the mean of claps. Using the average function. The mean is 113. So are most of the variables centred
around hundred and 13? It looks like they're
centered around 113 rent a previously I said the dataset
looks unbalanced. But now that I have
actually gone into the different metrics
and the dynamics, we find out that dataset
may not be imbalanced. Most of the data might be
distributed around the mean. As you can see most
of the details between hundred one to 201. So what we need is a better
representation of the data. This does not look good. What we have over here
does not look good, the range does not look good. What this range tells
us, there are outliers, what outlet outliers
are values that fall beyond the general
dispersion of the data. So most of the data is
distributed around the mean, and most of the data is
around 100.1313 is a mean, highlighting it in purple. Most of the data is around 113, but then you have likes, which are around 900, 800. So some articles get 800 likes, some articles get 900 legs. These are far away from the average or the mean and the general
dispersion of the data, the general distribution
of the data. So these data points
are outliers. We'll talk a little
bit more about outliers when I do the
scatter plot next, but does not look like a good
representation of the data. So what we're gonna
do is we're going to format the data series. So I'm going to click on one of these columns are
towers over here. I'm going to right-click
or control-click and I'm going to click on
Format Data Series. And what I'm gonna do
is I'm going to change the bin width to 30 over here. I'm going to change the number
of bins to 60 over here. I'm going to change the -100 over here. I'm going to change
the underflow bin. As you can see over here, most of the data falls
around the mean. So this might be
normally distributed, and if it's not
normally distributed, you gone to run a regression. You can't do certain techniques. In this case, it seems like
it's normally distributed. Now let's do a scatter plot.
I'm going to delete this. I'm going to go to Insert. I'm going to select
Scatter Plot. And I'm going to select the
data for this scatter plot. Chart. Data ranges. Basically, I've selected the
chart data range. Name is basically
scatter off likes, likes, by over
content than demand. School. X values are, Let's
select the x values. The x values are
content sentiment score and the y values are. Claps. Click on. Okay. Now we have a scatter plot. Let's change the
title of the plot. That's always good practice. Like versus sentiment grows. As you can see in
the scatter plot, most of the sentiments
scores are, most of the legs are 0-100. Most of the legs are 0-100. And some of the lakes
like over here, the range of around 10,000, 800,000 are far away from the general
dispersion of the data. The general dispersion of
the data is 0-100 or 150. Some of the data is
around thousands, some of the data is around 800. These are outliers. When you have outliers, it skews your data. What does it do? Well, it skews your data to the
left or the right. So when you have
most of the data to the left of the mean or
to the right of the mean. That means the data is skewed. If the data is skewed, you cannot run regression. So you have to make certain
that the data is not skewed. If you run regression, when the data is skewed your regression,
we'll go, alright. So from the scatter plot
we can see that we have outliers because of which
the skew and kurtosis, kurtosis basically
measures the amount of outliers that you have. It measures how the
outlier affects your data. So since you have outliers, you still and you have ketosis. So what can we do
to remedy this? The most used procedure
to remedies outliers. Problems created by outliers or skew or kurtosis is to
do a log transform. So I'm going to define
a new table log off like and this will be the new
y mu predictor variables. This will be the new
wide formalized type in equal to type in log. Select the value for
number of claps. And you have the log of likes. Double-click on the lower
right corner of the cell and the formula is copied
to all the observations. And now you have the
number of likes or log of the number of likes for
all the observations. Now that we have
this, let's proceed. Do the next step. What do we do next? The next thing we do is we find out the
descriptive statistics. And to do that, we're going to go to data. We have the data analysis. Optionally here. Click on Data Analysis will do descriptive statistics.
Click on Okay. And here to select
the input range. And the input range we want is basically H one to H, which is the last row over here. Let's check 40303 Labels in
First Row summary statistics. Let's click on Okay. As you can see, the mean of
the log of likes is 1.26. Highlighting it in
green, standard error is 0.0 for very low
standard error measures, if your sample is good enough to predict about the
general population, about the general population from which the sample was drawn. What is the population?
Population is the population of all students
in the United States? A sample would be 100 students from the population of all students in
the United States. The standard error tells
you if your sample is good enough to predict the
population parameters or predict about the population, or make some prediction
about the population. Standard error of
0.04 is very small. So your sample is good. Median is 1.14 is near mean. And if medial is near mean, that means the data is
normally distributed. There is hardly any skew
because of the locks transform. We removed skew,
removed kurtosis. Because after the log transform, what you have is your median and mean which are almost equal. They are close by. So our data is
normally distributed. Most of the data is
dispersed around the mean. Standard deviation is 0.88. It's quite a bit so. Basically, there is
a lot of variants. There is a lot of variance in the data which the log
transform could not deal with. Let's see how these veins
impacts the final results. Kurtosis, which is a measure
of outliers, is -1.0 line. It's okay. It's not too high if it's around four minus four, etcetera, It's too high. Skewness is 0.3. For very low, it's around zero. So there's hardly any skew left in the DDA because of
the log transform. Ranges to 0.99 range is high. Given this context
because you have outliers and log transform removes or reduces the
effect of the outliers. But still, there
will be some attack. Minimum is zero, maximum
is 2.99, sum is 510, pounds is 400 true because we have were only two observations. Now we'll go back to the
sheet containing the data. Now that we understand
that there's hardly any skew and the data is likely to be
normally distributed. And we can do a histogram again. We can do a scatter plot
again to test this, but I will refrain from doing that and I'll go directly to correlation and find
out if data or rather the new variable log apply X is correlated with the other
independent variables. Other is the dependent variable correlated with the
independent variables. Let's do some correlation
analysis to test that. Click on OK. And
the input range is basically the entire Data. Labels in First
Row, check on that. Group by columns, check
on that. Click on Okay. And here you have the
correlation matrix. Now, reading the
correlation matrix as easy as I showed in my introductory correlation
analysis lecture. The correlation between
title sentiment score and titled sentiment score
on the column is one because title
sentiment score and titles sentiment score
at the same variable. So it's one, as you
can see over here. And remember,
correlation coefficients vary from minus one to one, with zero being no
correlation and minus one being extreme
negative correlation, plus one being extreme
positive correlation. So correlation between
two variables of the same nature is
around one or minus one. In this case,
correlation between title sentiment score and content sentiment score is one because they
are the same value. Now what we are concerned
with is the correlation between our dependent variable, which I'm going to highlight
in blue over here, and content sentiment score. What is the correlation between
contents sentiment score and our dependent variable, log of the number of likes and the
correlation is 0.1. Going to highlight it in yellow. This is not the kind
of Utah I wanted. So I'm going to change the color and the correlation is 0.103. Now what is the correlation between log of likes and title sentiments
called the correlation, which I'm going to highlight in a light orange over
here, is again 0.006. There's hardly any correlation. So what does this tell us? Well, probably title
sentiments calls do not impact the number of
likes an article might get. Maybe title sentiment
scores have no impact on the amount of
likes and article guests. Does content sentiment score have an impact on the amount of likes an article gets the
contents sentiment score. Correlation. Pearson's correlation
coefficient is 0.103 when you're finding correlation
between log of y or log of likes and
content sentiment score. The correlation is 0.103 with the correlation
coefficient that's around 0.103 is difficult to say that content
sentiment score will have any impact on the number of likes and article gets because the correlation
coefficient is too small. Ideally, you will have high
correlation coefficients, either positive or negative, if your correlation
coefficient is around 0.7 or let's say your correlation
coefficient is around -0.7. That means there is a
strong relationship. So any correlation coefficient
above 0.6 is desired. In this case, the
correlation between log of lights and content
sentiment score is 0.100. So does content sentiment
score impact likes? Well, it's hard to say so given the correlation
coefficient, on the other hand, the correlation coefficient
between log of likes and length of title is -0.14. Again, it is very, very low. Since it is very low, it's difficult to see
that the length of the title has any impact on the number of likes
that article gets. So should we proceed? Well, we should proceed with the regression to
find out if there is An impact. So we go back to the original data, and next we go to Data
Data Analysis again. And then we conduct regression. I have the range for the
independent variable, which is h12h 403. And the range for the independent variables is the range I'm
selecting over here. 12403. Input range is H one to h403, which is the log of likes. And that is the input range for the dependent variable or
the predicted variable. And x ranges 1-403, which is the range for the
independent variables, are the X variable or the
predictor variables or predictor variables are used to predict our predicted variable, to predict the outcomes
of a predictive variable. Click on Labels because we
want the labels to be there. I want residuals, I want residual plots and I want
normal probability plots. What are residuals? Residuals essentially
predicted value minus original y value. So we're trying to
predict the number of likes that article gets. And we'll have, upon
running regression, we'll have some value for the number of likes that
is found by the regression. The regression analysis
basically predicts values for the number of likes. And then you have the
original observation, which is the number of likes. The article actually has. Regular residuals are
basically predicted values or values found out by the
regression minus observed values. So if, let's say the actual observed value
for number of likes is 100 and the value finally, by the regression using
the equation that we are telling Excel to execute is, let's say 150, then
the residual is 50. So predicted value
minus actual value, predicted value of Y minus
actual observed value of y. So that is the
residual is also known as error because it's obvious that if your
predicted values far away from the
original observed value, then that means there's a lot of error in your regression. Your regression model
is not good enough. So that is a residual
and we're doing normal probability plots because we want to see if the
d, dy is probable. The data is normally
distributed. So click on okay. The regression results
yet and it's a new sheet. As you may remember, the R-square value and the adjusted R-square
values are important. The r-squared value, which I'm going to highlight
in green over here, tells us how good
the model is in predicting values for likes, for the number of
likes an article gets. The R-squared value is 0.04. R-squared varies 0-1.
If it's near zero, that means the model is crappy. If it's near one, that means
the model is very good at predicting the number of likes. The value is near zero is 0.04. So the model is not good. Adjusted R-square takes
into account the number of regressors or number of predictors or number of
independent variables you have. The more independent
variables to include in your
regression analysis, the adjusted R-square
value reduces. Why does it reduce? Because more independent
variables you include your basically
artificially bumping up the R-square value and adjusted R-squared basically takes that into account and
reduces the R-square value. Adjusted R-square is basically derived from the
R-squared value and takes into account the number
of predictors you have. Adjusted R-square value
is 0.03, very less, 0.3 per cent, or
rather three per cent. R-squared is 4% are adjusted. R-square highlighted here
in orange is 3.4 per cent, which are very, very low. But F value is 3.83. And F value is important
because it tells you if the model is actually valid. So if the F value found
by the regression is greater than the
significance F value that you have over here, I'm going to
highlight this entire Significance F value in blue. So if F value, which is 3.83 is higher than the significance
F, which is 0.002. That means your model. Value. So in this case, F
value from the model. After calculating parameters
of the model is 3.83. Going to highlight it
in some other shade. I'm going to highlight
this in yellow. So F value is greater
than significance of F. Significance F value, the
significance F value is 0.00 to F value is 3.8. So since the F value is
greater than Significance F, That means the model is valid. What else do we need? We need to take a
look at the p-values. I am going to highlight
the p-values in orange. As you can see over here, the p-values are
highlighted in orange. Let's choose another color. Let's choose green, grass,
green vegetation green. The p-values are important. Remember what I told
you about p-values in my previous regression
introductory lecture. P values need to be
lesser than 0.05. So if p-value is less than 0.05, then you say that the independent variable impacts
the dependent variables. So let's take a look
at the p-values. The p-value of title
sentiment score is 0.86, is greater than 0.05. So title sentiments
code does not impact the number of likes. The article gets. The p-value of length
of title says 0.001. So it is lesser than 0.05. So length of title impacts the number of
likes and article gets. Content sentiment score has
a p-value of 0.00, 400467. Continents sentiment
score impacts the number of likes and article
guess that's intuitive. If contents sentiment
score is positive, then people are more
likely to like an article. If Canton sentiment
score is negative, then people are probably less
likely to like an article. The interaction term
has a p-value of 0.89, which is greater than 0.05. So interaction term does
not have any impact on the number of likes
and article receives. And the duration has
a p-value of 0.46. So again, duration does
not have an impact. People don't get the
article as long or short. So let's look at the
coefficients now. Let's look at the coefficient of content sentiment score and the coefficients I'm
highlighting in yellow. The entire column that I'm highlighting in yellow
is the coefficients. The coefficient for contents
sentiment score is 1.6. Now what does the
coefficient tells you? A coefficient tells you how much your predicted or dependent
variable increases by, if you increase your
independent variable by one. So if you increase contents sentiment score by
one or by one unit, how much does it impact the dependent variable
or the number of likes? So when you increase contents sentiment score by one unit, the number of likes
increases by 1.6 human. And this coefficient
tells you that content sentiment score has a huge impact on the
number of lights. So when you increase contents sentiment score by one unit, it increases the number
of likes received by the article by 1.6 units. So Alton sentiment score is significant and it
has a high coefficient, which means contents
sentiment score truly impacts the number of likes and content sentiment
score can be used to find out the number of
likes and article gets. Now let's take a look at
the residuals over here. The residuals, as you can see, are between around 01.80, 0.1. So residuals from here, what seems like is that the
error is not that much. The residuals are
low kind of load. But let's take a more
nuanced look at it. First thing we're gonna do
is we're going to check the residual chart for
content sentiment score. As you can see over here, the residuals are
distributed around zero. So if the residuals are distributed around zero,
what does this tell us? This tells you that your
regression model is valid. It's a good regression model
because ideally errors, so the difference between
predicted value and actual observed values for your y-variable or for
your predicted variable, or for your dependent variable, should be around zero, the arrows should
be around zero, the residuals should
be around zero. The mirror they are
to zero the better. That means that your model is good because most of your
errors around zero or zeros. As you can see in this plot, the errors are randomly
distributed around zero and there is no
pattern in the errors. If there's a pattern, there
is a problem when it isn't randomly distributed around
zero and there's no pattern. That means that this particular variable
contents sentiment score can be used to predict
the number of likes. So that is how you
read a residual plot. You have to see if
the residuals are distributed around
zero and they are randomly distributed
around zero and they don't have any sort of
pattern in depth. That is how you read
a residual plot. And then we have the
normal probability plot. In the normal probability plot, we tried to see if there's
a straight line at around 45 degrees starting at the origin and moving upward. That is what we trying
what we tried to see. The slope. Obviously, if the line is at 45 degree
slope is around one. So as you can see on the y-axis, you have the dependent
variable or log of likes. And on the x-axis we
have sample percentile. And the line is a straight line, which basically is
around 45 degrees from the x-axis and the y-axis. And it basically
shows that there are values in all different
percentiles of the distribution. So that indicates that the
data is normally distributed. Because if there's a
straight line which is around at a 45-degree angle
from the x and y-axis. That is how you interpret
the normal probability plot. And lastly, how do you find
out a regression equation? The regression equation
is quite simple. Is y equals to constant
plus Beta naught. And Beta naught is
the coefficient, coefficient or less a beta one. Into that is the coefficient into X1 plus Beta2 into x2, et cetera, et cetera. So y, which is the
dependent variable value, or the predicted value
is equals to constant plus the coefficient
of x1 into x1. In this case, X1 is titled sentiment score plus
the coefficient of x2 into x2 plus the coefficient
of x2 and x3 plus era. The final term is error. So dot, dot, dot
and you have error. That is what the regression
equation looks like. And in this case,
as you can see, some of the variables do not impact the dependent variable. Some of the
independent variables have no impact on the
dependent variable. So you can remove those, you can drop those from
the regression equation. What you can do is, for the sake of having a
regression equation that includes variables that truly impact the predicted
or dependent variable. What you can do is you can drop those variables that do not have a significant impact on
the dependent variable. And by significant impact, I mean p-values
greater than 0.05. So if p-value is less than 0.05, that means there is significant impact
of that variable on the dependent variable. If p-value is greater than 0.05, that means there is no impact of that variable
on the dependent variable. So what you can do is you can
drop variables which have p-values over 0.05 and only key variables which have
p-values less than 0.05. What will be the regression
equation in this case? So likes equals to constant plus coefficient of content, sentiments
into content. Then determined scores. So you can have a
regression equation.