Excel Operators
Excel operators are symbols used to perform operations on data in Excel spreadsheets. They include arithmetic operators such as addition (+), subtraction (-), multiplication (*), and division (/), as well as comparison operators like equal to (=), not equal to (<>), greater than (>), and less than (<). These operators are essential for performing calculations and comparisons within Excel.
7 Key excerpts on "Excel Operators"
eBook - ePub- Philip Bowcock, Natalie Bayfield(Authors)
- 2014(Publication Date)
- Estates Gazette(Publisher)
...Chapter 3 Formulae and operators 3.1 Formulae Formulae are instructions to Excel to operate on the numbers that you have already entered. We inform the computer that it is an instruction instead of a number or text by putting an equals (=) sign in front of the instruction. Many instructions are in the form of equations. 3.2 Operators Formulae are constructed using operators, and these will be used extensively. The principal operators are: addition + subtraction − exponentiation (raising to a power) ^ multiplication * division / Boolean True/False Operators take precedence according to the normal conventions of mathematics – Boolean first, exponentiation next, then multiplication and division, and finally addition and subtraction. (We shall not consider Boolean operators here.) Parentheses “( )” takes precedence over all operators. 3.3 Writing formulae Formulae in Excel generally use cell references, but may include numbers as constants. Example: (a) Type 1.1 in cell A1 and 20 in cell A2 (b) Select cell A3 and type: = A1^A2 (“^” is above the 6 on the keyboard) 9 Remember to press Return. The figure in cell A3 should now be 6.7275. We can now use cell references instead of numbers to allow “what if” analysis. (c) Select cell A2 and change the number to 30 The answer in cell A3 will immediately change to 17.4494. 3.4 Relative and absolute cell referencing 3.4.1 Relative cell referencing Relative cell referencing allows you to apply the same formula to different cells in the worksheet without rewriting it. Example (a) Enter number 20 in cell A1, 20 in A2, 5 in B1 and 25 in B2. (b) In A3 enter the formula = A1 + A2 Remember to press Return. (c) Select A3 again. Now we will apply the formula in this cell to the next column of numbers by copying it across. On the selection border around cell A3 is a small square in the bottom right corner of the cell. This is called the fill handle...
eBook - ePub- Surinder Virdi, Roy Baker, Narinder Kaur Virdi(Authors)
- 2014(Publication Date)
- Routledge(Publisher)
...CHAPTER 20 Computer techniques Learning outcomes: (a) Perform simple calculations involving addition, subtraction, multiplication and division (b) Use a range of functions to determine the sum, mean, range, etc. (c) Display the results as pie charts and bar charts 20.1 Introduction We have at our disposal many devices like calculators and computers to make mathematical calculations easier and faster than the manual techniques. It has taken inventors several centuries to reach the present stage. In the early 1600s John Napier, a Scottish mathematician, invented logarithms. The use of logarithms made several types of calculations easier and faster. Later, William Oughtred used Napier’s logarithms as the basis for the slide rule, which remained in common use for over 300 years. The use of the slide rule came to an end in the early 1970s, when pocket calculators became popular. The next development was an electronic spreadsheet invented by Daniel Bricklin, a student at Harvard Business School. Originally the electronic spreadsheet was invented to make calculations in business easier, but now it is used in many disciplines. Many spreadsheet softwares are available now, but this chapter is based on the widely used Microsoft Excel. 20.2 Microsoft Exel 2000 Microsoft Excel 2000 is a spreadsheet program that lets us work with numbers and text. An Excel file is known as a workbook, and one workbook can hold several sheets, e.g. sheet 1, sheet 2, sheet 3, etc. Each sheet is divided into rows and columns and their intersections create cells, which are known by a reference. A cell reference is a combination of letter(s) and number, e.g. B10 or AA5 to signify the intersection of a column and a row. Figure 20.1 shows a spreadsheet in which the reference of the selected cell is D6. Figure 20.1 Each cell can hold text, a number or a formula. A formula is a special way to tell Excel to perform a calculation using information present in other cells...
- Bernard Liengme(Author)
- 2008(Publication Date)
- Academic Press(Publisher)
...Logical expressions evaluate to either TRUE or FALSE in Excel. A logical expression has the form: It can be useful to have logical expressions evaluated to 1 or 0. Excel treats the Boolean values as 1 and 0 when combined with mathematical operations. Following the example above, the formula =(A1>10)*1 will return the value 0 while =(A1>=10)*1 returns the value 1. Using two negation operators is a very efficient method to coerce Boolean values to numeric values; we may use a formula such as =−−(A1>=10). When a Boolean value is expected, Excel will accept any nonzero numeric value as TRUE and a zero value as FALSE. Exercise 1: Boolean Functions The functions AND and OR may be used to test two or more logical expressions, while the NOT function is used to reverse the truth value of a logical expression. (a) On Sheet1 of a new workbook, enter the values in A1:B4 of Figure 5.1. Use the information in columns D and F to enter formulas in columns C and E. (b) Save the workbook as Chap5.xlsx. Figure 5.1 By default, Excel aligns Boolean values (TRUE and FALSE) centered horizontally in their cells. Note that if in this worksheet you entered the formula =A2>5, the result would be TRUE. Excel would compare the letter a (a text data type) with the literal 5 (also a text data type): the ASCII value for a is 97, and that for 5 is 53. There are some common combinations that are useful to know. In the following table, A and B may be expressions or references to cells containing the values TRUE or FALSE. You may wish to experiment with these nested formulas on Sheet1. Logic Formula TRUE returned if NAND =NOT(AND(A,B)) Not both true NOR =NOT(OR(A,B)) Neither is true XOR =OR(AND(A, NOT(B)),AND(B, NOT(A))) Only one is true Exercise 2: Practical Example Scenario for this Exercise: In a manufacturing plant, 10 items are tested every hour...
eBook - ePub- Stephen Morris(Author)
- 2007(Publication Date)
- Routledge(Publisher)
...3 Using formulae DOI: 10.4324/9780080481067-3 Entering a formula Operators Using brackets Names Excel functions Inserting a function The IF function Copying formulae Relative and absolute Moving cells and ranges Exercises Entering a formula A formula is used to calculate the value of a cell from the contents of other cells. For instance, formulae may be used to calculate totals or averages, produce percentages or find the minimum and maximum values in ranges. A formula consists of a mathematical or text expression, which refers to other cells or to constant values. The components (‘arguments’) of the formula are linked together by operators (+, − etc.) You can make formulae more readable by putting spaces on either side of the operators. Formulae must start with an =, + or − sign. Basic steps To enter a formula : Click on the cell where you want to show the result (e.g. C9). Type the formula. For example: =c5+c6+c7+c8 (You can use either upper or lower case letters.) The formula appears simultaneously in the cell and the formula bar. Press [ Enter ]. The result of the calculation is shown in the cell. When you click on this cell again, the formula is shown in the formula bar, where it can be edited. When you click on the formula bar or double-click on the cell, cell references in the formula are shown in different colours, and the cells concerned are highlighted by boxes in the same colours. Take note There is an easier way of doing this type of calculation, using the SUM function – see page 36. Tip Instead of typing a cell reference, click on the cell you want. If you make a mistake and a numeric formula cannot be calculated (for instance, you divide by zero or refer to a text cell), Excel displays #VALUE! in the cell and puts a green triangle in the top left-hand corner of the cell. When you click on the cell, a warning symbol is shown; clicking on that gives a pop-up menu of options. Other errors may be harder to identify and will not produce an error message...
eBook - ePubUsing Excel for Business Analysis
A Guide to Financial Modelling Fundamentals
- Danielle Stein Fairhurst(Author)
- 2012(Publication Date)
- Wiley(Publisher)
...CHAPTER 5 Using Excel in Financial Modelling In this chapter, we take a look at some of the practical tools, functions, and formulas commonly used in financial modelling in Excel. FORMULAS AND FUNCTIONS IN EXCEL Excel contains over 300 different formula codes, called functions. The most commonly known function is the SUM, used to total a range of cells. Most Excel users only use a very small percentage of the available functions, and many of them are irrelevant for use in finance and financial modelling. It is impossible to go over all of them, but here we will cover the ones that are going to be the most useful in using Excel for business analysis. In most cases, you will find it easiest to use the Function Wizard, accessible via the Insert Function button on the Formula tab. See Figures 5.1 and 5.2. FIGURE 5.1 Function Wizard FIGURE 5.2 Formula Builder in Excel for Mac 2011 Note that the Function Wizard is called the Formula Builder in Excel for Mac 2011; however we will refer to it as the Function Wizard in future for simplicity. It’s fair to say that some Excel users are guilty of using Excel as a fancy calculator! If so, you’d be just using a formula in Excel, such as =A1+A2 or =452*12. There are, however, pre-defined formulas in Excel such as IF, SUM, VLOOKUP, INDEX (and several hundred more). These are referred to as functions. So, you could write a formula containing a function, like = MAX(A1:B20), or you could just write a simple formula that does not contain a function, like = A1/A2. Many of us use the words formula and function interchangeably, but it’s nice to know the difference! Because there are hundreds of functions in Excel, you should familiarise yourself with as many as possible, so that you know what is available. For use in business analysis, the most useful functions fall into the categories of logical, aggregation, lookup, and financial...
- Morten Helbæk, Ragnar Løvaas, Jon Olav Mjølhus(Authors)
- 2013(Publication Date)
- Routledge(Publisher)
...Part I Excel Excel, which is a part of Microsoft Office, is a spreadsheet program. This computer program has been developed over many years, and is today the market leader of spreadsheet programs. This book uses Excel 2010, which includes some changes compared to Excel 2007. The 2010 version is available in a 64-bit version which offers increased capacity and makes it easier to work with large and complex spreadsheets. The ribbon in Excel 2010 includes some new tools, the most important being the File button which opens the backstage view. Part I is an introduction to Excel with demonstrations spanning from basic methods to more advanced applications. Tips showing how to work more effectively and how to build functional models are also included. Those who want to learn even more may consult more comprehensive textbooks on Excel or search on the Internet. Excel consists of the modules spreadsheet, charts, VBA (Visual Basic for Applications) and several library functions. This part discusses spreadsheets, charts and a few library functions. VBA will be discussed in Part V. 1 Getting started DOI: 10.4324/9780203362884-2 1.1 Workbooks and spreadsheets An Excel file is called a workbook and consists of one or several spreadsheets. A spreadsheet is built up by many cells that may contain text, numbers or formulas that refer to other cells. Each cell has an address defined by a column number (from 1 to 1 048 561) and a row number (from A to XFD). The nice thing about Excel is that you can perform operations on the numbers or the text written in the cells. You can do calculations in a budget, complex mathematical calculations, operations on a text, etc. In figure 1.1 the numbers 2 and 3 are written in the cells A1 and A2 respectively. A formula in Excel always starts with a “=”. In cell A3 we have written the formula “=A1+A2” so that the content in the cell becomes 5. When writing the formula we do not have to write “A1”...
eBook - ePub- Scott Fawcett(Author)
- 2013(Publication Date)
- Estates Gazette(Publisher)
...Chapter 2 Tools of the Trade Essential functions that you need to know If you are going to program a flexible cashflow, there are some functions that you will have to get to know and love. There are also techniques that you will need to grasp. We will start with some of the Excel spreadsheet functions that will be of particular use. The IF statement is your friend The essence of a spreadsheet is to have the value of individual cells changing depending on the value of other cells. In a simple SUM calculation for example, the output of the formula will vary depending on the values in the SUM range but it will always give the summation of those cells. The IF statement enables the spreadsheet author to do much more than this by allowing the operative formula (i.e. the formula which gives the cell’s result) within the cell to be changed. So, taking the above example, the SUM formula could become a multiplication calculation. Simple IF statements At its simplest, the IF statement need not contain other formulae. The structure of the statement is as follows: =IF(Logical Test, TRUE, FALSE) The “Logical Test” is essentially a question and can use equals, greater than, less than, etc. comparators. If the answer to the question is true, then the value generated by the TRUE part of the function is used, if not then the FALSE value is used. A simple example of how this works is as follows: =IF(B10=5,“B10 equals 57”,“B10 does not equal 5”) The results of this are straightforward and, of course, only one number in B10 will generate the TRUE result. It is worth briefly noting the use of the quotation marks. These are used to make text appear in cells, rather than figures...
