The document presents a comprehensive guide on mastering Excel formulas and functions, including techniques for displaying and entering formulas, using the IF and VLOOKUP functions, and various rounding and statistical functions. It also highlights recently added functions such as aggregate, isformula, and date/time calculations including weekday and networkdays. Additionally, it covers advanced topics like conditional operators and nested IF statements to enhance Excel proficiency.
2 Power Tips Display all formulas in a worksheet Highlight all formula cells in a worksheet Enter formulas in multiple cells with the Ctrl key Extended uses of the AutoSum button Relative/Absolute adjustments Power Functions The IF function: simple, nested, in combination with AND and OR VLOOKUP – Exact and approximate lookups Rounding functions ROUND, ROUNDUP, and ROUNDDOWN MROUND, CEILING, FLOOR, INT, TRUNC Statistical Functions MAX, MIN, AVERAGE, MEDIAN MODE, RANK, LARGE, SMALL COUNTIF and Related Functions Recently Added Functions Date/Time Formulas and Functions Date/Time calculations WEEKDAY – determine day of the week NETWORKDAYS – working days between dates DATEDIF – days, months, years between dates Major Topics Presented by: Dennis Taylor dennistaylor@msn.com
3.
AutoSum Button Select cells along right side or below data (or both) and use AutoSum button or its drop arrow functions Displaying Formulas and selecting formula cells Press Ctrl+~ to toggle between displaying formulas and displaying results On Home tab, click the Find&Select button, then Formulas Multiple cell formulas with the Ctrl key Select cells where similar formulas are needed Type one formula and press Ctrl+Enter Relative References - Use the F4 key Quickly adjust cell formulas to freeze row references, as in A$4 or column references, as in $A4. Use entire Row or Column references in formulas =SUM(B:B) instead of SUM(B2:B655) 3 Power Tips
4.
=IF(condition,true,false) Conditionaloperators - < > = >= <= < > =IF(B2>=400,1000,"Sell") Nested, multiple IFs =IF(B3>5,100,IF(B3>3,50,25)) =IF(B3>5,100,IF(B3>3,50,IF(B3>2,25,10))) Using AND and OR with IF =IF(AND(condition1,condition2,…),true,false) =IF(AND(B6="A",B7="B"),100,50) =IF(OR(condition1,condition2,…),true,false) =IF(OR(A3=7,D3=5),100,50) 4 Power Functions: the IF Function
5.
VLOOKUP (itemto look up, table location, column number in table where answer is found, exact indicator) =VLOOKUP(A7,P:S,2) approximate lookup Compare the value of cell A7 with values in the left column of the range in columns P through S; locate the value nearest to (but not above) the value in A7 and return a value found in column Q (second column of P thru S. =VLOOKUP(H3,K1:N9,3,0) exact lookups Compare the value of cell H3 with values in the left column of the range K1:N9; when an exact match is found, return a corresponding value found in the third column of K1:N9. If not found, return #N/A. 5 Power Functions: the VLOOKUP Function
6.
ROUND –alter the results of formulas to round results to the nearest power of ten – often to two or zero decimals =ROUND(B3*N1+B3,2) – rounds a calculation to the nearest two decimal places =ROUND(B3*N1+B3,0) – rounds the calculation to the nearest whole number ROUNDUP – rounds up to the next level, =ROUNDUP(C3*D4,1) – if C3*B4 equals 7.32, changes result to 7.4 ROUNDOWN - rounds down to the next level =ROUNDDOWN(F3*G4,1) – if F3*G4 equals 6.18, changes result to 6.1 MROUND, CEILING, FLOOR – allow rounding to any value, not just powers of ten 6 Rounding Functions
7.
MAX, MIN- find highest/lowest values in a range AVERAGE - calculate arithmetic mean of values MEDIAN - calculate the middle value in a values range MODE - calculate the most frequent occurrence in a values range RANK - calculate the rank of a value in a range LARGE, SMALL - find the nth largest or smallest value in a range 7 Statistical Function Overview
8.
=COUNTIF(E2:E90,"=Colorado") Counthow many cells in the range E2:E90 equal "Colorado" =SUMIF(K2:K66,"=Ohio",J2:J66) Total all values in J2:J66 when the corresponding entry in Column K equals "Ohio". =AVERAGEIF(K2:K66,"=Ohio",J2:J66) Calculate average of values in range J2:J66 when corresponding entry in Column K equals "Ohio" =COUNTIFS(E:E,5,F:F,"=Iowa") Count how many cells in Column E = 5 and in Column F = Iowa" =SUMIFS(H:H,E:E,5,F:F,"Utah",G:G,">500") Total all values in Column H when the corresponding entries in Columns E, F, and G are: equal to 5, equal to Utah, and greater than 500, respectively. =AVERAGEIFS(H:H,E:E,5,F:F,"Ohio",G:G,">500") Calculate the average of all values in Column H when the corresponding entries in Columns E, F, and G are: equal to 5, equal to Ohio, and greater than 500, respectively. 8 COUNTIF and Related Functions
9.
New inExcel 2010------------------------------------------------------------------------- AGGREGATE – tabulate data based on commonly used statistical measures (SUM, AVERAGE, MEDIAN…) with options to ignore errors and hidden rows) New in Excel 2013------------------------------------------------------------------------- ARABIC– convert Roman numerals into Arabic numbers; a companion the ROMAN function which converts numbers from Arabic to Roman. ISFORMULA – check whether a reference is to a cell containing a formula, and return TRUE or FALSE can be used with Conditional Formatting to dynamically indicate which cells contain formulas FORMULATEXT – displays the text of a formula from another cell ISNA – check whether a value is #NA, and returns TRUE or FALSE 9 Recently Added Functions
10.
WEEKDAY =WEEKDAY(A3) calculate day of the week for the date in cell A3. Returns a value of 1(Sunday) through 7(Saturday) NETWORKDAYS =NETWORKDAYS("5/2/13", "9/4/13",G2:G10) calculate working days from 5/2/13 through 9/4/13, omitting any dates found in a list of holidays in cells G2:G10 -- returns the value 87. DATEDIF =DATEDIF("11/17/2006","7/5/2013","y") calculate number of full years between 11/17/06 and 7/5/13 returns the value 6 =DATEDIF("11/17/2006","7/5/2013", "m") calculate number of full months between 11/17/06 and 7/5/13 returns the value 79 10 Date/Time Formulas and Functions