شروحات في معادلات ألأكسل

[Bishara]

Creating a Basic Formula First things first: what exactly do formulas do in Excel? A formula is a series of mathematical instructions that you place in a cell in order to perform some kind of calculation. These instructions may be as simple as telling Excel to sum up a column of numbers, or they may be complex enough to use advanced statistical functions to spot trends and make predictions. But in all cases, these formulas share the same basic characteristics. Here are a few points to note: You enter formulas into a single cell. Excel calculates the result of a formula every time you open a spreadsheet or change the data a formula uses. Formula results are usually numbers, although you can create formulas that have text or Boolean (true or false) results. To view any formula (for example, to gain some insight into how a displayed result was produced), you must move to the cell containing the formula and then look in the Formula bar (Section 1.3.4). The Formula bar also doubles as a handy tool for editing your formulas. Formulas can evaluate a combination of numbers you input (useful when you want to use Excel as a handy calculator) or, more powerfully, the contents of other cells. Formulas can even process groups of cells when using certain functions. One of the simplest formulas you can create is this one: =1+1 The equal sign is how you tell Excel that you're entering a formula (as opposed to a string of text or numbers). The formula that follows is what you want Excel to calculate. Note that the formula doesn't include the result. When creating a formula in Excel, you write the question, and then Excel gives you the answer, as shown in Figure 7-1. Figure 7-1. Top: This simple formula begins its life when you enter it into a cell. The checkmark and X buttons to the left of the formula bar let you quickly complete or cancel, respectively, your formula. Bottom: Or you can press Enter, and Excel displays the result. The formula bar always displays the complete formula (1+1). In formula lingo, this particular example consists of two literal values (1 and 1) and one arithmetic operator (+). All formulas use some combination of the following ingredients: The equal sign (=). Every formula must begin with the equal sign. It signals to Excel that the cell contains a formula, not just ordinary text. The simple operators. These include everything you fondly remember from high school math class, including addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^). Table 7-1 lists these ingredients, also known as arithmetic operators. Numbers. These are known as constants or literal values, because they never change (unless you edit the formula). Cell references. These references point to another cell, or a range of cells, that you need data from in order to perform a calculation. For example, say you have a list of 10 numbers. A formula in the cell beneath this list might refer to all 10 of the cells above it in order to calculate their average. Functions. Functions are specialized formulas, built into Excel, that let you perform a wide range of calculations. For example, Excel provides dedicated functions that calculate sums and averages, standard deviations, yields, cosines and tangents, and much more. These functions, which span every field from financial accounting to trigonometry, are described over the next four chapters. Spaces. Excel ignores these. However, you can use them to make a formula easier to read. For example, you might write the formula =3*5 + 6*2 instead of =3*5+6*2. (The only exception to this rule occurs with cell ranges, where spaces have a special meaning. You'll see this described on Sidebar 7.3.) Table 7-1. Arithmetic Operators Operator Name Example Result + Addition =1+1 2 - Subtraction =1-1 0 * Multiplication =2*2 4 / Division =4/2 2 ^ Exponentiation =2^3 8 % Percent =20% 0.20 -------------------------------------------------------------------------------- Note: The percentage (%) operator divides a number by 100. -------------------------------------------------------------------------------- 7.1.1. Excel's Order of Operations For computer programs and human beings alike, one of the basic challenges when it comes to reading and calculating formula results is figuring out the order of operationsmathematician-speak for deciding which calculations to perform first when there's more than one calculation in a formula. For example, given the formula: =10 - 8 * 7 the result, depending on your order of operations, is either 14 or -46. Fortunately, Excel abides by what's come to be accepted among mathematicians as the standard rules for order of operations, meaning it doesn't necessarily process your formulas from left to right. Instead, it evaluates complex formulas piece-by-piece in this order: Parentheses (any calculations within parentheses are always performed first) Percent Exponents Division and Multiplication Addition and Subtraction -------------------------------------------------------------------------------- Note: If Excel encounters formulas that contain operators of equal precedence (that is, the same order of operation priority level), it evaluates these operators from left to right. However, in basic mathematical formulas this has no effect on the result. -------------------------------------------------------------------------------- For example, consider the following formula: =5 + 2 * 2 ^ 3 - 1 To arrive at the answer of 20, Excel first performs the exponentiation (2 to the power of 3): =5 + 2 * 8 - 1 and then the multiplication: =5 + 16 - 1 and then the addition and subtraction: =20 To control this order, you can add parentheses. For example, notice how adding parentheses affects the result in the following formulas: 5 + 2 * 2 ^ (3 - 1) = 13 (5 + 2) * 2 ^ 3 - 1 = 55 (5 + 2) * 2 ^ (3 - 1) = 28 5 + (2 * (2 ^ 3)) - 1 = 20 You must always use parentheses in pairs (one open parenthesis for every closing parenthesis). If you don't, Excel gets confused and lets you know you need to fix things, as shown in Figure 7-2. 7.1.2. Cell References Excel's formulas can be handy when you want to perform a quick calculation. But if you want to take full advantage of Excel's power, you're going to want to use formulas to perform calculations on the information that's already in your worksheet. To do that you need to use cell referencesExcel's way of pointing to one or more cells in a worksheet. Figure 7-2. Top: If you create a formula with a mismatched number of opening and closing parentheses (like this one), Excel won't accept it. Bottom: Excel offers to correct the formula by adding the missing parentheses at the end. This may not be what you wanted, though. If not, cancel the suggestion, and edit your formula by hand. Excel helps a little bit by highlighting matched sets of parentheses. For example, as you move to the opening parenthesis, Excel automatically bolds both the opening and closing parentheses in the Formula bar, so you can easily identify the expression that's enclosed between them. For example, you might want to calculate the cost of your Amazonian adventure holiday based on information like the number of days your trip will last, the price of food and lodging, and the cost for vaccination shots at a travel clinic. If you use cell references, you can enter all this information into different cells, and then write a formula that calculates a grand total. This approach buys you unlimited flexibility, because you can change the cell data whenever you want (for example, turning your three-day getaway into a month-long odyssey), and Excel will refresh the formula results automatically. Cell references are a great way to save a ton of time. They come in handy when you want to create a formula that involves a bunch of widely scattered cells whose values frequently change. For example, rather than manually adding up a bunch of subtotals to create a grand total, you can create a grand total formula that uses cell references to point to a handful of subtotal cells. They also let you refer to large groups of cells by specifying a range. For example, using the cell reference lingo you'll learn in the upcoming Section 7.1.5 (Section 7.1.5), you can specify all the cells in the first column between the 2nd and 100th rows. Every cell reference points to another cell. For example, if you want a reference that points to cell A1 (the cell in column A, row 1), use this cell reference: =A1 In Excel-speak, this reference translates to "get the number from cell A1, and insert it in the current cell." So if you put this formula in cell B1, it displays whatever value is currently in cell A1. In other words, these two cells are now linked. Cell references work within formulas just as regular numbers do. For example, the following formula calculates the sum of two cells, A1 and A2: =A1+A2 Provided both cells contain numbers, you'll see the total appear in the cell that contains the formula. (If one of the cells doesn't contain numeric information, you'll see a special error code instead that starts with a # symbol. Errors are described in more detail on Section 7.1.6.) -------------------------------------------------------------------------------- Note: This chapter focuses on how to perform calculations using cells that contain ordinary numbers. Excel also lets you manipulate other types of content in a formula, like text and dates. You'll learn more about these topics in Chapter 10. -------------------------------------------------------------------------------- 7.1.3. How Excel Formats Cells that Contain Cell References As you learned in Chapter 4, the way you format a cell affects how Excel displays the cell's value. When you create a formula that references other cells, Excel attempts to simplify your life by applying automatic formatting. It reads the number format that the source cells (i.e., the cells being referred to) use, and applies that to the cell with the formula. This means that if you add two numbers and you've formatted both with the Currency number format, your result will also have the Currency number format. Of course, you're always free to change the formatting of the cell after you've entered the formula. Usually, Excel's automatic formatting is quite handy. Like all automatic features, however, it can be a little annoying if you don't understand how it works when it springs into action. Here are a few points to consider: Excel copies only the number format to the formula cell. It ignores other details, like fonts, fill colors, alignment, and so on. (Of course, you can manually copy formats using the Format Painter, as discussed on Section 4.3.3.) If your formula uses more than one cell reference, and the different cells use different number formats, Excel uses its own rules of precedence to decide which number format to use. For example, if you add a cell that uses the Currency number format with one that uses the Scientific number format, the destination cell will have the Scientific number format. Sadly, these rules aren't spelled out anywhere, so if you don't see the result you want, the best approach is just to set your own formatting. If you change the formatting of the source cells after you've entered the formula, it won't have any effect on the formula cell. Excel copies source cell formatting only if the formula cell uses the General number format (which is the format that all cells begin with). If you apply another number format to the cell before you enter the formula, Excel doesn't copy any formatting from the source cells. Similarly, if you change a formula to refer to new source cells, Excel doesn't copy the format information from the new source cells. GEM IN THE ROUGH Excel Is a Pocket Calculator Sometimes you need to calculate a value before you enter it into your worksheet. Before you reach for your pocket calculator, it might interest you to know that Excel lets you enter a formula in a cell, and then use the result in that same cell. This way, the formula disappears and you're left with the result of the calculated value. Start by typing your formula into the cell (for example =65*88). Then, press F2 to put the cell into edit mode. Next, press F9 to perform the calculation. Finally, just hit enter to insert this value into the cell. Remember, when you use this technique, you replace your formula with the calculated value. That means if your calculation is based on the values of other cells, Excel won't update the result if you change those other cells' values. That's the difference between a cell that has a value, and a cell that has a formula. 7.1.4. Functions A good deal of Excel's popularity is due to the collection of functions it provides. Functions are built-in, specialized algorithms that you can incorporate into your own formulas to perform powerful calculations. Functions work like miniature computer programsyou supply the data, and the function performs a calculation and gives you the result. In some cases, functions just simplify calculations that you could probably perform on your own. For example, most people know how to calculate the average of several values, but if you're feeling a bit lazy, Excel's built-in AVERAGE( ) function automatically gives you the average of any cell range. Even more usefully, Excel functions perform feats that you probably wouldn't have a hope of coding on your own, including complex mathematical and statistical calculations, like calculating a best-fit trend line. -------------------------------------------------------------------------------- Tip: You can create your own Excel functions by writing a series of instructions using VBA (Visual Basic for Applications) code. Chapter 26 shows you how. -------------------------------------------------------------------------------- Every function provides a slightly different service. For example, one of Excel's statistical functions is named COMBIN( ). It's a specialized tool used by probability mathematicians to calculate the number of ways a set of items can be combined. Although this sounds technical, even ordinary folks can use COMBIN( ) to get some interesting information. You can use the COMBIN( ) function, for example, to count the number of possible combinations there are in certain games of chance. The following formula uses COMBIN( ) to calculate how many different five-card combinations there are in a standard deck of playing cards: =COMBIN(52,5) -------------------------------------------------------------------------------- Note: Functions alone don't actually do anything in Excel. They need to be part of a formula to produce a result. For example, COMBIN( ) is the name of a function. But it actually does somethingthat is, it gives you a resultonly when you've inserted it into a formula, like so: =COMBIN(52,5). -------------------------------------------------------------------------------- Whether you're using the simplest or the most complicated function, the syntaxor, rules for including a function within a formulais always similar. To use a function, simply enter the function name, followed by parentheses. Then inside the parentheses, put all the information the function needs in order to perform its calculations. In the case of the COMBIN( ) function, two pieces of information, or arguments, are required. The first is the number of items in the set (the 52-card deck), and the second is the number of items you are randomly selecting (in this case, 5). Most functions, like COMBIN( ), require two or three arguments. However, some functions can accept many more, while a few don't need any arguments at all. -------------------------------------------------------------------------------- Note: Excel provides a detailed function reference that you can use to find functions and learn about them. The information Excel provides doesn't make for light reading, though; for the most part, they're written in IRS-speak. You'll learn more about using this reference in the Section 7.2.3 on Section 7.2.3. -------------------------------------------------------------------------------- Once you've typed this formula into a cell, the result (2598960) will appear in your worksheet. In other words, there are 2,598,960 different possible five-card combinations in any deck of cards. Rather than having to calculate this fact using probability theoryor, heaven forbid, trying to count out the possibilities manuallythe COMBIN( ) function handled it for you. UP TO SPEED Learning New Functions This book will introduce you to dozens of new functions. Sometimes you'll start off by looking at a sample formula that uses the function, but for more complex functions, start by considering the function description. The function description assigns a name to each argument. This lets you learn about the type of data the function requires before you start wading into an example with real numbers. For example, here's the function description for the COMBIN( ) function: COMBIN(number_in_set, number_chosen) You can tell the difference between a sample formula and the function description by the fact that the function description is italicized. Also, the function description doesn't include the initial equal sign (=) that you need in all formulas. Sometimes a function takes an optional argument. This means the argument isn't required, but it may be important depending on the behavior you want. Optional arguments are always shown in square brackets. (Excel uses the same convention in its help and formula tooltips.) When you enter a function's arguments, be sure to separate each argument with a comma. Excel guides you in this process by providing a tooltipa small pop-up windowthat presents the name of each argument as soon as it recognizes that you're entering a function. For example, when you type COMBIN and then an open parentheses, Excel displays COMBIN(number, number_chosen) to let you know what information needs to come next. The argument you're currently entering is shown bolded in the tooltip. You don't need to worry about the capitalization of function names, as Excel automatically capitalizes whatever you type in, provided it matches a known function. -------------------------------------------------------------------------------- Note: Even if a function doesn't take any arguments, you still need to supply an empty set of parentheses after the function name. One example is the RAND( ) function, which returns a random fractional number. The formula =RAND( ) works fine, but if you forget the parentheses and merely enter =RAND, Excel displays an error message (#NAME?) that is Excelian for: "Hey! You got the function's name wrong." See Table 7-2 for more information about Excel's error messages. -------------------------------------------------------------------------------- UP TO SPEED Understanding Functions Even though it's relatively easy to understand the basics behind how functions work and how to combine them in a formula, that doesn't mean you'll understand what all functions do and why you should use a particular function. If you don't already know a little probability theory, for instance, the COMBIN( ) function might not be very useful. Excel is packed full of advanced formulas like COMBIN( ) that are tailored for statisticians, accountants, and mathematicians. Most of these you'll never need to use. In this book, functions that'll likely be useful to a non-specialist reader are explained completely. For example, you might not know the financial term net present value, but you'll probably still be interested in using Excel's NPV( ) function to calculate the value of your investments. On the other hand, if you don't know the meaning of a complex conjugatean abstract concept used in some engineering calculationsyou won't be interested in the IMCONJUGATE( ) function. This book won't explain the math behind these more specialized functions. (In fact, properly explaining some of these concepts would require at least a whole other book!) Instead, these functions will be briefly pointed out in a note or table in the relevant chapter. This way, you can easily find these functions if they're relevant to your work and you already know the underlying math or statistical concepts that power them. One of the particularly powerful things about functions is that they don't necessarily need to use literal values in their arguments. They can also use cell references. For example, you could rewrite the five-card combination formula (mentioned above) so that it specifies the number of cards that'll be drawn from the deck based on a number that you've typed in somewhere else in the spreadsheet. Assuming this information is entered into cell B2, the formula would become: =COMBIN(52,B2) More complex formulas combine functions, cell references, and numbers. You might even use multiple functions at once. For example, you can calculate the probability (albeit astronomically low) of getting the exact hand you want in one draw: =1/COMBIN(52,B2) You could even multiply this number by 100 or use the Percent number style to see your percentage chance of getting the cards you want. 7.1.5. Specifying Cell Ranges In many cases, you won't want to refer to just a single cell, but rather a range of cells. A range is simply a grouping of multiple cells. These cells might be next to each other (say, a range that includes all the cells in a single column), or they could be scattered across your worksheet. Ranges are useful for computing averages, totals, and many other calculations. To group together a series of cells, use one of the three following reference operators: The comma (,) separates more than one cell. For example, the series A1, B7, H9 is a cell range that contains three cells. The comma is known as the union operator. You can add spaces before or after a comma, but Excel will just remove them. The colon ( separates the top-left and bottom-right corners of a block of cells. For example, A1:A5 is a range that includes cells A1, A2, A3, A4, and A5. The range A2:B3 is a grid that contains cells A2, A3, B2, and B3. The colon is known as the range operatorby far the most powerful way to select multiple cells. You can add spaces before or after a comma, but Excel will just remove them. The space can find cells that are common to two or more different cell ranges. For example, the expression A1:A3 A1:B10 is a range that consists of only three cells: A1, A2, and A3 (because those three cells are the only ones found on both ranges). The space is technically known as the intersection operator, and it's not used terribly often. -------------------------------------------------------------------------------- Note: As you might expect, Excel lets you specify ranges by selecting cells with your pointer, instead of typing in the range manually. You'll see this later in this chapter in Section 7.2.1 on Section 7.2.1. -------------------------------------------------------------------------------- You can't enter ranges directly into formulas that just use the simple operators. For example, the formula =A1:B1+5, won't work. Instead, you need to use ranges with functions. For instance, one of Excel's most basic functions is named SUM; it calculates the total for a group of cells. To use the SUM( ) function, you must enter its name, an open parenthesis, the cell range you want to add up, and then a closed parenthesis. Here's how you might use the SUM( ) function to add together three cells, A1, A2, and A3: =SUM(A1, A2, A3) And here's a more compact syntax that performs the same calculation using the range operator: =SUM(A1:A3) A similar SUM( ) calculation is shown in Figure 7-3. Clearly, if you want to total a column with hundreds of values, it's far easier to specify the first and last cell using the range operator rather than including each cell reference in your formula! Figure 7-3. Top: Using a cell range as the argument in the SUM( ) function is a quick way to add up a series of numbers in a column. Here's the formula as it's being entered into the cell. Note that when you type the name of a function that Excel recognizes, it automatically displays a tooltip indicating what arguments the function requires. If you're entering a range, as in this example, you'll also see that Excel highlights the cells within the range (C2, C3, and C4) in a blue border. Bottom: Hitting Enter completes the formula and displays the result. -------------------------------------------------------------------------------- Tip: Sometimes your worksheet might have a list with unlimited growth potential, like a list of expenses or a catalog of products. In this case, you can code your formulas to include an entire column by leaving out the row number. For example, the range A:A selects all the cells in column A (and the range 2:2 selects all the cells in row 2). This range also includes any heading cells, which isn't a problem for the SUM( ) function (because it ignores text cells). If you don't want to include the top cell, just create a normal range that stretches to the last cell. For example, to select every cell in column A except for the first one, use the range A2:A65536. -------------------------------------------------------------------------------- 7.1.6. Formula Errors If you make a syntax mistake when entering a formula (such as leaving out a function argument or including a mismatched number of parentheses), Excel alerts you immediately. Moreover, Excel won't accept the formula until you've corrected it. It's also possible, though, to write a perfectly legitimate formula that doesn't return a valid answer. Here's an example: =A1/A2 If both A1 and A2 have numbers, this formula works without a hitch. However, if you leave A2 blank, or if you enter text instead of numbers, Excel won't be able to evaluate the formula, and it reminds you with an error message. Excel alerts you about formula errors by using special error codes which begin with the number sign (#), as depicted in Figure 7-4. In order to remove this error, you need to track down the problem and resolve it, which may mean correcting the formula or changing the cells it references. Figure 7-4. If Excel spots an error, it'll insert a tiny green triangle into the top-left corner of the cell. When you move to the offending cell, Excel displays an exclamation mark next to it. Hover over the exclamation mark to view a description of the error (which appears in a tooltip), or click the exclamation icon to see a list of menu options. Using the menu, you can jump to Excel's online help, or you can tell Excel to ignore the error, in which case the error code remains but the green triangle disappears. -------------------------------------------------------------------------------- Tip: Sometimes a problem isn't an error, but simply the result of data that hasn't yet been entered. In this case, you can solve the problem by using a conditional error-trapping formula. This conditional formula will check if the data is present, and it will only perform the calculation if it is. The next section, Section 7.1.7 shows one way to use an error-trapping formula. -------------------------------------------------------------------------------- Table 7-2 lists the error codes that Excel uses. Table 7-2. Excel's Error Codes Error Code Description #VALUE! You used the wrong type of data. Maybe your function expects a single value and you submitted a whole range. Or, more commonly, you might have used a function or created a simple arithmetic formula with a cell that contains text instead of numbers. #NAME? Excel can't find the name of the function you used. This usually means you misspelled a function's name, although it might indicate you used text without quotation marks or left out the empty parentheses after the function name. (You'll see how you can use text in a formula in Chapter 10.) #NUM! There's a problem with one of the numbers you're using. For example, this appears if a calculation produces a number that's too large or too small for Excel to deal with. #DIV/0 You tried to divide by zero. This also occurs if you try to divide by a cell that's blank, because Excel treats a blank cell as though it contains the number 0 for the purpose of simple calculations with the arithmetic operators. (Some functions, like AVERAGE( ), are a little more intelligent and ignore blank cells.) #REF! Your cell reference is invalid. This most often occurs if you delete or paste over the cells you were using, or if you try to copy a cell from one worksheet to another. (For information about creating formulas that span worksheets and workbooks, refer to Chapter 12.) #N/A The value is not available. This can occur if you try to perform certain types of lookup or statistical functions that work with cell ranges. For example, if you use a function to search a range and it can't find what you need, you might get this result. (You'll learn about lookup functions in Chapter 11.) Sometimes people enter a #N/A value manually in order to tell Excel to ignore a particular cell when creating charts and graphs. The easiest way to do this is to use the NA( ) function (rather than entering the text #N/A). #NULL! You used the intersection operator incorrectly. Remember, the intersection operator finds cells that two ranges share in common. This error results if there are no cells in common. Oftentimes, people use the intersection operator by accident, as the operator is just a single space character. ######## This isn't actually an error conditionin all likelihood, Excel has successfully calculated your formula. However, the formula can't be displayed in the cell using the current number format. To solve this problem, you can widen the column, or possibly change the number format (Section 4.1.1) if you require a certain number of fixed decimal places. -------------------------------------------------------------------------------- Note: Chapter 12 describes a collection of Excel tools designed to help you track down the source of an error in a complex formulaespecially one where the problem might not be immediately obvious. -------------------------------------------------------------------------------- TROUBLESHOOTING MOMENT Circular References One of the more aggravating problems that can occur with formulas is the infamous circular reference. A circular reference occurs when you create a formula that depends, indirectly or directly, on its own value. For example, consider what happens if you enter the following formula in cell B1. =B1+10 In order for this formula to work, Excel would need to take the current B1 value, and add 10. However, this operation changes the value of B1, which means Excel needs to recalculate the formula. If unchecked, this process would continue in an endless loop without ever producing a value. More subtle forms of circular references are possible. For example, you might create a formula in one cell that refers to a cell in another cell that refers back to the original cell. This is what's known as an indirect circular reference, but the problem is the same. Ordinarily, Excel won't allow circular references. If you enter a formula that contains a circular reference, Excel displays an error message and forces you to edit the formula until you've removed the circular reference. However, you can configure Excel to allow circular references by modifying the calculation settings, as described on Section 12.4. In this case, Excel will repeat the loop a fixed number of times, or until the value seems to settle down and stop changing. Occasionally, this technique is useful for calculating certain types of approximations in advanced formulas. In most cases, though, this approach is rather dangerous, because it means you won't catch accidental circular references, which can lead to invalid data. A better approach is to write a custom function that performs a calculation in a loop using VBA (Visual Basic for Applications), as described in Chapter 26. 7.1.7. Logical Operators So far, you've seen the basic arithmetic operators (which are used for addition, subtraction, division, and so on) and the cell reference operators (used to specify one or more cells). There's one final category of operators that's useful when creating formulas: logical operators. Logical operators let you build conditions into your formulas so the formulas produce different values depending on the value of the data they encounter. You can use a condition with cell references or literal values. For example, the condition A2=A4 is true if cell A2 contains the same content as cell A4. On the other hand, if these cells contain different values (say 2 and 3) the formula generates a false value. Using conditions is a stepping stone to using conditional logic. Conditional logic lets you perform different calculations based on different scenarios. For example, you might use conditional logic to see how large an order is, and provide a discount if the total order cost is over $5,000. Excel evaluates the condition, meaning it determines if the condition is true or false. You can then tell Excel what to do based on that evaluation. Table 7-3 lists all the logical operators you can use to build formulas. Table 7-3. Logical Operators Operator Name Example Result = Equal to 1=2 FALSE > Greater than 1>2 FALSE < Less than 1<2 TRUE >= Greater than or equal to 1>=1 TRUE <= Less than or equal to 1<=1 TRUE <> Not equal to 1<>1 FALSE You can use logical operators to build standalone formulas, but that's not particularly useful. For example, here's a formula that tests whether cell A1 contains the number 3: =(A2=3) The parentheses aren't actually required, but they make the formula a little bit clearer, emphasizing the fact that Excel evaluates the condition first, and then displays the result in the cell. If you type this formula into the cell, you'll see either the uppercase word TRUE or FALSE, depending on the content in cell A2. On their own, logical operators don't accomplish much. However, they really shine when you start combining them with other functions to build conditional logic. For example, you can use the SUMIF( ) function, which totals the value of certain rows, depending on whether the row matches a set condition. Or you can use the IF( ) function to determine what calculation you should perform. The IF( ) function has the following function description: IF(condition, [value_if_true], [value_if_false]) In English, this line of code translates to: if the condition is true, the second argument is displayed in the cell; if the condition is false, the third argument is displayed. Consider this formula: =IF(A1=B2, "These numbers are equal", "These numbers are not equal") This formula tests if the value in cell A1 equals the value in cell B2. If this is true, you'll see the message "These numbers are equal" displayed in the cell. Otherwise, you'll see the text "These numbers are not equal." -------------------------------------------------------------------------------- Note: If you see a quotation mark in a formula, it's because that formula uses text. You must surround all literal text values with quotation marks. Literal numbers, on the hand, you can enter directly into a formula. -------------------------------------------------------------------------------- The IF( ) function is often used to prevent Excel from performing a calculation if some of the data is missing. Consider the following formula: =A1/A2 This formula will cause a divide-by-zero error if A2 contains a 0 value. Excel will then display an error code in the cell. To prevent this from occurring, you can replace this formula with the conditional formula shown here: =IF(A2=0, 0, A1/A2) This formula checks if cell A2 is empty or contains a 0. If so, the condition is true, and the formula simply gives you a 0. If it isn't, the condition is false, and Excel performs the calculation A1/A2.

[cccccc55]

thaaaaaaaaaaaaaaaaaaaanks

[alaaaboulela]

تسلم الأيادي

[اسامة عمر]

حياك حياك حياك يابطل الاكسل

[amr_abdo11]

شكرا علي المجهود الرائع هكذا تعلمنا منك العطاء

[eslamlord]

شكرا لمساعدة

[عماد الرعد]

مشكووووووووووووور

[ramies]

مشكور اخى على المجهود بس لى عتاب بسيط

لماذا الشرح بالانجليزى فى منتدي عربي 100 %

[رضوة]

مشكور يعطيك الصحة والعافية