Excel class notes control f is your friend PDF

Preview

Summary

This will help you with Excel basics...

Description

Getting Start Excel myeducator CHAPTER 1 1 S

1 EXCEL 2013  3 things o File itself (on excel) = workbook o Workbook is divided into one or more worksheets o Worksheet (sheet 1): 2 D table that contains spreadsheet cells.

 Rows = sequentially numbered  Columns = letters  The "Page Layout" menu items are used to format the appearance of worksheets.  The "Formulas" menu items aid in performing calculations using Excel’s built-in functions.  Items in the "Data" area are used to aid in importing and working with large amounts of data.  The "Review" menu items help in sharing an Excel workbook with colleagues.  The "View" menu items are used to change how a worksheet is displayed on the screen.  The Excel menu area is also customizable; other menu tabs, such as the "Developer" or "Add-Ins" tabs, can be added to the Excel menu as you begin to use more of the features included in these groups. 1 THE FILE MENU  Manipulates the entire workbook  ? = excel help o Merge and center? → gives internet articles that help 1 THE CELL ADDRESSING SCHEME  Worksheet = full of cells  Cells defined by number and letter 1 ENTERING AND MANIPULATING DATA  Select cell and type = enter data  Dancing line around a cell = you have copied what is stored there o Highlight where you want to paste  Have a pattern, but don’t want to type every single #... go to home→ editing tab → click fill icon→ click series…→ o Step value (how much goes up each successive time) o Stop value (what value ends on)  Or click autofill, excel can figure out the pattern of the info 1 WORKING WITH ROWS AND COLUMNS  Add a row: Highlight row immediately below where you want to insert row o home→ insert→ insert sheet rows

o Or highlight and right click to do the same thing  Delete? o Click delete icon instead of insert  Double click on cell = see entire content of cell o Resize by going to column boundary (A) options:  Move it  Or double click it  Highlight it→ format→ choose size  Hide info? o Right click column or row and click hide -or- click format and then hide/unhide… o User has clues that info is being hidden: double line and name of column/row is missing  Want to see the hidden info? Highlight the boundary columns (the ones that the hidden one is “squashed” between)--> right click→ select unhide

 Data too much (company 8, date, o Freeze panes of the wksht  To do that highlight cell direct below and to the right of where I want to freeze panes→ click view→ click freeze panes 1 ADDING AND DELETING WORKSHEETS  Home→ insert→ insert sheet or do right click on sheet name at bottom to see options 1 PRACTICE PROBLEMS  Assignment o Click instruction sheet if don’t know what to do… o Or click task guide to jump in…  Cut and paste (make old info go away after paste… do not need to press copy at all?...)  format→ column width → 20 (cm)  Freeze panes

o Highlight row below row you want frozen o View menu-- window group-- freeze panes  When inserting a column, it will insert to the left of the one you highlight 1 1. 2. 3. 4. 5.

CHAPTER 1 3.3 FORMATTING CELLS AND SHEETS Apply basic formatting (bolding, italicizing, centering) Define the data type of cells Specify the background color, patterns, and outlines of cells Use the format painter to copy the format of a cell and apply it to other cells in your worksheet Define the page margins and print areas for a worksheet 1

   

 

 

BASIC FORMATTING OF CELLS Arrow on the font menu items -- line through etc… #### = info in cell = exceeds space of cell o Can just double click to double the width… Align centered vertically or horizontally… o Can angle text Span a title over two columns = highlight both cells and then click merge and center option o -or- wrap text→ makes it so info stays in the margins of cells, going down more spaces within the cell if margin = too narrow/if necessary More alignment options…. 1 COLORS, PATTERNS, AND OUTLINES Color o System automatically keeps last color you used as go to one…  Can do more unique colors by clicking further options under color Pattern: changes the cell background pattern o Expand the menu (little arrow in the corner) → go over to the fill tab → change color pattern/style Border o Border drop down menu = right next to underline option on home o Select area→ select type border want





 



 Or expand menu-- click border-- be more specific in how they are formatted 1 DEFINING DATA TYPES The go to format is general o Below that are options to add commas, make currency or percentage display, or add or take away decimals o Click corner arrow to control more unique things, under number tab… 1 REUSING AND CLEARING CELL FORMATS Reuse same set of formatting option from one cell to other? o Highlight cell from which you want to copy the format→ o Format Painter (under cut and copy, to the R side of paste) o Then select the cell where you want to apply the format now EZ clear format→ highlight cell you want to clear→ click clear (under fill) and clear formats… Short date = 05/13/1990 o Or next to wrap text (home) pull down menu offers short date option 1 FORMATTING WORKSHEETS Don't often print… but when you do→ page layout o Margins-- how far away from edges…

 Repeat row at top every page or column at side of every page? o Click print titles icon→ print setup window→ select rows/columns… o Headers and footers tap… o Can customize…  Print only certain area →highlight where→ print area-- set print area 1 SORTING AND FILTERING  Sorting and filtering in Excel allows you to reorganize and limit the items in a table to help you make sense of the table. 1 SORTING ITEMS IN A TABLE  Data tab

o Ascending order? → A to Z tab. (select letter column first)  Or click sort button  Can do more advanced things here… does the last one you pick  categories→ groups (e.g. industry-- do sorting within that --sort→ add level → largest to smallest… 1 USING THE FILTER FEATURE  Filter records out of a table… o Some records shown and hidden o Select in data-- click filter-- click which you want to show…  Can clear (clear next to filter) o Click drop down box by e.g. stock price (should be showing after clicking filter) (e.g. show companies with stock price less than $100)--> then you go to number filter-- choose less than option option… window comes up… show values in a range for stock price 1 PRACTICE PROBLEMS  1. Select any cell in column o Data - A to Z↓ (ascending order)  2. Select cell in range of table - Filter - drop down, unselect “m” to show only “f” Random tip: Check to know if internet is working? Search terminal then type ping 8.8.8.8 and then it will show you if working (its like calling google chrome)

CHAPTER 2 2    

4.1 REFERENCES AND CALCULATIONS Absolute vs relative cell references Calculations in excel = always start with the = sign o Hit enter =(B3+B4+B5)/3 = average Reuse formulas o Copy that cell (don’t have to see formula to do that)--- select paste  Reletters formula automatically to match new column

 When I use relative cell reference… anytime I reuse a relative cell reference by copying and pasting into new cell where I want to apply it-- updates row and column depending on how you copy and paste…  This is the default

* constant/absolute:$A1 = column; row reference = A$1 (no automatic increment

2 2.1 PRACTICE PROBLEMS: REFERENCES AND CALCULATIONS  $ in front of what you want to be fixed/stay same o =c12*c8 o =c12*c$8(copying from row to row) = fixed  Relative = automatically done on excel  Regular price - discount sale price 2

4.2 SUMMARY STATISTICS FUNCTIONS

Can look for functions too… Argument = info function needs *=SUM( *arguments are separated by a comma *if it is a range use :  Count function tells us how many values are in the range (cells filled with numbers) (only counts #s)  Count A cell counts any non-blank cells  =average(range)  =max(  =min(  Function argument window = function wizard o Lets you enter inputs (arguments) o Gives a description of the arg and function o Lists the result based on input entered  “Calculate the average racer age (using the AutoSum feature in Excel) in cell C34. Notice that cell C34 is directly below the Age range (C9:C33). Because cell C34 is below the Age range, you can use the AutoSum feature to have Excel automatically insert the range argument into the AVERAGE function.”  AutoSum o Under home o Select average-- automatically knows the average 2 4.3 FINANCIAL FUNCTIONS  Common financial functions (dollar today not same as dollar tomorrow)

       



o PMT = payment amount  (rate, nper, pv, [fv], [type]) o PV= present value (amount/value earning today)  (rate, nper, pmt, [fv], [type]) o FV: future value (residual value other than 0)  (rate, nper, pmt, [pv], [type]) o RATE= monthly rate (interest)  (nper, pmt, pv, [fv], [type], [guess]) o NPER= number of payments for the loan  (rate, pmt, pv, [fv], [type]) o EFFECT= how much loan truly costs us (annual rate is what loan officers always tell us; annual rate is actually slightly less than what effective rate that we are going to pay on our money because of the principle of compounding interest (every month make a little more interest)(make more interest based on the amount of interest earned in the prior month… ))  (nominal_rate, npery)  Nominal rate = annual rate  Npery = how many times pay interest (monthly =12) Need the 3 of the 4 below Loan amount Interest Rate Number Payments Payment Amount Make interest amt (always annual… /12) Optional argument = brackets around it [ ] Why payment amount negative o Money you need to pay = negative o Loan and payment will always be opposite sign) “Each of the functions described above is based on the notion of the time value of money, or compounding interest. The time value of money recognizes that a dollar earned today is worth more than a dollar earned next year. The reason is that I can earn a year’s worth of interest on the money I earn today. While this concept is pretty intuitive, most people do not know how to make some of the calculations necessary to apply the time value of money. For example, there is a perfect relationship between the person’s mortgage payment, the length (or term) of

their loan, the interest rate they pay on their loan, and the amount of money they borrowed when they purchased their home. If I know any three of these, I can calculate the fourth. However, many people do not know how to calculate their own mortgage payment when they buy a house even if they know the asking price of the home they wish to buy, the interest rate they will be changed on their loan, and the number of months they will take to pay the loan back.” 8888 2 4.4 FORMULA AUDITING  Trace Precedents  Trace Dependents  Showing our transactions  Under formulas tab→ on the right side where it says formula auditing→ above that click trace precedents 8888 o Can click remove arrows as well  Which cells are referenced in other cells = tracing dependents… one number wrong… how many other cells affected by that  See all formulas at the same time? o Go to formula auditing group and click where it says: show formulas

Lesson 3 Logic and Reference Functions 3      

5.1 BOOLEAN FUNCTIONS Boolean logic: construct a statement that can have only 2 logical outcomes (can be true or false) 50 =A1 = 5 o False =A1 =50 o True =A1 >5 o True Does sum = average… e.g.

 Built in boolean = “and”T “or”F  =and (sum(A1:A3)=6,sum(B1:B3)=6) o And = both have to be true→ true; if any or all false→ false  or→ if any argument is what true… o If all false than “or” function will show false  And and or let us combine functions  “Not” function→ flips result o Sometimes easier to define what things are not… 3 PRACTICE PROBLEMS  Credit score = ok if not less than threshold o Not (C12=5, “between 5 and 10”, “less than 5”)) o Red parenthesis is not a problem o Nesting IF function...  Rule: you always need 1 fewer if statement than possible outcomes: 3 possible outcomes = you need 2 if statements

3 “NESTING” IF FUNCTIONS

3 PRACTICE PROBLEMS 1. Calculate "PMT OK?" in cell I12 using an IF function that returns "Yes" if the "PMT/Income" ratio in cell E12 is below 43% and "No" otherwise. Copy your formula down to complete the "PMT OK?" column. 1. If (E12=) the "Threshold Credit Score" in cell F9 otherwise return "No." Copy your function down to complete the "Score OK?" column on the table. Be sure to appropriately use relative and absolute cell references. 1. If (C12>=F9, “yes”, “no”) b. Calculate "Down OK?" in cell K12 using an IF function that returns "Yes" if the "Down Payment" in cell F12 exceeds 10% of the "Loan Amount" in cell D12. The function will also return "Yes" if the "Down Payment" exceeds 5% of the "Loan Amount" and the "PMT/Income" ratio (cell E12) is less than 40%. Otherwise, the IF function should return "No." Copy your formula down to complete the "Down OK?" column. 1. =IF(F12>0.1*D12, "YES", IF(AND(F12>0.05*D12, E12 $A$1 --> A$1-- > $A1-- > A1  By highlighting your range of cells and then pressing Fn and F4 key to get to Absolute reference  Hold Fn down and then press F4 until you have toggled to the option you want 3 PRACTICE PROBLEMS  Calculate the "# of Transactions" (cell G9) using the COUNTIF function. The range for the function should reference the values in the "Location" column on the "Transaction Details" table (range C9:C73) and the criteria should reference the appropriate "Location" value on the "Sales Summary" table (cell F9). Copy your formula down to complete the "# of Transactions" column. Be careful to use the correct relative and absolute cell references. o =COUNTIF($C$9:$C$73, F9)  Calculate "Total Sales" (cell H9) using the SUMIF function. The range for the function should reference the "Location" values on the "Transaction Details" table (range C9:C73), the criteria should reference the appropriate "Location" value on the "Sales Summary" table (cell F9), and the sum_range should reference the "Sale Amount" values on the "Transaction Details" table (range D9:D73). Copy your formula down to complete the "Total Sales" column. Be careful to use the correct relative and absolute cell references. o =SUMIF($C$9:$C$73, F9, $D$9:$D$73)  Calculate "Average Sales" (cell I9) using the AVERAGEIF function. The range for the function should reference the "Location" values on the "Transaction Details" table (range C9:C73), the criteria should reference the appropriate "Location" value on the "Sales Summary" table (cell F9), and the average_range should reference the "Sale Amount" values on the "Transaction Details" table (range D9:D73). Copy your formula down to complete the "Average Sales" column. Be careful to use the correct relative and absolute cell references. o =AVERAGEIF($C$9:$C$73, F9, $D$9:$D$73)... 3 5.4 REFERENCE FUNCTIONS  Vlookup and hlookup

o Need to use same value over and over again, but don’t want to have to remember it/keep looking it up  Reference table…  Vlookup match range to label in our reference table o Don’t include table headings-- emit them o Column index number value = column/row number in reference table (e.g. 2 if you find the info in the 2nd column of the table…) o close match… o 1st column of reference table needs to contain what I am going to need to use to find a match

Not finding closest value, but the appropriate value Order table starting from lower to higher Vlookup is for vertical/column reference tables Hlookup is for horizontal/row reference tables =VLOOKUP(lookup value,table array,col index num,[range lookup]) =HLOOKUP(lookup value,table array,row index number, [range lookup])  Value: what you look up (e.g. distance) to get to what you need… the variable you know…  Table: 2 or more columns of data… sorted in ascending order  Index number: the column # in the table from which the matching value must be returned  Approximate match: is false (to find an exact match) is TRUE to find approximation 3 PRACTICE PROBLEMS  An “infield fly” in baseball is called to prevent the defense from recording an easy double play. When an “infield fly” occurs, the batter      

is automatically out once the ball is touched by a fielder or hits the ground and the baserunners must go back to their bases (though they may "tag up" if they wish). An infield fly occurs when the following conditions are met: (1) there is a “force out” at third base (this means that there are runners on first base and second base), (2) there are not two outs, and (3) the batter hits a catchable fly ball to the infield or the shallow outfield. The table below highlights 30 baseball scenarios. Complete the tasks below to determine if the umpire should declare an infield fly.  Use the AND function with appropriate arguments in cell H11 to determine if there is a force out at third base. There is a force out at third base if "Runner on 1st" and "Runner on 2nd" are both "Yes". [9 points] o IF(AND(C11= “YES”, D11 = “YES”), “Forced Out” (value if true), “Not Out” (value if false)) o Or you could say:  AND(C11 = “Yes”, D11 = “Yes”) o 2 outcomes, so only one logical test needed  Use the OR function with appropriate arguments in cell I11 to determine if there is a "Fly Ball". There is a "Fly Ball" if a Catchable Fly Ball [is] Hit to the "Infield" (cell E11 is "Yes") or "Shallow Outfield" (cell F11 is "Yes"). o =OR(E11= “YES”, F11= “YES”) o This will then auto. Populate if true or not o It will say “TRUE” or “FALSE” after using this function...

 Use the NOT function in cell J11 to determine if there are "Not 2 Outs". Use the "Outs" column in your determination. o =not(G11>=2)  Use the AND function in cell K11 to determine if all of the conditions are met for an infield fly to be declared. These conditions are: there must be a force out at third (H11 = TRUE), there must be a catchable fly ball hit to the infield or shallow outfield (I11 = TRUE), and there must not be 2 outs (J11 = TRUE). o AND(H11=TRUE, I11=TRUE, J11=TRUE) 3 PRACTICE PROBLEMS  Beverly sells donuts at the local bakery. Donuts cost $.50 each unless customers buy a dozen or more. When customers buy at least a dozen they cost $.40 each. Beverly earns a commission based on her sales performance. Because the bakery makes so much money on beverages, she earns 10% commission (based on the total sale) when a customer buys a drink. She also earns commission on her sales. She earns 2% on each sale that totals more than $10. She earns 1% commission on each sale that totals more than $5 (but less than $10). She does not earn commission on each sale that totals $5 or less (though she will earn the beverage commission). Complete

the table below to help Beverly calculate her commission based on the 50 customers she helped this morning.  Use an IF function in cell E18 to calculate the price to charge per donut for order 1. A customer is charged $.50 per donut unless they buy a dozen or more. In this case, they are charged $.40 a donut. Refer to the appropriate price in cells C11 and C12 for your "value_if_true" and "value_if_false" arguments. Use absolute and relative references when appropriate.  Use an IF function in cell E18 to calculate the price to charge per donut for order 1. A customer is charged $.50 per donut unless they buy a dozen or more. In this case, they are charged $.40 a donut. Refer to the appropriate price in cells C11 and C12 for your "value_if_true" and "value_if_false" arguments. Use absolute and relative references when appropriate. [5 points] o =IF(C185, IF (F18>10, $g$14,$g$13), 0)  Commission sales = F18*H18  Beverage: D18*$G$12  Total Commission: I18+J18  Buy a donut? o Commission (total commission cell) > 40, “yes”, “no” 3 MORE PROBLEMS  Built-tough Boards sells outside bulletin boards used to display community information on the outside of buildings. They sell blue and red bulletin boards which they will deliver regionally up to 1000 miles. While they offer a discount for purcha...