Transcript
100:00:00,000 --> 00:00:05,520so this is module 3 project 1A so in
200:00:02,820 --> 00:00:07,560this Step 1 it is saying switch to the
300:00:05,520 --> 00:00:10,679sales projects and worksheet use the
400:00:07,560 --> 00:00:12,840value in the range B6 through C6 to
500:00:10,679 --> 00:00:17,039extend the incremental percentage to
600:00:12,840 --> 00:00:19,380range D6 through S6 so from
700:00:17,039 --> 00:00:21,660B6 through C6
800:00:19,380 --> 00:00:24,480going to use this so go to the corner
900:00:21,660 --> 00:00:28,500and this plus sign will appear now hold
1000:00:24,480 --> 00:00:29,460it and drag through D6 through F6 so up
1100:00:28,500 --> 00:00:32,279to here
1200:00:29,460 --> 00:00:35,520so this this this plus sign will appear
1300:00:32,279 --> 00:00:38,219hold it and drag it until S6
1400:00:35,520 --> 00:00:41,579so it will auto fill after the releasing
1500:00:38,219 --> 00:00:45,480use the value in the range A7 through A8
1600:00:41,579 --> 00:00:47,460so is seven through eight and to extend
1700:00:45,480 --> 00:00:52,739the list of the year in the range A9
1800:00:47,460 --> 00:00:55,440through a11 so A9 through 11.
1900:00:52,739 --> 00:00:57,120same method just just go to the corner
2000:00:55,440 --> 00:01:01,320this type of plus sign will appear and
2100:00:57,120 --> 00:01:01,320hold it and drag it until 11.
2200:01:01,620 --> 00:01:07,320so after that use autofill to fill the
2300:01:04,500 --> 00:01:09,900range a 9 through S11 with the
2400:01:07,320 --> 00:01:11,040formatting from the range A7 through
2500:01:09,900 --> 00:01:13,200acid
2600:01:11,040 --> 00:01:15,960so A7
2700:01:13,200 --> 00:01:18,360now hold shift and then click here
2800:01:15,960 --> 00:01:20,820and it will select all and it is saying
2900:01:18,360 --> 00:01:24,720use this to fill the range A9 through
3000:01:20,820 --> 00:01:26,040h11 so A9 3x11 same process just go to
3100:01:24,720 --> 00:01:29,880the government plus sign will appear
3200:01:26,040 --> 00:01:32,939hold it and drag it until S11
3300:01:29,880 --> 00:01:34,680and then release it after that in cell
3400:01:32,939 --> 00:01:37,079B8
3500:01:34,680 --> 00:01:39,060initial we'll create a formula using a
3600:01:37,079 --> 00:01:42,960function that multiplies the value in
3700:01:39,060 --> 00:01:44,880cell B7 by the value in cell B6
3800:01:42,960 --> 00:01:48,500so
3900:01:44,880 --> 00:01:52,220equals to sign after that V7
4000:01:48,500 --> 00:01:52,220by B6
4100:01:52,680 --> 00:01:57,619and then adds the value in B7
4200:01:58,079 --> 00:02:02,700okay
4300:01:59,700 --> 00:02:04,159V7 in this formula use the mixed
4400:02:02,700 --> 00:02:07,380reference for
4500:02:04,159 --> 00:02:09,000B6 by including an absolute reference to
4600:02:07,380 --> 00:02:12,599row six
4700:02:09,000 --> 00:02:15,420so for B6 going to do the absolute
4800:02:12,599 --> 00:02:17,400reference for row 6 so just for row 6
4900:02:15,420 --> 00:02:18,660into 100 dollars and it will stay
5000:02:17,400 --> 00:02:20,420constant
5100:02:18,660 --> 00:02:23,340and then
5200:02:20,420 --> 00:02:24,720row six okay so this is done and hit
5300:02:23,340 --> 00:02:27,300enter
5400:02:24,720 --> 00:02:29,760after that in this formula so this is
5500:02:27,300 --> 00:02:32,580done complete the formula from copy the
5600:02:29,760 --> 00:02:36,480formula from p8 to the range B9 through
5700:02:32,580 --> 00:02:39,599b11 so click on V8 again plus PSI plus
5800:02:36,480 --> 00:02:43,019sign in B 9 through v11
5900:02:39,599 --> 00:02:44,940after that it is saying and then copy
6000:02:43,019 --> 00:02:49,920the formula from the range B8 through
6100:02:44,940 --> 00:02:52,379b11 to the range C8 through h11 so
6200:02:49,920 --> 00:02:53,959go to the kernel plus signal up here and
6300:02:52,379 --> 00:02:57,180then drag it until
6400:02:53,959 --> 00:02:58,980c83 has 11 so until here
6500:02:57,180 --> 00:03:01,620now it will auto fill out the value
6600:02:58,980 --> 00:03:03,840switch to the cost estimates worksheet
6700:03:01,620 --> 00:03:07,800so let's do it
6800:03:03,840 --> 00:03:10,019and then in cell A9 so A9
6900:03:07,800 --> 00:03:11,700create a formula using average function
7000:03:10,019 --> 00:03:14,940that calculates
7100:03:11,700 --> 00:03:16,680so every time we enter formula or any
7200:03:14,940 --> 00:03:20,099mathematical calculation we shall start
7300:03:16,680 --> 00:03:23,940with equals to sign and then enter
7400:03:20,099 --> 00:03:26,340average so this is case insensitive so
7500:03:23,940 --> 00:03:28,080it doesn't matter if we type in capital
7600:03:26,340 --> 00:03:30,840case or lowercase
7700:03:28,080 --> 00:03:33,959or anything and then that calculates the
7800:03:30,840 --> 00:03:37,920average value in the range A5 through A7
7900:03:33,959 --> 00:03:41,580so just enter the range A5 through A7
8000:03:37,920 --> 00:03:44,760and then hit enter and then copy your
8100:03:41,580 --> 00:03:49,680formula to cell D9
8200:03:44,760 --> 00:03:51,720so just copy it Ctrl C and then Ctrl V
8300:03:49,680 --> 00:03:55,560and then hit Escape
8400:03:51,720 --> 00:03:58,980after that in cell 18 create a formula
8500:03:55,560 --> 00:04:01,739using the max function so cell a 10
8600:03:58,980 --> 00:04:04,739again go to here equals to sign
8700:04:01,739 --> 00:04:07,920Max function and it accepts
8800:04:04,739 --> 00:04:11,840uh which number so that identify maximum
8900:04:07,920 --> 00:04:16,799value in the range a 5 through A7
9000:04:11,840 --> 00:04:18,720formula to d11 so just copy it and then
9100:04:16,799 --> 00:04:22,019paste it
9200:04:18,720 --> 00:04:24,960after that in cell a11 create a formula
9300:04:22,019 --> 00:04:26,100using a mean function so again win
9400:04:24,960 --> 00:04:30,300function
9500:04:26,100 --> 00:04:32,100that identify A5 through A7
9600:04:30,300 --> 00:04:36,440same thing
9700:04:32,100 --> 00:04:36,440so copies and then paste it
9800:04:36,900 --> 00:04:42,180so after that it is saying in cell B13
9900:04:40,020 --> 00:04:43,199create a formula using the vlookup
10000:04:42,180 --> 00:04:47,940function
10100:04:43,199 --> 00:04:50,940okay so peer lookup function that looks
10200:04:47,940 --> 00:04:55,860up the value from cell a11 so lookup
10300:04:50,940 --> 00:04:59,280value is 11 and then a 5 through B7 and
10400:04:55,860 --> 00:05:02,100then in column 2 that is specify and
10500:04:59,280 --> 00:05:05,280specifies an exact match
10600:05:02,100 --> 00:05:06,300is intervals and then close the
10700:05:05,280 --> 00:05:08,220parenthesis
10800:05:06,300 --> 00:05:11,639and then hit enter
10900:05:08,220 --> 00:05:15,440and then copy the formula to cell e13 so
11000:05:11,639 --> 00:05:15,440just copy it and then paste it here
