Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:01
in this. Listen, I'm going to talk about information functions and why are they important for advanced or intermediate use of excel
00:11
Now? Information functions are those that people almost never use in worksheets alone. So their use is primarily intended
00:22
to work with look of functions
00:25
and to work a CZ, some kindof information giver when you're making macros and doing work it automation.
00:37
So first of these is cell
00:40
and sell returns requested information in for type over reference cell or or group of cells.
00:48
And it's ah,
00:50
it's very complex to type because you have to know about
00:55
what are you looking at
00:58
looking for? And when you look at this info type, it's
01:02
explanation is nothing. So you have to click on help function, helping dysfunction and get the
01:07
full explanation. And I'll just give you the hint because it makes absolutely no sense for me to explain all off in for types you can request on a cell
01:19
so it can be addressed, which gives you the
01:23
coordinates. It can be called, which means which column is it? It can be color. It could be contents, which basically gives you what's in the cell.
01:32
It can be file name, so you can see the full path off the file in which this cell is the format, which is very important there. There's parenthesis is which shows you if there are parenthesis. Is in the
01:46
the cell, used or not
01:48
prefix protective. It's protected type of cell with a bit of cell in a calm, so
01:57
you can expect the all kinds of information. Sometimes they can be very important when you're writing macros. So, for example, if you pick format
02:07
as an infant type,
02:09
this is what you can get his, ah, return from the function.
02:13
So Gee's for example, General, um, if you get the answer or return like comma to it means it's formatted. There's a number with two decimal points
02:25
and so on and so on. I'm not going to explain every one of them because there's quite a few. Uh, what you can do is always go into into help, and if you need to use this sells, you can
02:39
see.
02:40
But what can happen with it?
02:44
So just to give you an example of cell function,
02:50
uh, so, uh,
02:52
this is Ah,
02:53
this is, for example, you type so
02:58
and then you look for
03:00
for mint
03:02
off
03:04
this one here,
03:07
and you get that too.
03:10
And here you will get general for mating. Although this is formative is text because it's just general for mating, it won't return difference between general former thing and formative is text,
03:24
and, uh, and yes, that's pretty much it. So you have to
03:30
looking these things,
03:32
they could,
03:34
ah,
03:35
trials to see how it behaves before you can actually use it.
03:40
I'm just mentioning it because sometimes it can be very, very useful.
03:46
Um,
03:47
the other function we're talking here about information function is info,
03:54
and it returns information about operating and Myron. Now, you might think, Why would I need that in excel sheet? And, uh, the reason is, for example, if you're writing a macro
04:08
at the beginning of it,
04:10
you want to check
04:12
um, which version of accelerator using
04:15
because if you're using some function that they're not available in previous versions of Excel or in the current versions of Excel that are not the one you have intended, For example, we're talking here
04:29
about off off his 2019 and excel within that office package.
04:34
And if you're using office 365 Some off. The functions cannot be used because they or some of the parameters, for example, for cell function cannot be used in our frustrations expiry. You want get the answer. So if you know that you're using something that's specific to this version off
04:54
Excel that you
04:56
can get info and then release
05:00
and you get ah,
05:02
the
05:03
answer 16.0, which is this Release off Excel.
05:08
Also, this will give you all his version.
05:11
This will give you the system so it will answer to you. Is if this is a window system or max system.
05:18
This isn't the number off
05:21
worksheets in this file. And, for example, this is information on recalculation mode off this worksheet. So is it automatic or manual? This one is automatic, of course. So,
05:38
uh, the other
05:40
the effects can be the city directory can be origin, which is something that is there mainly for compatibility with the lotus 12 tree. Anybody knows what is 1 to 3 was one of the first spreadsheets offers and so on, so on. So this is
06:00
this is all about info.
06:01
If you actually need these things, you can also study them in detail.
06:05
I'm a pressing on the help
06:08
link in the in the explanation syntax window off the dysfunction.
06:17
So, um,
06:18
next group off functions which we're going to talk about our is functions and their 12 these franks is black. Is there is there is error is even Its formula is logical, is not text is number is odd Israel and is text and they're quite self explanatory. So
06:38
they just returned through a false
06:41
and for example, if you reference to a selling, want to check if it is text
06:46
If it is text you get true. If it's something else, you get false. It also is Izod will give you the truth. It's old number and false if it's everything else, including decimal number.
07:01
So examples off his functions
07:06
you can have here. So, for example, is blank
07:12
for
07:14
cell that has something in it will give you falls.
07:16
But if you have really blank cell, it will give you true.
07:20
So
07:23
is there we'll give you
07:25
true because we have division by zero here. The only difference between ease air now and his hair is that if the
07:35
air is an A,
07:36
it will give you a different answer for dysfunction,
07:42
then this function because this one will return
07:46
true for any kind of error. And this one real return falls. If it's an a error
07:53
Ah, then we have is even so 25 years old numbers. So it's false.
07:58
This is his formula. So it checks if you have formula in it and you have, so it will give you true.
08:05
And this one is formula and gives you false because we have just the constant here. So these air
08:11
is functions.
08:13
And finally, in this listen
08:16
Ah, I'm going to talk about end
08:20
and then is very interesting function between converts known number two, number dates, the serial numbers through to one and pretty much everything else to false. So let's do some check up on it.
08:35
So,
08:35
um,
08:37
we have, ah,
08:39
this number that is formative is next,
08:43
and it will convert it to actual number.
08:48
Ah, but if we type here a B C,
08:52
it will give a zero because it's ah,
08:56
it's everything else.
08:58
So if it's a date, it will give us the serial number of a date.
09:03
If it's true, it will give us one If it's false, it will give us zero. And if it's text, it's also going to give a zero.
09:11
So this is how function and works.
09:15
It's Ah,
09:16
it can have again use, especially in the writing. Mac Rose
09:22
either rise.
09:24
I don't see much use of it in real world.
09:28
And that concludes this lesson. Thank you for your attention. In the next lesson, we're going to be talking about look up functions.

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor