YT YouTube2Text

YouTube2Text → Transcripts → Excel exercise - Conference participants

Excel exercise - Conference participants — Transcript

by Albert Fornells · 2,943 words · 421 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:01Welcome to this video where we are going
0:04to check different concepts described it
0:07in the unit called Excel formulas. The
0:11first thing we are going to check is how
0:14you can combine information from
0:16different columns that contains text. In
0:20this case we are going to create a new
0:23column called full name. Okay.
0:26And we it is this column is going to
0:29include the information from the name as
0:32well as the surname. For doing that we
0:34use the amperson operator. So the first
0:37thing we need to do is select in this
0:39case Thomas and then we use the amperson
0:42operator. As you can see when you
0:45include the amperson symbol between the
0:47content of two cells basically what you
0:50get is a new cell that contains a text.
0:54But here as you can see the na the name
0:57and the surname are together. There is
1:00not an extra space between them. And
1:03this is because when you define the
1:05equation you are saying please put
1:08together B2 and C2. If you want to
1:11include an additional extra space you
1:14need to say okay let's include an extra
1:16space. And that's why you need to use in
1:18this case the empress operator two
1:21times. The first time for including the
1:23white space and the second time for
1:25including the surname. If you apply if
1:28you click two times in the green square
1:30the system will reply we apply the same
1:32equation uh along the whole column.
1:37Other other interesting operation that
1:40you can do in Excel formulas is for
1:42example calculate the age of someone. In
1:46this case, we are going to create a new
1:48column called H. And the H is calculated
1:52as the current day minus the birthday.
1:57And when you do this difference,
1:59basically what you get is the number of
2:02days between these two periods because
2:05what we need is the number of years. We
2:08need to divide by 365.25.
2:11In this case the 25 the 0 25 is because
2:15the lip year and last and least the
2:19result must be uh displayed without
2:24decimals because in general if you are
2:27asking about the age of someone you
2:29don't want to you want to get only the
2:31integer part. So let's do that. So the
2:34first thing is we get the current day
2:36and we do minus
2:41the birthday.
2:44Okay.
2:46So we get this number. If uh if you do
2:50the operation and you see a date is
2:53because Excel sometimes is assigning the
2:56date based on the original data. So if
3:00you see a date no worry just specify a
3:04this is not a date this is a number once
3:07we get the number of days we divide this
3:10amount by 365.25
3:14okay so we get the h including decimals
3:18here be careful don't don't use this op
3:21this elements for removing the decimals
3:23because what you are doing is rounding
3:26it's not the same 69.4 24 then 696.
3:31So in this case if you want to take the
3:33integral part you need to use the int
3:36formula.
3:39So now we get the current h. Now it's
3:43possible to remove the decimals select
3:46two times click two times square and we
3:48get the h. Other interesting thing that
3:51we can do using Microsoft Excel is using
3:54the if function. The if function allows
3:57you to apply a logic. Okay. So in this
4:01case we are going to get to create a new
4:03column called uh total expenses level.
4:07And the idea is if this value is lower
4:11than 500 then we are going to assign the
4:14low
4:16uh label. If the value is between 500
4:20and 700 then it's going to be height.
4:23And if it's between 500 and 700 then we
4:27will assign the category called middle.
4:30For doing that we can use the ifs
4:32function. So in this case if s this
4:35value is lower equal than 100 then we
4:39write low.
4:42If the same value is greater than
4:46700
4:49then we write height
4:52and in the rest of options we write
4:56middle medium.
4:58It's important to remember that through
5:00means okay no matter the condition
5:03please do that.
5:06So here we get the three different
5:09levels and the last thing we were
5:11explaining in this unit was how you can
5:13use the and and or operators in order to
5:16apply conditions. So for example if you
5:19want to check if someone attended the
5:22three days for example
5:25uh full attendance
5:28you can use the and function. The and
5:30function is checking that when these
5:33three elements
5:36this, this and that are through then the
5:40result is true. In this case, because
5:42this person didn't attend the first day,
5:45we get false. But when we drag and drop
5:48this equation, we can see that in this
5:50case, we get through because this person
5:53attended the three days. But sometimes
5:55we want to check only if at least this
5:58person attended one day. So in this case
6:00for example we will create something
6:02called at least one day.
6:05Then we can use the or function. The or
6:08function is going to tell us okay when
6:12this person attended at least one day.
6:14So in this case only in this case when
6:17the person didn't attend we get the
6:20false value.
6:22Now we are going to check how to build
6:25some statistics. For example, let's
6:27suppose we want to count how many males
6:29and how many females. So it means that
6:31we need to focus on this column. So we
6:34create a new spreadsheet called
6:36statistics
6:39and we define male female
6:47participants
6:49and percentage of participants.
6:55Okay. So if we want to count how many
6:59participants are male, we need to use
7:01count ifs. So the first parameter
7:04criteria range one means please select
7:07which range of cells contains
7:11the value. So you want to check in this
7:14case the E column. It's very important
7:17that after you select the column now you
7:20include comma or semicolon depending on
7:23the the location of your computer. Okay?
7:27Because if you don't include the comma
7:29the system will change the reference. So
7:32for example, pay attention if I don't
7:34include the comma the system is
7:37highlighting criteria range one. So when
7:39I click on the statistics I will get an
7:41error or the system will change the
7:43value. Okay. So again very important
7:47first thing you have to do is select the
7:49information then the comma and now you
7:52can go back and select in this case the
7:55value that you're looking for. Okay.
7:58Now we can click click two times drag
8:00and drop and it gets the information for
8:03regarding males and females. Other
8:06important things to take into account
8:08please here when you write the condition
8:11never select the condition from here.
8:15Let me show you an example. In this
8:18case, if instead of statistics A2, I
8:21select for example uh this one,
8:26we need when I move the equation, we get
8:29the same result. Why? Because here I'm
8:32checking this cell and here I'm checking
8:34this other because here E9 contains male
8:39and E10 contains also male. Okay. So
8:43please always when you select the values
8:45it's extremely important that you select
8:47the information from the table. So you
8:50know that always here you have the right
8:53uh the right labels. Then we get we
8:56calculate the sum of both values and
9:00then we can calculate the percentage
9:02which is basically the number of male
9:04participants divided by the total
9:07number.
9:08Once we do that, if we apply the
9:10percentage symbol, the system will sorry
9:13the percentage option, the system is
9:14going to format the cell as a
9:17percentage. Format means the system
9:19multiplies by 100 and it includes the
9:22percentage symbol. Here, if you want to
9:25apply the same equation for females, you
9:29need to block in this case before. Why?
9:32Because if you drag and drop here, the
9:35system is going to use this value and
9:37this value. But you don't want to use
9:40this empty cell. You want to use 500. So
9:42in this case, you need to block the row
9:46number four. Remember, always when you
9:48block, you're including the dollar
9:49symbol before
9:51before the element you want to block.
9:54Okay. So now we can get the expected
9:58information. Other operation that could
10:01be interesting to know is for example
10:02let's check uh the
10:06the average of age
10:13by males and females. So in this case we
10:16need to use average if s
10:20the first parameter is average range. So
10:22you need to select the range of cells
10:25that contains the elements that you want
10:27to do the average. In this case, we
10:30select the new column that we created
10:32before. Then we select the column that
10:36contains the condition. And now again
10:38and as you did before, we need to select
10:40the male value.
10:43So in this case we get the average of
10:46age. We can in this case just uh leave
10:51uh and use one decimal
10:55and we can drag and drop. Okay, very
10:58important in this case we can drag and
11:00drop for one reason here there are no
11:03row numbers
11:05so it works but if for example
11:09if you when you do the max if s
11:14if in this case you select
11:22if in this case you select a range of
11:24cells
11:27for selecting
11:29the age as well as the gender.
11:35Okay, in this case you need to just or
11:38yes block these values because when you
11:40drag and drop as you can see the system
11:42is updating the row numbers. Okay, so be
11:45careful with that. Both are correct. You
11:47can use the whole column or you can use
11:49a range of cells but always you need to
11:53understand what you are doing. What I
11:55recommend is that if here in the list of
11:58elements you know that at the end there
12:02is there is no additional information in
12:05general is easier and quicker to use the
12:07whole column. But if for any reason
12:09after this range of cells with
12:11information there are other additional
12:14range of cells with other data in order
12:16to avoid uh wrong uh results then it's
12:21much better use a range of cells. Okay,
12:24let's uh do other statistics. For
12:26example, in this case, let's calculate
12:28for example how many male and females
12:31depending the level of the expenses. So
12:35the first thing we need to do is we
12:37generate the table.
12:40Okay, low, medium, and high. Uh again
12:44one common mistake is that you don't
12:48write the text as it's included in the
12:52other places. Uh and for I don't know
12:56why but in the last years I have
12:57realized that many of you accidentally
12:59include here an extra weight space. Be
13:02careful with this extra weight space
13:04because the system won't be able to find
13:07height with an extra white space. Okay.
13:10So here if you want to count how many
13:12people how many how many participants
13:15again count if s the first column
13:18includes in this case the
13:23um
13:25the gender
13:27we select the gender then we select the
13:33level level of expenses
13:37okay
13:40so This this equation returns how many
13:44participants are male and at the same
13:48time they are categorize it as low in
13:53travel expenses. In this case if you try
13:55to drag and drop you will get a wrong
13:59result. Why? Because in this case as you
14:01can see there are some row numbers 14
14:05and 13. And when you drag and drop these
14:08values are updated to 15 and 21.
14:12So please be careful always if you need
14:15to create a cross tab table of
14:18statistics once you create the first
14:21formula please try to analyze if you can
14:26write the equation when you drag and
14:27drop in this direction and then check if
14:30it works in the other direction. So
14:33first of all in this case if you are
14:36going to drag the equation in this
14:38direction you are changing or you are
14:41moving across rows. So you need to pay
14:44attention to the row numbers in this
14:46case. Here there are not row numbers.
14:49Here there is a row number 14. It means
14:52that when you drag and drop, the system
14:55is going to use A15 instead A14
15:00because in this case we want to use A15
15:03because we want to check female. In this
15:06case, it's fine. I want to allow the
15:08system to move here. There are no row
15:11numbers. So I don't I don't need to do
15:12anything. And but here B3 it's doing a
15:17reference to low. If we drag and drop
15:19the system is going to use 35 not low.
15:23So in this case we need to block 13. Now
15:26as you can see when you drag and drop
15:28the system is properly referencing
15:32the labels. Now if we repeat the same
15:35process but in the other direction we
15:38need to pay attention to the same
15:41problem but in this case considering
15:43only the columns. In this case here we
15:46can see E E which is pointing the gender
15:49but here it's not E it's FF. So it means
15:53that because we want to use the gender
15:56column we will need to maintain we need
15:58to block in this case the the E column.
16:01Here we see other column in this case
16:03the A column. A column points to the
16:07column which includes the labels about
16:09the gender. Because we want to use male,
16:12we need to block A14. Otherwise, the
16:15system will use 35 instead of male. With
16:19Q, it happens the same. We want to use
16:21the level of travel expenses. So, we
16:23need to block Q. But in this case in B,
16:27we don't need to block. Why? Because
16:29here I want to use medium. So, I need to
16:31allow the system to move from this to
16:33this. Now when I drag and drop the
16:36equation and drag and drop again the
16:38equation then we get the right result.
16:42If we repeat this this for example uh
16:46total number of participants.
16:52Now let's suppose uh we want to do the
16:57same but in do the same but instead of
16:59counting how many participants I want to
17:02check the total amount of total expenses
17:06depending on male female low medium and
17:08height. In this case we need to repeat
17:10the same process.
17:13this case uh total amount of travel
17:18expenses but in this case we need to use
17:22sum ifs s because we want to sum all of
17:26this information. So again here pay
17:28attention what do you want to what do
17:30you need to do you need to select as
17:31first parameter the range of cells that
17:33you want to use in the sum. So you need
17:36to look for the travel expenses column.
17:40Then look for the gender column
17:44because it's the condition. We select
17:47male.
17:48Then we select the level of travel
17:51expenses.
17:53And last but not least, we select the
17:56low. So here this is the total amount of
17:59travel expenses of all the participants
18:02that are at the same time male and they
18:05are categorized as low in the travel
18:08expenses.
18:10So if you need to apply the same
18:12equation in the other elements you need
18:14to follow the same process as you as we
18:18did before. So in this case because the
18:21first movement is in this direction. In
18:23this direction, we are only moving uh
18:27numbers, row numbers. So, let's review.
18:30In this case, there are no row numbers,
18:32so we don't need to do anything. There
18:34are no row numbers. We don't need to do
18:35anything. Here, there is one row number,
18:38but they want to allow the system to
18:40move. So, that's fine. Here, there are
18:42no row numbers. And here, there is one
18:45row number, which in this case, I need
18:47to lock. Okay? Because I want to fix the
18:51low label. So now when I apply the
18:54equation it's possible to get the other
18:56result. If now we do the same but in
18:59this other direction again we need to
19:02focus but in this case in the columns.
19:05So we need to repeat the process in P
19:09in E
19:11in A
19:13and in Q as we did before. Now when we
19:17drag and drop then we get the results.
19:21If you want to apply some kind of uh
19:26look and feel style, remember you can do
19:29it. You can do this using the cell
19:31styles. Be careful with the option
19:33format as table because this option is
19:35not just including colors. It's creating
19:37an Excel table. This is something we
19:40will discuss in further sessions. So be
19:42careful with that. Don't use it. So in
19:44this case, if I want to use Celest, what
19:46I can do is select for example the
19:47titles. I can include them for example
19:50in this and here I can use for example
19:55and
19:57uh a light blue for example here if you
20:00want to apply the same format for
20:03example you can select this
20:06and apply to this because here I was
20:08rounding and in that I'm not rounding
20:10that's why the system is removing this
20:12the previous versions okay uh here again
20:16you can apply the same style if you
20:20wish. It's possible to include for
20:22example uh borders. So it's basically
20:26play with these options and at the end
20:29you will get the expected uh result.
20:33Okay.
20:35Uh that's all from my side. If you have
20:38any question don't hesitate to contact
20:40me. Bye-bye.

About this transcript

This page contains the full transcript of Excel exercise - Conference participants by Albert Fornells, generated from the public captions YouTube serves with the video. The transcript has 2,943 words across 421 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to choose a caption track available on the video.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, caption language selection, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.