WEBVTT

1
00:00:01.280 --> 00:00:15.880
William Rosener: Well, so, Dr. Mo. Rosner, welcome back in this video. You'll learn more about formulas using Google sheets. Let's start by. Take a look at what you learn how to create, and then we'll go ahead and create it from scratch here. So I mentioned you got a little company, a little business, and we'll get some employees.

2
00:00:15.890 --> 00:00:45.040
William Rosener: We have, like a person, if you have for first name, last name, their birthday, where they're going to be using foremost to compute their age, and then, whether or not they're a a Rp. Eligible. This is a retirement benefit. Then we're going to come on down here and get compute the age of the average employee, the annual supply. All this number of less than forty and number of those that are eligible for a Rp. And again, the beauty of ah hormones. In this case she's in Google sheets by change. One of these guys let me change this

3
00:00:45.050 --> 00:00:58.730
William Rosener: actually in this case. Here these robin performers. So let me change this. Right now. I can see the fifty eight, and the are a algebra, and some of these values are going to change to let me change this. We make it.

4
00:00:59.070 --> 00:01:00.420
William Rosener: He did it for

5
00:01:00.920 --> 00:01:03.850
William Rosener: okay. So now, all of a sudden,

6
00:01:04.080 --> 00:01:16.930
William Rosener: this person person is now thirty, eight rather than fifty eight, no longer eligible for Arp. And you're going to also have noticed that some of these numbers have changed as well. Okay, Now that we know what we want to do. Let's get started.

7
00:01:17.710 --> 00:01:26.680
William Rosener: So the first step you're going to want to do is hopefully you got your count on Google, you're going to go to Google Com. Come over here and press on the Google apps these little dots right up here.

8
00:01:27.600 --> 00:01:30.170
William Rosener: Come on down and choose. Drive.

9
00:01:33.020 --> 00:01:40.830
William Rosener: You're going to come up here. You're going to choose. Actually, there was the one we were just looking at. Simon. Seventeen finished. I'm going to choose new

10
00:01:40.850 --> 00:01:43.839
William Rosener: phenomena cheers Google sheets.

11
00:01:46.370 --> 00:01:52.260
William Rosener: Okay, in the same era as you found a link to this video, you should also find some

12
00:01:52.380 --> 00:02:07.239
William Rosener: with that Um. So a little of blurry right there. There we go find um some data. So rather than typing all this in you're going to simply um select that copy that there's a control, C on the windows, and it's going to come right here and paste.

13
00:02:07.870 --> 00:02:11.460
William Rosener: Then take a look. Here we're going to go ahead and put a page,

14
00:02:12.230 --> 00:02:14.239
William Rosener: and whether they are

15
00:02:14.340 --> 00:02:16.409
William Rosener: A. A. R. P.

16
00:02:17.300 --> 00:02:18.670
William Rosener: I'll show you,

17
00:02:19.510 --> 00:02:25.990
William Rosener: and i'll tell you what i'm. Here, let me just come along. Come right back here, and i'm going to grab some more data.

18
00:02:32.570 --> 00:02:34.730
William Rosener: And i'm going to paste that right here,

19
00:02:34.920 --> 00:02:42.050
William Rosener: and i'm going to go ahead and get this a little bit larger font size for you. Let me see what fourteen looks like here. So let me just grab the whole thing.

20
00:02:43.580 --> 00:02:45.440
William Rosener: He had what was for him.

21
00:02:48.660 --> 00:02:58.060
William Rosener: Okay. So anyway, take a look here. Probably make sure you want to make sure if these are not, I'm going to come right up here to format, and these need to be formatted

22
00:02:58.720 --> 00:03:03.549
William Rosener: as date. So once you come out and go ahead and re-check. Format

23
00:03:03.700 --> 00:03:06.090
William Rosener: and come down and choose date.

24
00:03:07.230 --> 00:03:27.000
William Rosener: Now i'm going to go ahead and just get all this center. But i'll tell you what. Let me go ahead and merge this information. So right now it's on three cells, because it's kind of carried over from this case a nine, B, nine, and C nine. So i'm going to simply highlight that i'm going to the format and repeat this for remaining ones.

25
00:03:27.510 --> 00:03:31.600
William Rosener: I'm going to grab that information and span it across three cells

26
00:03:39.340 --> 00:03:41.119
William Rosener: and two more here.

27
00:03:51.120 --> 00:04:05.040
William Rosener: Okay, these actually i'm going to go ahead and write a line. So i'm going to highlight those cells from right up here, and i'm going to choose the right align. But all this information up here. Let me just go ahead, and I get that information center,

28
00:04:05.050 --> 00:04:07.490
William Rosener: and I believe Let me go ahead in.

29
00:04:07.540 --> 00:04:16.249
William Rosener: Add a little color up here, so I highlight that top row. Press on here the fill color, and I don't care if it's shade it can blue agree what I do with them.

30
00:04:16.260 --> 00:04:22.170
Okay. So now we're ready to start working on some formulas. So the first one i'm going to work on is the age.

31
00:04:22.180 --> 00:04:36.569
William Rosener: And to do that i'm going to come right up. I can actually type here, or I can come up here to the formula area, and i'm going to first of all put equals today of them parentheses, close parentheses. And so today is August, thirty to some obviously

32
00:04:36.580 --> 00:04:56.460
William Rosener: coming on. When you watch this video, your numbers are going to be different. So um! Maybe it's a year from now. Maybe it's two years now. So this might be. Who knows but time you'll get around to watch this so anyway. Um, Now, what i'm gonna do is come up here and go to a format, and I am going to choose just plain and text.

33
00:04:57.570 --> 00:05:10.679
William Rosener: So that is put that date today's date in terms of a digit. Now, what i'm going to do is i'm going to take this minus their birthday. So i'm going to take today's day minus

34
00:05:11.770 --> 00:05:13.129
William Rosener: see you, too.

35
00:05:15.310 --> 00:05:35.140
William Rosener: So today, you know I'll I'll wait till now. So if you're born on March fourteenth, one thousand nine hundred and ninety eight. Today you would be eight thousand nine hundred and thirty, five days on. So again, if you're giving something other than that make sure you come up here to format and make sure you got it on plain text, and maybe

36
00:05:35.150 --> 00:05:38.649
it's not formatted as a date or anything else. It shows plain text.

37
00:05:38.660 --> 00:05:49.370
William Rosener: Okay. So now, what we're going to do is we're going to take all of this information apparatus at the beginning. Let's see at the end, and i'm going to divide that by three hundred and sixty, five

38
00:05:50.250 --> 00:05:57.549
William Rosener: point two, five, and that's because the reason I need to put those parentheses is because division would have precedence over subtraction.

39
00:05:57.580 --> 00:05:59.550
William Rosener: Summer. Go ahead and have the other

40
00:05:59.980 --> 00:06:12.659
William Rosener: and at this point I could see this employee. It's twenty-four point four, six. So typically people don't go around and up Twenty-four Point, four, six years old. So they're going to round that So i'm going to go ahead and to the near signature.

41
00:06:13.100 --> 00:06:16.330
William Rosener: So i'm gonna do is put an I and T here at the front.

42
00:06:16.870 --> 00:06:28.790
William Rosener: The closing parentheses at the end. Some equals make sure all formulas, whether in Google Sheets or Microsoft, excel. Start with an able sign I've got in T. That's going to return the Integer portion.

43
00:06:29.140 --> 00:06:38.549
William Rosener: Start off with today, minus their birthday, divided by three hundred and sixty, five point two, five, because of the leap here, and then get the integer portion now,

44
00:06:41.390 --> 00:06:44.720
William Rosener: and I'm missing my my practices here at the beginning.

45
00:06:47.560 --> 00:06:57.020
William Rosener: Okay, So that's a particular um employee would be twenty four years on, and again make sure you get it. It's almost like I had it there. But so we got two parentheses

46
00:06:57.180 --> 00:07:01.809
William Rosener: one. This one is actually closing the whole I and T. There.

47
00:07:02.500 --> 00:07:15.040
William Rosener: Okay. So once you've got that just simply a position to cursor on the film handle and drag that on out. And now I can see if you're born on August second today, that person would be fifty, eight years old.

48
00:07:15.510 --> 00:07:29.079
Okay, now that we've computed that we can now determine whether or not an employees Aa or P. Algebra, and I believe that that age is simply fifteen. So I can simply do is take a look here and I can go. He calls the

49
00:07:35.660 --> 00:07:37.670
William Rosener: if d two,

50
00:07:38.020 --> 00:07:41.680
William Rosener: it's greater than or equal to fifteen.

51
00:07:41.910 --> 00:07:43.280
William Rosener: It's, it's, it's it's it's it's it's, it's it's it's, it's, it's it's it's it's it's

52
00:07:43.760 --> 00:07:53.199
William Rosener: now what it. Now the first value is what happens if that's true, i'm going to display. Yes, comma, and if it evaluates to false, i'm going to display No,

53
00:07:53.250 --> 00:07:54.890
William Rosener: so equals

54
00:07:55.160 --> 00:07:59.199
William Rosener: starts your form. Resol for people sign if D two.

55
00:07:59.410 --> 00:08:10.959
William Rosener: And you can actually see at that time that Google Sheets gave me a little hand. Some feedback shown. The cell is referencing, but that evaluation is true. I would display yes, otherwise. No,

56
00:08:13.000 --> 00:08:28.650
William Rosener: and um! You cannot let it go on in, or you can position your cursor on the fill gandal and drive it on down, and we can see at this point we've got only one employee that is over the age of fifty. But again, take your advantage.

57
00:08:28.930 --> 00:08:31.829
William Rosener: I changed this to uh seventy-four. Uh,

58
00:08:31.840 --> 00:08:36.579
William Rosener: then I can see that employees only forty eight and No, they're not algebra.

59
00:08:40.419 --> 00:08:46.609
William Rosener: And why don't you go to when you're creating this? Why, don't you put your own birthday, and they're just gonna help confirm it

60
00:08:47.770 --> 00:08:53.819
William Rosener: next up here is age of the average employee. Ottoman do is average these out.

61
00:08:54.000 --> 00:08:55.310
William Rosener: It was

62
00:08:56.860 --> 00:09:00.400
William Rosener: average, and i'm take A. D two.

63
00:09:00.700 --> 00:09:01.960
William Rosener: Come on

64
00:09:02.170 --> 00:09:03.470
William Rosener: this up.

65
00:09:08.030 --> 00:09:14.619
William Rosener: I can see the average employee would be thirty. Seven point eight, three. Let's maybe see if we can come up here,

66
00:09:15.640 --> 00:09:17.799
William Rosener: and i'm just gonna choose the number

67
00:09:17.960 --> 00:09:19.560
William Rosener: kind of around that,

68
00:09:19.590 --> 00:09:22.840
William Rosener: and then maybe i'll even to highlight these cells here

69
00:09:23.350 --> 00:09:24.720
William Rosener: and guitar centered.

70
00:09:25.290 --> 00:09:34.629
William Rosener: Okay, the age of the youngest employee. If you want to go ahead and stop the video. Challenge yourself There, that's just going to be the main function. I'll go ahead and get you that, anyway.

71
00:09:35.010 --> 00:09:36.630
William Rosener: Same range.

72
00:09:37.800 --> 00:09:39.650
William Rosener: It's her gone.

73
00:09:39.750 --> 00:09:41.210
William Rosener: This happened,

74
00:09:43.150 --> 00:09:46.300
William Rosener: and I can see the youngest employee is twenty two

75
00:09:46.730 --> 00:10:04.709
William Rosener: Sometimes it's just that oppressive, if you only got in this case uh like six employees. But if you got six hundred employees all of a sudden, it's a little bit more tricky to sit there and look through a row. You know column after column or row after row, trying to find the the average or the Angerson's way here.

76
00:10:04.980 --> 00:10:08.359
William Rosener: Okay, the oldest employ that's going to simply be the Max,

77
00:10:10.180 --> 00:10:12.449
William Rosener: and it's going to be that same range.

78
00:10:13.390 --> 00:10:15.379
William Rosener: You, too gone.

79
00:10:15.600 --> 00:10:17.140
William Rosener: They've suffered.

80
00:10:17.960 --> 00:10:29.069
William Rosener: There would be fifty, eight number employees, less than forty, less than forty. Some. It's gonna okay, Less than forty would be one, two, and three.

81
00:10:29.390 --> 00:10:38.749
William Rosener: So what i'm going to do here is i'm going to simply take a look at the age and count those if it's the less than forty. And to do that, i'm going to use a function called count. If

82
00:10:38.860 --> 00:10:46.690
William Rosener: the first parameter is going to be the range, and it's going to be that the exact same range d two colon,

83
00:10:46.820 --> 00:10:48.140
William Rosener: he's A,

84
00:10:49.530 --> 00:10:58.019
William Rosener: then the next parameter is the condition. So the first one is the the range. Second one is the condition, and this can simply be less than forty now.

85
00:11:00.310 --> 00:11:06.990
William Rosener: Okay. And so I've got one, two, three employees less than forty.

86
00:11:07.000 --> 00:11:09.939
William Rosener: But again, watch this. If I change this to um,

87
00:11:09.970 --> 00:11:11.530
William Rosener: make this um

88
00:11:13.530 --> 00:11:14.920
William Rosener: it's for

89
00:11:14.930 --> 00:11:22.349
William Rosener: Well, now I've got one, two, three, four employees, less than fourteen,

90
00:11:23.150 --> 00:11:25.160
William Rosener: you know. Put their bag.

91
00:11:25.420 --> 00:11:39.620
William Rosener: Okay, just to challenge you just a little bit Here I'll let you get the number of employees that are eligible, and you've got two options. One you could count How many of the employees are over fifty or another option is to count the yeses over here

92
00:11:39.630 --> 00:11:47.289
William Rosener: now. I mentioned i'd like to see you do that. See how, if you can figure out how to count how many guesses appear over here,

93
00:11:47.300 --> 00:11:48.190
William Rosener: Sarah,

94
00:11:48.200 --> 00:11:56.979
William Rosener: Anyway, let's Ah, i'll let you figure out that last one, Mr. Challenge a little bit. Here, let's go ahead and a couple of last things it said, Let's go ahead, and I give this the name. I'm just going to call this one

95
00:11:57.160 --> 00:12:00.180
William Rosener: assignment seventeen.

96
00:12:00.470 --> 00:12:18.860
William Rosener: But then probably more importantly, here is some what I would even do if I was you, as I would even come down here and download a copy, and Microsoft excel. Just keep that on your hard drive. Then what I really want you to do is come on down here and you're going to go to file and share and share with others.

97
00:12:20.660 --> 00:12:31.530
William Rosener: Okay. So when I create these videos, I purposely log in as Phil Rosner at Gmail Com. Now I can show you who I wanted to share with,

98
00:12:31.540 --> 00:12:41.420
William Rosener: and that is Rosner at Nashville, Kid. Edu. So what you're going to do is to make sure you're in Rome, sdn er and in the showcase. Edu,

99
00:12:41.620 --> 00:12:45.650
William Rosener: that's who you're going to share it with. Then you're going to press the send a link

100
00:12:45.680 --> 00:12:47.080
William Rosener: and the

101
00:12:47.590 --> 00:12:56.200
William Rosener: or Red send it. Get another email message. Okay. But Now, what you're going to want to do is come right back up here to the file

102
00:12:56.350 --> 00:13:06.109
William Rosener: and share share with others. And there's going to be a Video: I'm: walking through how to upload your work and what you're going to do here is you're going to copy this link.

103
00:13:06.360 --> 00:13:19.149
William Rosener: So it's simple. If I press in the copy link. It's simply copied that into the clipboard. Maybe it's just a yeah url right up here, and then you're going to paste that Yeah, Url: on your websites when I access your work

104
00:13:19.300 --> 00:13:31.530
William Rosener: in effect, rather than looking at a document that you have saved on your own Pc. It's just going to simply point to your file that you've created using on the Google Drive there.

105
00:13:31.900 --> 00:13:46.570
William Rosener: Anyway. Hopefully, in this video you learned a little bit about the work, and more with formulas. In this case, in particular, we were computing the age, which is very common, because never do you really want to simply store the age, because in inevitably you're

106
00:13:46.580 --> 00:14:00.709
William Rosener: the database or your spreadsheet is going to become obsolete pretty quickly as time progresses. We also use it to compute whether Someone's a a Rp. Elgible along with using the average mathematics to in the count of.

107
00:14:00.720 --> 00:14:02.969
Anyway, i'll catch you next video. Thanks. Bye, bye,