Excel Chapter 3 Video 2


Uploaded by CISatOTC on 08.07.2011

Transcript:
In this video, we will learn how to show and format the system date, and the more difficult
tasks of creating an If function and using absolute cell referencing.
Let’s use the same worksheet that we were using in the previous video. The only changes
I have made, is I have inserted a row between row 2 and three, I’ve typed “Sheet updated”
and “quota” in cells A1 and A2 and have typed the value 1,500 in cell B2.
First let’s look at showing and formatting the system date. I am going to do this in
cell B1. The system date can serve as a date stamp. This is placed in a worksheet to show
when the spreadsheet was updated or prepared. Yes, you could just type that date in, but
using a function that displays the date will automatically update every time the workbook
is opened. To place a date stamp in this worksheet we
will use the “Now” function. Start by left clicking on cell B1 to make it your active
cell. Next click the insert function button located in the formula bar. The insert function
button dialogue box should open. Select the category “Date & Time”. Scroll down until
you find the “Now” option. Select it, then click ok.
This function by default shows the date and the time. We really only need the date, so
let’s reformat this by using the format menu in the number grouping on the home ribbon.
Choose short date. Make sure that cell B1 was your active cell before doing this.
Now let’s look at creating an If function and using absolute cell referencing. In the
“Met Quota” column, I want the word “yes” to appear if the item met the sales quota
of 1,500 listed in cell B2 and I want the word “no” to appear if it did not. That
would take forever if I did this manually. There is a function that exists in Excel that
can display numbers or messages based on a logical test. It is called the If function.
The IF function is mentioned on page Ex 164 in your book. It works by doing a logical
test that is a true or false test. If the test comes out true, the function will do
one thing, if it comes out false, the function will do another. We get to decide what the
logical test is, as well as the results for the function if the logic test comes out true
or false. Start by making cell C6 your active cell.
Next use the insert function button to search for the IF function in the insert function
dialog box. Select the category “logical” then select “IF”. Press ok. The function
arguments dialog box will help us create the IF function. Move the dialog box to the right
so you can see columns B and C. Put your cursor in the logical text field here. Single left
click on cell B6 in your worksheet and then type >=B2. Place your cursor in the value
if true field and type “yes”. Place your cursor in the value if false field and type
“no”. Now press ok. As you can see, the function placed the word
“no” in cell B6. Now let’s try to use our fill handle to copy the formula down like
you learned in chapter 1. The formula did copy. However, some of these are incorrect.
Take cell C8 for example. It should say “yes” because it sold over 1500 units but it says
“no”. Let’s take a closer look at the function to determine the problem. Just like
the sum function adjusted in Chapter 1, the cell references in this if function also adjusted.
This IS what we wanted for the B6 cell reference to the total # sold column, but the cell reference
to B2 has adjusted to B4 and that is not what we want. We want this if function to be comparing
the total # sold to the value in cell B2 every time. We can solve this problem by using Absolute
Cell referencing. Absolute Cell referencing allows you to determine
what cell references you want to adjust and which ones you don’t want to adjust when
copying the functionCould you just retype the function? Sure, but think about how long
that would take for all of these Met Quota columns. Instead of adjusting each function,
we will go back to our original function by clicking on cell C6 and make the Cell reference
B2 an absolute cell reference, which is a cell reference that does not adjust when copied.
Do this by placing your cursor in the function as I have just done, anywhere next to the
cell reference B2 and pressing the F4 button at the top of your keyboard. Notice this places
dollar signs in front of the B and the 2. A dollar sign in front of the row number or
column letter in a cell reference makes it an absolute reference and tells Excel not
to adjust the reference when copied. You can also simply type the dollar sign in front
of the row number and column letter. Now we hit enter to save our function changes.
By clicking back on cell C6 and using the fill handle once again, the function copies
and we get the correct results. Copying the function also copied the borders, which is
why it is suggested that you don’t put your borders in until the end. I will simply fix
this by removing all borders in my selected area and reapplying an outside border.
With this same area selected, I can copy and paste into columns E G I K M O and Q. By using
absolute cell references in conjunction with our relative references, we quickly created
a function in several large columns.6C