CSC exam 3

After looking at the video above, what did you witness happening? First, I created a table that will be in ascending order and will store the "criteria" for earning a certain letter grade. Next, I typed in some random numeric overall grades. Then, I proceeded with using the VLOOKUP() function that will lookup the overall numeric average in the table and return the 2nd column. I used the number 2 for the COLUMN_INDEX_NUMBER because the letter grades were in the second column. The VLOOKUP() function can be used for various other types of scenarios. For example, we could search somebody's salary and look their salary up in a table to see what % they must pay in taxes. We could lookup somebody's height in inches and print out a label depending on if they were very tall, tall, short, or very short.

alculating the monthly payment can be a very helpful tool. I use it all the time. For example, if you do not already have a car, you might need one in the future. Or, you might be looking at a new home purchase. In either case, you will probably look at how to finance it. The PMT() function will accept arguments that will help you determine what the monthly payment will be each month. The PMT() function will calculate constant monthly payments with fixed interest rate. If you were to look at Help for the PMT function, this is what it would tell you about it: =PMT(RATE, NUMBER_PERIODS, PRESENT_VALUE) The PMT() function also has two additional arguments that are outlined with brackets. These are optional and will be omitted for this course. However, be careful when you specify the rate and number of periods because they must be in terms of months. RATE This rate (normally expressed as an annual rate) is the rate of interest one must pay. It must be in terms of the time period you are making payments. In other words, if you are making monthly payments, it should be a monthly interest rate. If you are making quarterly payments, it should be a quarterly rate. NUMBER_PERIODS The number of payments that will be made for the life of the loan. PRESENT_VALUE The principle amount of money that must be paid back on the loan. Usually the loan amount.

he first thing to remember about both the TODAY() and NOW() functions is that they have no arguments. You are still required to use the parenthese, but you type nothing in them. The biggest difference between the two functions is that the TODAY() function will return the date, and the NOW() function will return the date & time. Try both of these functions out by typing in =NOW() and =TODAY() in two different cells. Remember, you may have to change the format up to display the time component.

don't need to necessarily know how they work, but you want to know what each function computes. Function What does it compute AVERAGE Adds up all the values, then divides by how many MIN Finds the smallest value in the list MAX Finds the largest value in the list MEDIAN Finds the value(s) in the middle after sorted When you are using the MIN() and MAX() functions, remember to use context. You can use the MIN() function to find the "coldest" temperature or the "shortest" person depending on the data that is available. The same idea is applied to the MAX() function.

The Fill Handle is located in the bottom right corner of the active cell. Remember that the active cell is the one(s) you are currently using. The fill handle is a tool that can copy your cell contents down, up, left, or right a number of cells. Also, a fill handle can create a Fill Series. There are two types of fill series - numeric series and Days of the Week/Months of the Year series. Watch the video below as we discover the Fill Handle and how useful it can be. The fill handle performs a "smart copy". Depending on the types of references used (discussed below), the fill handle will copy your formula or function to the appropriate cell. Also, notice how if you use the fill handle to copy the number 1 down, it will simply copy it. However, if you select the 1 and 2 and copy down, it will create a series for you. Days of the Week (Monday, etc.) and Months of the Year (January, etc.) you only need to select one cell for the fill series to work.

Cell References or Cell Addresses refer to a cell. If these cells are being used in a formula, what happens if you use the Fill Handle or copy the formula to another cell? This would depend on what type of cell references you are using. This paragraph will only talk about 2 of the 4 types of references: Absolute and Relative. An Absolute Cell Reference will always refer to that cell. Absolute References are denoted by a dollar sign ($) in front of the Column Letter and Row Number. A Relative Reference is the default reference where there are no dollar signs at all. Watch the video below and pay special attention to how the cell references are updated as you use the fill handle to copy the formula down. Notice how when we tried to divide =E6/E5 and used the fill handle to copy it down, it updated both cell references E6 and E5 accordingly. This is what happens with Relative Cell References. When we copied =E6/E5 down one row, the E6 changed to E7 and E5 changed to E6. So, the new formula was =E7/E6. This is not what we wanted. We wanted the E5 to always, absolutely stay E5 no matter what. This is an example of why we use absolute references. Notice how we made E5 absolute by typing in $E$5. This means that $E$5 will never change. I would highly encourage you to play around with formulas on your own to see what would the result formula be if the cell were to be copied down/up X number of rows or left/right Y number of columns. Always remember, to increase columns (letters), you move RIGHT, to decrease columns(letters) you move LEFT, to increase rows you move DOWN, and to decrease rows you move UP. Take a look at the table below for some examples.

A Style is a pre-defined set of formats. Microsoft Excel (just like Microsoft Word) allows you to quickly and easily use pre-created formats in your spreadsheet. The image below will highlight some of the styles Microsoft Excel allows you to use.

Have you ever looked at a table full of values and thought to yourself - What's the point of all this data? Well, normally it is hard to see the big picture of values when there are so many. This is where charts come into play. A Chart is a graphical representation of your data. There are several different types of charts. Each type of chart has its special purpose, but all of them help the reader understand the values. Watch the following video as it shows you how to chart some basic data in Microsoft Excel. Remember that the chart can be resized and moved around. To allow the Chart to be "snapped" into a cell, be sure to use the ALT key when dragging the chart. Make sure you have the entire chart selected when you do this. One way to notice that the entire chart is selected is by looking at the Sizing Handles. The sizing handles are the little boxes that surround the chart, image, or graphic. They indicate that the particular object has been selected.

Now that we know what Absolute and Relative Cell References look like and how they work, let us take a look at the last two types of cell references. These are called Mixed Cell References. More specifically, they are Row Absolute Cell References and Column Absolute Cell References. Let us investigate the visual difference between these. Relative A3 The Row and Column could change. Absolute $A$3 Neither Column nor row will change. Always stays the same. Row Absolute (Mixed) A$3 Only the Row will stay the same. Only the Column could change. Column Absolute (Mixed) $A3 Only the Column will stay the same. Only the Row could change. Mixed References are used in a very specific domain when you only want EITHER the row OR column to change, but never both. The most common example of this is a multiplication table. Let us take a look at the video below to see what happens when you use mixed references in a Multiplication Table.

One of the most challenging ideas to learn when coming to dates is actually quite simple. A date is simply a number. A date is a format. Excel's date format will take a simple number and change it to look like a date. A date is represented by the number of elapsed days since 1/1/1900. Why January 1, 1900? Who cares! It's what Microsoft Excel in it's greatest intellect decided to do - we just have to use it! Watch the video below as we play around with some dates in Excel. It is quite common to know how many of such items you have in a worksheet. Whether it's knowing how many values, people, cities, temperatures, etc., counting these cells is sometimes necessary. Let us look at the different ways of counting cells. Functions What does it count? Example COUNT Counts the number of cells with a number in it =COUNT(A1:A50) COUNTA Counts the number of cells that are non-empty =COUNTA(A1:A50) COUNTIF Counts the number of cells that meet a condition =COUNTIF(A1:A15, "COOL") Below is a video that will further explain the differences between each of the preceding functions.

Although CSC101 is not a math class, it is an applied math class. This means that we will not necessarily compute formulas and equations for the mere purpose of learning them, but we will need them to make calculations. Remember back from your first Algebra class the acronym PEMDAS that you learned. Please Excuse My Dear Aunt Sally.

One of the most valuable & widely used functions in Excel is the IF() function. The IF() function is the first function where you want to know the function layout. =IF(logical_test, value_if_true, value_if_false) Consider an example where we are trying to check a person's age with a legal voting age. Remember that the IF() function could be written out in 4 different ways. Assume cell C4 has a person's age for the following expressions: =IF(C4>=18, "Yes", "No") =IF(C4<18, "No", "Yes") =IF(18<=C4, "Yes", "No") =IF(18>C4, "No", "Yes") The above IF() function examples are all correct in testing whether a person's age is higher than 18 in order to vote. Honestly, I pesonally think the first one is easier to understand, but they are each correct.

Conditional Formatting allows the user to apply format to certain cells if they meet a certain condition. For example, let's say we have a list of 10 numbers in 10 different cells. We could conditionally format all these cells and have all numbers with a value less than 30 have a red background. The red background would only be applied to those cells that meet the condition. Another cool thing about using Conditional Formatting is that the formats are automatically updated when the values in the cells are changed. This way we only need to apply a conditional format to a cell once. Remember, it is possible to have more than 1 rule in any cell. For example, if we had a cell with a temperature that is updated every hour. We could have 3 different rules to have a background color change for how hot or cold it is. So, we could have one rule that is if the temperature is below 32, then make it a blue background. If the temperature is greater than or equal to 32 and less than 90, then make it a green background. If the temperature is greater than or equal to 90, then make it a red background. This way, the color could give us an indication of how hot it is.

The VLOOKUP() Function is a very practical function. Let us consider a scenario where you are the professor of a college course. At the end of the semester, you will calculate the overall average for all of your students (and it will be a numeric average). However, when you formally submit the overall grades to the registrar, they must be a letter grade. Well, if you had around 400 students, this could take a significant amount of time if you have to manually lookup each grade. That being said, the VLOOKUP() function can definitely save us some time! Before we start to look at how the VLOOKUP() function works, let us look at the arguments. If you were to look at Help for the VLOOKUP function, this is what it would tell you about it: =VLOOKUP(SEARCH_ARGUMENT, TABLE_RANGE, COLUMN_INDEX_NUMBER) The VLOOKUP() function will search the SEARCH_ARGUMENT in the leftmost column of the table in the TABLE_RANGE. Once a match has been found, it will return the corresponding value in the column number that you specify. SEARCH_ARGUMENT The SEARCH_ARGUMENT is the value in which you are looking up. Let us consider the example where we are the professor, and we need to look up an 88.3% and get a 'B' for the course grade. The 88.3% in this case would be the SEARCH_ARGUMENT. The SEARCH_ARGUMENT is our "input" in the function. It is the value that we will take and use for determining what we will get out of the table. TABLE_RANGE The TABLE_RANGE argument specified the range of cells in which your table (not including the labels or headings) is located. Where is your table? B8:C14? Z14:AA20? You must specify this as the second argument in the function. Most of the time, the cell references for the TABLE_RANGE are absolute (because you want to copy the function, but the table remains in the same location). COLUMN_INDEX_NUMBER The COLUMN_INDEX_NUMBER is the argument that is the trickiest. Columns are usually identified by letters, but this is asking for a number. What column in your table contains the answer you are looking for? Is it in the first column, second column, third column, fourth column, etc? You simply put a number in this argument. Remember, you are searching in the first column...so the resulting value will be in one of the remaining columns. Some other things to consider regarding the VLOOKUP() function is to make sure that your table is in Ascending order. That means, the values in the leftmost column must get larger as you look down the spreadsheet. Let's look at an example.

he first function we will learn will be the SUM function. The SUM function (not to be mistaken with the fictional SOME function) is a simple function that will add up all the arguments. An argument is any piece of information you use with a function. Take a look at the following examples as we will discuss them. =SUM(1,2,3,4,5) =SUM(A1,F8,B11,H4,A66) =SUM(1,2,A6,N12) =SUM(1,2,3,SUM(9,8)) The first example listed above would add up the values 1, 2, 3, 4, 5 and would output 15 as the answer. The 2nd and 3rd functions listed above would have an output depending on what values were in the cells being referenced. Notice that the 3rd function combines cell references with actual values. The last SUM function example listed above shows how you can have another function as an argument. This example would produce 23 as the output. Notice how we can have 3 different types of arguments: constant values, cell references, or functions. Often with functions, arguments are listed using Range Notation. Range Notation requires that you specify the Top Left Cell:Bottom Right Cell. For example, A1:A10, F3:G17, C9:D10 are all examples of range notations. A1:A10 means A1 through A10. This also means A1, A2, A3, A4, A5, A6, A7, A8, A9, and A10. (Not just A1 and A10, but A1 through A10). Watch the following video as I demonstrate how to use Autosum to automatically sum up values for you. Also, watch how I use the Range Finder to click on cells to be used to add up values. If you use a formula, function, or format that outputs a value that is too wide to fit in the cell, it will display a series of ######## signs (Pound Signs). The easy fix for this is to re-adjust the column width for that column.

The two types of formatting you can use for dollar amounts are Accounting and Currency. Take a look at the table below and see the similarities and differences between Accounting and Currency formats. Accounting Currency Dollar Sign Fixed Dollar Sign to the Left Edge of the cell Floating Dollar Sign (can move depending on size of value) Thousands Separator Comma placed every 3 digits to the left of the decimal Same Negative Values Parentheses around the value Negative sign (-) on the left hand side of the $ sign Decimal Point Two decimal places to the right of the decimal Same

Column and Row Headings allow a user to manipulate them. For example, you can Insert or Delete a row or column before or after whichever one you wish. Watch the following video as I highlight some of the basics with data entry and row/column insertion/deletion.

There are 4 modes in Excel. Knowing which mode you are in can help you complete your desired task more efficiently. These modes are automatically enabled depending on what you are doing. Ready Not currently "in" a cell, but have a cell active Enter Enabled when you are entering text into an empty cell Edit Enabled when you are entering text into a non-empty cell Point Enabled when you are using the Range Finder with charts and functions Next, we will look at shortcuts. Remember that shortcuts can save you tons of time when doing the same thing over and over. These shortcut keys are similar to the shortcut keys in Microsoft Word, but they depend on which mode you are in! Home Moves to beginning of a row in a worksheet Moves insertion point to beginning of the cell End Moves to end of a row in a worksheet Moves insertion point to end of the cell CTRL + Home Moves to beginning of worksheet Moves insertion point to beginning of cell CTRL + End Moves to the last cell of a worksheet Moves insertion point to end of cell

Start Microsoft Excel and take a look around at the Workbook. When you save a file in Microsoft Excel, the file extension will be .xlsx. Also, remember that this file is called a workbook. Inside each workbook, you can have one or more worksheets. You can quickly tell how many worksheets are in a workbook by look at the sheet tabs in the bottom left area of the window. Notice how the different columns and rows are labeled. Columns are the vertical set of cells that are identified by letters (column headings). Rows are the horizontal set of cells that are identified by numbers (row headings). The letters increase as you move towards the right, and the numbers increase as you move downward. (This will be important later on.) A cell is the intersection of a row and column. An active cell is identified by the column letter and the row number. For example, A5 or F9. A5 and F9 are examples of a cell reference or cell address.There are 3 ways to tell if a cell is active: Black border around the cell Row and Column Headings are highlighted Cell address is in the Name Box The image below has cell C2 as the active cell. When you type labels or values in a cell, press the Enter key (or click on the Enter Box to complete the entry. If you want to get back to ready mode, simply press the ESC key or click the Cancel Box.

There are also a couple of views that you will want to be familiar with. Normal View and Page Layout View are the two common views used in Excel. The biggest difference between these two views is that Page Layout view allows the user to see the margins (which includes the Headers/Footers), visible page break, and ruler. When it comes to inserting something in a cell, you can only insert Text, Numbers, Formulas, and Functions. Take a look at the following examples of each: Item Example Text (default Left Alignment) Texas, Dog, Five, 123 Main St., Super man, © Numbers (default Right Alignment) 145667, 1, 99, 3, 0, 3.125 Formulas =2+3, =A1+B3, =A16/12 Functions =sum(A1:A10), =if(A6>A7, "Yes","No") Formulas and functions must begin with an = sign and can be used with each other in a cell.