Introduction to Excel formulae and functions



-935990-914400 Excel 2007More FunctionsTable of Contents TOC \o "1-2" Working with Names PAGEREF _Toc319416126 \h 3Default Names PAGEREF _Toc319416127 \h 3Naming Rules PAGEREF _Toc319416128 \h 3Creating a Name PAGEREF _Toc319416129 \h 4Defining Names PAGEREF _Toc319416130 \h 4Creating Multiple Names PAGEREF _Toc319416131 \h 5Selecting Names PAGEREF _Toc319416132 \h 5Deleting Names PAGEREF _Toc319416133 \h 6Names in Formulae PAGEREF _Toc319416134 \h 6Applying Names PAGEREF _Toc319416135 \h 6Paste List PAGEREF _Toc319416136 \h 8Counting and totalling cells conditionally PAGEREF _Toc319416137 \h 9SUMIF() PAGEREF _Toc319416138 \h 9COUNTIF() PAGEREF _Toc319416139 \h 9Calculation with Dates PAGEREF _Toc319416140 \h 9Viewing Dates as Numbers PAGEREF _Toc319416141 \h 10Calculating the Difference Between two Dates PAGEREF _Toc319416142 \h 10Date Functions PAGEREF _Toc319416143 \h 11=Today() PAGEREF _Toc319416144 \h 11=Now() PAGEREF _Toc319416145 \h 11=MONTH PAGEREF _Toc319416146 \h 11=DAY PAGEREF _Toc319416147 \h 11=YEAR(date) PAGEREF _Toc319416148 \h 11Text Functions PAGEREF _Toc319416149 \h 12=CONCATENATE PAGEREF _Toc319416150 \h 12=TRIM PAGEREF _Toc319416151 \h 12=PROPER PAGEREF _Toc319416152 \h 13=UPPER PAGEREF _Toc319416153 \h 13=LOWER PAGEREF _Toc319416154 \h 13=LEFT, =RIGHT PAGEREF _Toc319416155 \h 14=MID PAGEREF _Toc319416156 \h 14=LEN PAGEREF _Toc319416157 \h 14Logical Functions PAGEREF _Toc319416158 \h 15=IF PAGEREF _Toc319416159 \h 15Nested IF PAGEREF _Toc319416160 \h 16=AND PAGEREF _Toc319416161 \h 16OR PAGEREF _Toc319416162 \h 17NOT PAGEREF _Toc319416163 \h 17=VLOOKUP PAGEREF _Toc319416164 \h 18=HLOOKUP PAGEREF _Toc319416165 \h 18Maths Functions PAGEREF _Toc319416166 \h 19=ROUND PAGEREF _Toc319416167 \h 19=INT PAGEREF _Toc319416168 \h 19Understanding error messages PAGEREF _Toc319416169 \h 19Typical errors and their causes PAGEREF _Toc319416170 \h 19Conditional Formatting PAGEREF _Toc319416171 \h 21Clearing Conditional Formats PAGEREF _Toc319416172 \h 21Working with NamesIt is easy to lose track of what information particular cells or ranges of cells in a worksheet contain, particularly in a large worksheet. Referring to a cell (or range of cells) by its cell address (e.g. A1, G19, C25:C65) is not very intuitive. Excel allows you to create a Name to refer to a cell, a group of cells, a value or a formula.A name is easier to remember than a cell reference.You can use a named reference almost anywhere you might use a cell reference, including in formulae and dialog boxes.Formulae that use names are easier to read and remember than formulae using cell references. For example, the formula: =Assets-Liabilities is clearer to read and understand than the formula: =F6-G6Excel can automatically create names for cells based on row or column titles in your spreadsheet, or you can enter names for cells or formulae yourself.If you name a cell you are likely to need to use in an absolute reference, it will save you from using the $ symbol in the cell reference, as you will simply need to refer to the cell name.Default NamesBy default, every cell has a unique name – the cell address (A1, F4 etc.). When you select a cell, its name appears in the Name Box. It is possible to move directly to a cell location simply by typing the cell name into the Name Box and pressing Enter.Naming RulesNames are unique within a workbook and the names that you choose to use must adhere to certain rules. The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, full stops, and underscore characters. Names cannot be the same as a cell reference, such as AB11 or R1C1.Spaces are not allowed. Underscore characters and full stops may be used as word separators – for example, First.Quarter or Sales_Tax.A name can contain up to 255 characters.Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. Creating a NameSelect the cell or cells you want to name.Click in the Name box and type a name.Press Enter.Defining NamesYou will often find that the names you want to use for your cells are the same as the headings you have given them in your worksheet. When this is the case, you can save yourself some typing by using Define Name to set them up. With the Define Name command, Excel looks at the cells around those selected and if it finds a label, it proposes that you use it as your name. You can still overwrite Excel’s proposal if it chooses something inappropriate.Select the cell or cells you want to name.From the Formulas tab, in the Defined Names group, select Define Name The following dialog box will appear:18859582550The New Name dialogue box will appear and displays the name that Excel proposes for the selection. You can change this if it is not appropriate. The Refers to box (at the bottom of the dialog box) will show the range of the selected cells.You can set the Scope for your name ie whether the name is visible anywhere in the workbook or just in the sheet you are in.Creating Multiple Names2658745259080When you want to use column and row headings on a worksheet to set up names for data, you don’t have to do them one by one. In the example below, it would be useful to set up names for the different stationery items and the different column headings. You can create them all at once using Create Names.Select the range for which you want to set names up, including the column and/or row headings to be used as names.3935730131445 From the Formulas tab, in the Defined Names category, select Create from Selection The following dialog box will appear: Excel will guess which edges of the selection contain the labels you want to use. However, you can change the options by checking and unchecking the boxes until the correct edges are selected.Click OK to set the names up.When you select a named range, its name appears in the Name Box.Selecting NamesOnce you have created names in a workbook, you can quickly move to them either using the Name Box or F5 (GoTo key).Click the drop-down list arrow to the right of the Name Box.Choose the name you want to select by clicking it with the mouse.The screen display will jump to the range you chose and select the cells within it.orPress F5 to access the GoTo dialog box.Press Tab to select the first item in the GoTo list.Use the arrow keys to move the highlight bar up and down the list of defined names.Press Enter to move to the selected name.Deleting NamesThe Name Manager will allow you to view all names (and their Scope in the workbook). You can delete names from here. 262255210820To delete a name: Select the Name and click on the Delete button. If you delete a name that is being used in formulae, Excel will display #NAME? in the cell containing those formulae. (You can use the Edit|Undo feature to reinstate the name.)Names in FormulaeBecause names make selecting and referring to cells much easier, it makes sense to use them in formulae. The other advantage that they have over cell references is that names are absolute. This means that you don’t have to worry about copying formulae that refer to names.To use names in a formula:4394835358140Move to the cell where you want the formula and begin typing it – all formulae begin with an equals (=) sign. When you want to use the name, click on the Use in Formula button in the Defined Names group. Or press F3 to access the Paste Name dialog box.Use the up and down arrow keys to highlight the name you want in your formula.Press Enter to close the dialog box and paste the name into the formula.If you can remember what you called your ranges when you named them, you can simply type the names into the formula.Applying NamesThere may be occasions where you already had formulae and functions set up in a workbook before you created any names. This might mean that there are formulae referring to cell references that you have subsequently given names to. You can apply names to formulae even if you created them after the formulae themselves were set up.Select the cell or cells containing the formulae whose references you want to replace with names.From the Formulas tab, in the Defined Names group, click on the arrow at the side of the Define Name button and select Apply Names... The following dialog box will appear: Excel will pick those names it thinks relevant to your selection, however, you can select or deselect other names in the list by clicking on them.When all names to be applied have been selected, click OK to apply the names and close the dialog box. When you look at your formulae, you should find that anywhere there were references to named ranges; Excel has replaced the cell references with the names.Paste List4442460259080You can use the paste a list of all the Names into your worksheet. Excel will place this on the workbook wherever the active cell is positioned.Select a blank cell where you want the list of names to begin.Click on Use in Formula button in the Define Names group.Click on the Paste Names option at the bottom. (You can also Press F3 to access the Paste Name dialog box.)Click the Paste List button.The list will appear on the worksheet.When you choose a start cell for your pasted list, make sure there isn’t any data immediately below, as it will get cleared when you paste the list.391160046355Will display in your worksheet:Counting and totalling cells conditionallyOccasionally you may need to create a total that only includes certain cells, or count only certain cells in a column or row. The only way you could do this is by using functions that have conditions built into them. A condition is simply a test you can ask Excel to carry out, the result of which will determine the result of the function.SUMIF()You can use this function to say to Excel, “only total the numbers in the Total column where the entry in the Course column is “Word Intro”. The syntax of the SUMIF() function is detailed below:=SUMIF(range,criteria,sum_range)Range is the range of cells you want to test.Criteria are the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".Sum_range are the actual cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed.Using the example above the SUMIF() function would be as follows:=SUMIF(B4:B23,"Word Intro",C4:C23)COUNTIF()The COUNTIF function allows you to count those cells that meet a certain condition. The function syntax is as follows:=COUNTIF(range,criteria)Range is the range of cells from which you want to count cells.Criteria are the criteria in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples". With our example (shown above), the COUNTIF function you could use to determine the number of Word Intro courses run would be: =COUNTIF(B4:B23, “Word Intro”)or=COUNTIF(B4:B23, E4)Calculation with DatesExcel also allows you to perform calculations with dates. All dates are stored in Excel as sequential numbers. By default, January 1 1900 is serial number 1, and January 1, 2004 is serial number 40933 because it is 40,933 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.Because dates and times are values, they can be added, subtracted, and included in other calculations. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.Viewing Dates as NumbersTo view dates as numbers:Select the cell and click Cells on the Format menu. Click the Number tab, and then click Number in the Group box.Calculating the Difference Between two Dates63500885825In the following example the date in cell B1 has been subtracted from the date in cell B2. The result in cell B3 has been formatted to display a number (the number of days between two dates) with no decimal places.NB: You will need to format the result of the formula to a number format, as it may display as a date.If you want to know what the date is 3 weeks’ time, and you have the current date in cell A1, then your formula could be:=A1+21 Date FunctionsExcel won’t recognise a date just typed in directly into a formula: Eg =12/1/2012+21. You would have to use a date function to convert the date into one that Excel can understand as below:=Date(2012,1,12)+21 The arguments being: (year,month,day)=Today() Current date – this is a dynamic date (will change every day). You could use this in a formula to see what the date will be in 3 weeks’ time from today’s date: =Today()+21=Now()Returns the current time. Recalculates as the sheet recalculates. To force a recalculation, press F9.=MONTH=MONTH(date) Returns the month as a number from 1 (January) to 12 (December)=DAY=DAY(date) Returns the day of the month as a number from 1 to 31=YEAR(date)=YEAR(date) Returns the year as an integer. From the year 1900 to 9999Text Functions=CONCATENATEYou can join the contents of cells together using & (ampersand) symbol. Eg. =A1&B2 will result in haroldgreen. To include a space in between, you will need to add the space in as another argument:=A1&” “&B2As this can be laborious if you have several cells to join together, there is a function called CONCATENATE to help. You can join up to 255 separate arguments.This function takes a series of text arguments separated by commas and joins them together to create a string. Arguments can be cell references, numbers or text. In the example below, we want column D to say “Harold Green is aged 75”, “Violet Brown is aged 77” etcWe can use CONCATENATE to achieve this as follows:=CONCATENATE(A2," ",B2,"is ",C2) The Function Arguments dialogue box will look like this:=TRIMRemoves all spaces from text except for singe spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.=TRIM(text)Text is the text from which you want spaces removed. This is usually a cell ref.=PROPERConverts a text string to proper case. The first letter of each word is a capital, the rest is in lower case:=PROPER(text)Text is the text from which you want to convert to proper case. This is usually a cell ref.=UPPERConverts a text string to all upper case (capital) letters: =UPPER(text)Text is the text from which you want to convert to upper case. This is usually a cell ref.=LOWERConverts a text string to lower case. =LOWER(text)Text is the text from which you want to convert to lower case. This is usually a cell ref.You can combine the above case conversion functions with the concatenate function to always have a text string in the case you want:= PROPER(CONCATENATE(A2," ",B2,"is ",C2))The above function will result in:Harold Green is 75=LEFT, =RIGHTReturns the specified number of characters from a text string, starting from the left:-56515132080=LEFT(A3,5) will return 57003. These are the first five characters in Cell A3, starting from the left.=RIGHT(A3,5) will return 697/1. These are the last five characters in Cell A3, starting from the right..=MID=MID returns the middle characters from a text string, given a starting point and how many to return from that point:=MID(A3,7,2) will return 69. These are the two characters to the right of the 7th character.=LEN=LEN will return the number of characters in a string.=LEN(A3) will return 11. There are 11 characters in Cell A3Logical Functions=IF=IF checks if a condition is met and returns one value if TRUE, and another value if FALSEEg=IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.=IF(C5+D5>=100,"Pass","Fail") returns “Pass” if the sum of C5 and D5 is 100 or more, and “Fail” if the result is less than 100.The Function Arguments would be:Value-if-true and Value-if-false can be Text, Values or Calculations/Formulae.Nested IFYou may want to evaluate more than one condition, and therefore, result in more than one outcome. You can have up 64 nested IF functions in Excel 2007.Eg =IF(C5+D5>150,"A",IF(C5+D5>=100,"B","Fail"))IF C5+D5 is greater than 150, then the result will be “A”, if C5+D5 is greater or equal to 100, then the result will be “B”, if C5+D5 is less than 100 (this is only other number it could be!) then the result is “Fail”In Excel 2007 onwards you can have up to 64 nests!=ANDYou may have more than one condition to meet for your logical test to be true. You can Nest the AND function inside the IF and have up to 30 conditions to be evaluated.EG The students ONLY get a Merit if they gain more than 80 marks for both Part 1 AND Part 2 of the Audit Exam:=IF(AND(C5>80,D5>80),"Merit","")Only the students who have achieved over 80 marks in both exams will gain a MeritORIf you have more than one condition, but any can be met for the result to true, then use OR.=IF(OR(C5>80,D5>80),"Merit","")The students who have achieved over 80 marks in Audit Exam Part 1 OR Part 2 will gain a Merit18859550800NOTReverses the true value. Eg:=IF(NOT(C5=50,”OK”,””) will return “OK” as C5 does = 50, but none of the other cells in the Column C have the value 50, therefore the result will be “blank” for the rest of the column.=VLOOKUP -227965450850The VLOOKUP function will look up a value in the first column of table, and returns the value in the same row from a column that you specify.77660597155Range called SALARYIn the above example =VLOOKUP(E5,SALARY,2) looks at value in cell E5 (34), looks for this value in the first column in the range “SALARY” and returns the value in column 2 of that range.lookup_value: the value to be looked uptable_array: where to look the information upcol_index: the number of the column from the start of the table_array that you want to retrieveindex_num: ‘True’ will find the closest match, ‘False’ will find the exact match=HLOOKUP=HLOOKUP is as the VLOOKUP function, but looks up the value in first row of a range, instead of the first column.Maths Functions=ROUND=ROUND if useful to force the result of a calculation to be a specific number of decimal places. Unlike number formatting, which just changes what the number looks like, but retains the accuracy of the original calculation, and if this value is then used in other formulae, it may result in rounding errors.Eg =ROUND(2.3165,2) Result will be 2.32 This can be nested into another function: For example:=ROUND(C5*12.5%,2) Will round to 2 decimal places the result of the calculation C5 multiplied by 12.5%=INT=INT rounds the number to the nearest integer=INT(2.3165) Will return the integer part of the value – the result will be 2.Understanding error messagesExcel may display error messages if your formulae or functions contain mistakes (note that it will not detect all errors in calculations). It is always worth checking the result of your formulae by hand if the formula is at all complex. Excel’s error messages contain a # symbol followed by a diagnostic word (see the table below). In some cases, the cell with an error in it has a small green arrow in the corner. In such cases, if you click in the cell a yellow symbol with an exclamation mark appears. Click the exclamation mark for options to help you to trace the source of the error.2007235165100 Green triangleCell containing error messageYellow symbol with exclamation markOptions for dealing with errorTypical errors and their causes######The column is not wide enough to display data (for numbers).Date or time may be negative.#VALUE!Occurs when the wrong type of argument is used in a function or formula. For example, there is text in a formula that requires a number or logical value.#DIV/0!Occurs when a number is divided by zero.#NAME?Occurs when Excel doesn’t recognise text in a formula (e.g. misspelling a function name or cell reference). #N/AOccurs when a value is not available to a formula or function – perhaps data are missing.#REF!Occurs when a cell reference is not valid – perhaps the cell has been deleted.#NUM!Occurs when a number is invalid – perhaps a price has been entered with the ? sign, or a formula results in a number too big or too small for Excel to display.Circular referenceThis happens when the formula points to the cell in which the result is to be displayed, e.g., placing the formula =SUM(A1:A2) into cell A2.Conditional FormattingConditional formatting will format cells to your specifications or to preset formats, which match the criteria that you specify. For example, you may want to highlight all the cells that have a value higher than 80 in a red font with a yellow background.This is a cell formatting feature, so you need to select the cell range which you want to affect first. Then click on the Conditional Formatting button in the Styles group on the Home tab.On the Highlight Cell Rules option, choose the criteria required. Eg Greater Than, and specify 80 as the value and then choose from the list of formats offered, or create your own with Custom Format...Clearing Conditional FormatsYou can clear all Rules by selecting Clear Rules on the Conditional Formatting button or be selective in the Rules you wish to delete or edit with the Manage Rules option. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download