| | كاتب الموضوع | رسالة |
---|
soubhi المدير العام
عدد المساهمات : 443 تاريخ التسجيل : 07/05/2012 العمر : 52
| موضوع: excel.............excel 10/5/2012, 7:30 pm | |
| Multiply numbers in different cells by using a formula To do this task, use the asterisk (*) operator or the PRODUCT function. Example:The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula
Auditing Mode.
A 1 Data 2 5 3 15 4 30 Formula Description (Result) =A2*A3 Multiplies the numbers in the first two cells (75) =PRODUCT(A2:A4) Multiplies all the numbers in the range (2250) =PRODUCT(A2:A4,2) Multiplies all the numbers in the range, and 2 (4500)
Subtract numbers in a cell To do this task, use the - (minus sign) arithmetic operator. For example, if you type the following formula in a cell: 10-5 The cell displays the following result: 5 Subtract numbers in a range To do this task, use the SUM function. Adding a negative number is the same as subtracting.
Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 A Data 15,000 9,000 -8,000 Formula Description (Result) =A2-A3 Subtracts 9,000 from 15,000 (6,000) =SUM(A2:A4) Adds all numbers in the list, including negative numbers (16,000)
Divide numbers in a cell To do this task, use the / (forward slash) arithmetic operator. For example, if you type the following formula in a cell:=10/5 The cell displays the following result: 2 Divide cells in a range Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
1 2 3 A Data 15,000 12 Formula Description (Result) =A2/A3 Divides 15,000 by 12 (1250)
SUMIF Applies to: Microsoft Office Excel 2003 Adds the cells specified by a given criteria. Syntax SUMIF(range,criteria,sum_range) Range is the range of cells that you want evaluated by criteria. 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", or "apples". Sum_range are the actual cells to add if their corresponding cells in range match criteria. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria. Remarks Sum_range does not have to be the same size and shape as range. The actual cells that are added are determined by using the top, left cell in sum_range as the beginning cell, and then including cells that correspond in size and shape to range. For example: If range is And sum_range is Then the actual cells are A1:A5 B1:B5 B1:B5 A1:A5 B1:B3 B1:B5 A1:B4 C1:D4 C1:D4 A1:B4 C1:C2 C1:D4 You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character. Microsoft Office Excel provides additional functions that you can use to analyze your data based on a condition or criteria: To count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF function. To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF function. To analyze data in a list based on criteria, such as profit margins or product types, use the database and list management functions (DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, and DVARP). Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example A B Property Value Commission 100,000 7,000 200,000 14,000 300,000 21,000 400,000 28,000 =SUMIF(A2:A5,">160000",B2:B5) Sum of the commissions for property values over 160000 (63,000) =SUMIF(A2:A5,">160000") Sum of the property values over 160000 (900,000) =SUMIF(A2:A5,"=300000",B2:B3) Sum of the commissions for property values over 160000 (21,000)
Create conditional formulas Applies to: Microsoft Office Excel 2003 Testing whether conditions are true or false and making logical comparisons between expressions are common to many tasks. You can use the AND, OR, NOT, and IF function to create conditional formulas. The IF function uses the following arguments. Formula with the IF function logical_test: The condition that you want to check. value_if_true: The value to return if the condition is true. value_if_false: The value to return if the condition is false.
What do you want to do? Create a conditional formula that results in a logical value (TRUE or FALSE) Create a conditional formula that results in another calculation or in values other than TRUE or FALSE Create a conditional formula that results in a logical value (TRUE or FALSE) To do this task, use the AND, OR, and NOT functions, and operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.). Example The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers.Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 A Data 15 9 8 Sprockets Widgets Formula Description (Result) =AND(A2>A3, A2<A4) Is 15 greater than 9 and less than 8? (FALSE) =OR(A2>A3, A2<A4) Is 15 greater than 9 or less than 8? (TRUE) =NOT(A2+A3=24) Is 15 plus 9 not equal to 24? (FALSE) =NOT(A5="Sprockets") Is A5 not equal to "Sprockets"? (FALSE) =OR(A5<>"Sprockets",A6 = "Widgets") Is A5 not equal to "Sprockets" or A6 equal to "Widgets"? (TRUE)
Function details AND OR NOT Create a conditional formula that results in another calculation or in values other than TRUE or FALSE To do this task, use the IF, AND, and OR functions. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
1 2 3 4 A Data 15 9 8 Sprockets Widgets Formula Description (Result) =IF(A2=15, "OK", "Not OK") If the value in cell A2 equals 15, then return "OK". (OK) =IF(A2<>15, "OK", "Not OK") If the value in cell A2 is not equal to 15, then return "OK". (Not OK) =IF(NOT(A2<=15), "OK", "Not OK") If the value in cell A2 is not less than or equal to 15, then return "OK". (Not OK) =IF(A5<>"SPROCKETS", "OK", "Not OK") If the value in cell A5 is not equal to "SPROCKETS", then return "OK". (Not OK) =IF(AND(A2>A3, A2<A4), "OK", "Not OK") If 15 is greater than 9 and less than 8, then return "OK". (Not OK) =IF(AND(A2<>A3, A2<>A4), "OK", "Not OK") If 15 is not equal to 9 and 15 is not equal to 8, then return "OK". (OK) =IF(OR(A2>A3, A2<A4), "OK", "Not OK") If 15 is greater than 9 or less than 8, then return "OK". (OK) =IF(OR(A5<>"Sprockets", A6<>"Widgets"), "OK", "Not OK") If the value in cell A5 is not equal to "Sprockets" or "Widgets", then return "OK". (Not OK) =IF(OR(A2<>A3, A2<>A4), "OK", "Not OK") If 15 is not equal to 9 or 15 is not equal to 8, then return "OK". (OK)
Function details IF AND OR Check if a number is greater than or less than another number Applies to: Microsoft Office Excel 2003 Let's say you want to determine whether a salesperson sold more this year over last year so that you can calculate an appropriate bonus, or one warehouse contains more of an item than another warehouse in order to keep inventory levels balanced. To check if a number is greater than or less than another number, use the IF function. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers.Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
1 2 3 4 A Data 15,000 9,000 8,000 Formula Description (Result) =A2>A3 Is A2 greater than number in A3? (TRUE) =IF(A3<=A4, "OK", "Not OK") Is A3 less than or equal to the number in A4? (Not OK)
Function details IF Look up values in a list of data Applies to: Microsoft Office Excel 2003 Let's say you want to look up an employee's phone extension by using their badge number, or the correct rate of a commission for a sales amount. You look up data to quickly and efficiently find specific data in a list and to automatically verify that you are using correct data. After you look up the data, you can perform calculations or display results with the values returned. There are several ways to look up values in a list of data and to display the results. What do you want to do?
Use the VLOOKUP and HLOOKUP functions Look up values vertically in a list by using an exact match Look up values vertically in a list by using an approximate match Look up values horizontally in a list by using an exact match Look up values horizontally in a list by using an approximate match Use the INDEX, MATCH, and OFFSET functions Create a lookup formula with the Lookup Wizard Look up values vertically in a list by using an exact match Look up values vertically in a list of unknown size by using an exact match Look up values vertically in a list by using an exact match To do this task, use the VLOOKUP function The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers .selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 5 6 7 A B C D Badge Number Last Name First Name Extension ID-34567 Davolio Nancy 5467 ID-16782 Fuller Andrew 3457 ID-4537 Leverling Janet 3355 ID-1873 Peacock Margaret 5176 ID-3456 Buchanan Steven 3453 ID-5678 Suyama Michael 428 Formula Description (Result) =VLOOKUP("ID-4537", A1:D7, 4, FALSE) Lookup the badge number, ID-4537, in the first column and return the matching value in the same row of the fourth column (3355)
Function details Look up values vertically in a list by using an approximate match To do this task, use the VLOOKUP function. Important This method only works if the values in the first column have been sorted in ascending order. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic.
Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. In this example, you know the frequency and want to look up the associated color. 1 2 3 4 5 6 A B Frequency Color 4.14 red 4.19 orange 5.17 yellow 5.77 green 6.39 blue Formula Description (Result) =VLOOKUP(5.93, A1:B6, 2, TRUE) Looks up 5.93 in column A, finds the next largest value that is less than 5.93, which is 5.77, and then returns the value from column B that's in the same row as 5.77 (green)
Function details Look up values horizontally in a list by using an exact match To do this task, use the HLOOKUP function.
Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 A B C Status Axles Bolts In stock 4 9 On order 5 10 Back order 6 11 Formula Description (Result) =HLOOKUP("Bolts", A1:C4, 3) Looks up Bolts in row 1, and returns the value from row 3 that's in the same column (10)
Function details Look up values horizontally in a list by using an approximate match To do this task, use the HLOOKUP function. Important This method only works if the values in the first row have been sorted in ascending order. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers.Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 A B C D 10000 50000 100000 Sales Volume .05 .20 .30 Rate Formula Description (Result) =HLOOKUP(78658,A1:D4,2, TRUE) Looks up $78,658 in Row 1, finds the next largest value that is less than $78,658, which is $50,000, and then returns the value from row 2 that's in the same column as $50,000 (20%)
Notes To display the rate and return number as a percentage, select the cell and then click Percent Style on the Formatting toolbar. To display the Sales Volume number as dollars, select the cell and then click Dollar Style on the Formatting toolbar. Function details Create a lookup formula with the Lookup Wizard The Lookup Wizard creates the lookup formula based on a worksheet data that has row and column labels. The Lookup Wizard helps you find other values in a row when you know the value in one column, and vice versa. The Lookup Wizard uses INDEX and MATCH in the formulas that it creates. 1. On the Tools menu, click Add-ins, select the Lookup Wizard box, and then click OK. 2. Click a cell in the range. 3. On the Tools menu, click Lookup. 4. Follow the instructions in the wizard. Look up values vertically in a list by using an exact match To do this task, use the INDEX and MATCH functions. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. 3. Note Do not select the row or column headers .Selecting an example from Help 4. Press CTRL+C. 5. In the worksheet, select cell A1, and press CTRL+V. 6. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 5 A B Product Count Bananas 38 Oranges 25 Apples 41 Pears 40 Formula Description (Result) =INDEX(A2:B5,MATCH("Pears",A2:A5,0),2) Looks up Pears in column A and returns the value for Pears in column B (40).
The formula uses the following arguments. Formula to look up a value in an unsorted range (INDEX function) A2:B5: The entire range in which you are looking up values. MATCH("Pears",A2:A5,0): The MATCH function determines the row number. "Pears": The value to find in the lookup column. A2:A5: The column for the MATCH function to search. 2: The column from which to return the value. The leftmost column is 1. Function details INDEX MATCH Look up values vertically in a list of unknown size by using an exact match To do this task, use the OFFSET and MATCH functions. Use this approach when your data is in an external data range that you refresh each day. You know the price is in column B, but you don't know how many rows of data the server will return, and the first column isn't sorted alphabetically .Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
1 2 3 4 5 A B Product Count Bananas 38 Oranges 25 Apples 41 Pears 40 Formula Description (Result) =OFFSET(A1,MATCH("Pears",A2:A5, 0),1) Looks up Pears in column A and returns the value for Pears in column B ( 40).
The formula uses the following arguments. A1: The upper left cell of the range, also called the starting cell. MATCH("Pears",A2:A5, 0): The MATCH function determines the row number below the starting cell to find the look up value. "Pears": The value to find in the lookup column. A2:A5: The column for the MATCH function to search. Don't include the starting cell in this range. 1: The number of columns to the right of the starting cell to find the lookup value.
Function details MATCH OFFSET
Add dates Applies to: Microsoft Office Excel 2003 Let's say you want to adjust a revised schedule date by adding two weeks to see what the new completion date will be, or you need to determine how long an activity will take to complete in a list of project tasks. There are several ways to add dates. What do you want to do? Add a number of days to a date Add a number of months to a date Add a number of years to a date Add a combination of days, months, and years to a date Add a number of days to a date To do this task, use the addition (+) operator. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 A B Date Days to add 6/9/2007 3 =TODAY() 5 12/10/2008 54 Formula Description (Result) =A2+B2 Add 3 days to 6/9/2007 (6/12/2007) =A3+B3 Add 5 days to the current day (varies) =A4+B4 Add 54 days to 12/10/2008 (2/2/2009)
Add a number of months to a date To do this task, use the DATE, YEAR, MONTH, and DAY functions. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 A B Date Months to add 6/9/2007 3 9/2/2007 5 12/10/2008 25 Formula Description (Result) =DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2)) Add 3 months to 6/9/2007 (9/9/2007) =DATE(YEAR(A3),MONTH(A3)+B3,DAY(A3)) Add 5 months to 9/2/2007 (2/2/2008) =DATE(YEAR(A4),MONTH(A4)+B4,DAY(A4)) Add 25 months to 12/10/2008 (1/10/2011)
Function details DATE YEAR MONTH DAY Add a number of years to a date To do this task, use the DATE, YEAR, MONTH, and DAY functions. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 A B Date Years to add 6/9/2007 3 9/2/2007 5 12/10/2008 25 Formula Description (Result) =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2)) Add 3 years to 6/9/2007 (6/9/2010) =DATE(YEAR(A3)+B3,MONTH(A3),DAY(A3)) Add 5 years to 9/2/2007 (9/2/2012) =DATE(YEAR(A4)+B4,MONTH(A4),DAY(A4)) Add 25 years to 12/10/2008 (12/10/2033)
Function details DATE YEAR MONTH DAY Add a combination of days, months, and years to a date To do this task, use the DATE, YEAR, MONTH, and DAY functions. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers.Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
1 2 A Date 6/9/2007 Formula Description (Result) =DATE(YEAR(A2)+3,MONTH(A2)+1,DAY(A2)+5) Add 3 years, 1 month, and 5 days to 6/9/2007 (7/14/2010) =DATE(YEAR(A2)+1,MONTH(A2)+7,DAY(A2)+5) Add 1 year, 7 months, and 5 days to 6/9/2007 (1/14/2009)
The formula above has the following arguments.
Formula to add dates start_ date: A date or reference to a cell that contains a date. add_ year: The number of years to be added. add_ month: The number of months to be added. add_ day : The number of days to be added. Function details DATE YEAR MONTH DAY Show dates as days of the week Applies to: Microsoft Office Excel 2003 Let's say you want to see the date displayed for a date value in a cell as "Monday" instead of the actual date of "October 3, 2005." There are several ways to show dates as days of the week. What do you want to do? Format cells to show dates as the day of the week Convert dates to the text for the day of the week Format cells to show dates as the day of the week 1. Select the cells that contain dates that you want to show as the days of the week. 2. On the Format menu, click Cells, and then click the Number tab. 3. Under Category, click Custom, and in the Type box, type dddd for the full name of the day of the week (Monday, Tuesday, and so on), or ddd for the abbreviated name of the day of the week (Mon, Tue, Wed, and so on). 4. Convert dates to the text for the day of the week To do this task, use the TEXT function. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic.
Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 A Date 19-Feb-2007 3-Jan-2008 Formula Description (Result) =TEXT(A2, "dddd") Calculates the day of the week for the date and returns the full name of the day of the week (Monday) =TEXT(A3, "ddd") Calculates the day of the week for the date and returns the abbreviated name of the day of the week (Thu)
Function details TEXT Calculate a running balance Applies to: Microsoft Office Excel 2003 Let's say you may want to see a running balance of items that you purchase so that you don't exceed your spending limits. You can use a running balance to watch values of items in cells add up as you enter new items and values over time. To calculate a running balance, use the following procedure. 1. Set up a worksheet like the following example. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 A B C Deposits Withdrawals Balance $1,000 $625 =SUM(A2,-B2) 1000 740 =SUM(C2,A3,-B3)
2. Click anywhere outside cell C3 to see the calculated total. 3. To maintain the running balance, add a row for each new entry by doing the following: Type the amounts of your deposits and withdrawals into the empty rows directly below the existing data. For instance, if you were using the example above, you would type the deposits into A4, A5, and so on, and the withdrawals into B4, B5, and so on. Extend the running balance formula into the new rows by selecting the last cell in the balance column and then double-clicking the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.). For instance, if you were using the example above, you would select cell C3 and then double-click its fill handle to extend the formula into all new rows that contain deposit and withdrawal values. Note A running balance differs from a running total (also called a running count), in which you watch the number of items in a worksheet add up as you enter new items. For more information, see Calculate a running total Calculate a compound annual growth rate (CAGR) Applies to: Microsoft Office Excel 2003
A compound annual growth rate (CAGR) measures the rate of return for an investment — such as a mutual fund or bond — over an investment period, such as 5 or 10 years. The CAGR is also called a "smoothed" rate of return because it measures the growth of an investment as if it had grown at a steady rate on an annually compounded basis. To calculate a CAGR, use the XIRR function. If the XIRR function is not available and returns the #NAME? error, install and load the Analysis ToolPak add-in. How? 1. On the Tools menu, click Add-Ins. 2. In the Add-Ins available list, select the Analysis ToolPak check box, and then click OK. 3. If necessary, follow the instructions in the setup program.
Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 5 6 A B Values Dates -10,000 January 1, 2008 2,750 March 1, 2008 4,250 October 30, 2008 3,250 February 15, 2009 2,750 April 1, 2009 Formula Description (Result) =XIRR(A2:A6,B2:B6) The compound annual growth rate (0.373362535 or 37.34%)
Notes When you compare the CAGRs of different investments, make sure that each rate is calculated over the same investment period. To view the number as a percentage, select the cell and then click Cells on the Format menu. Click the Number tab, and then click Percentage in the Category box. Function details XIRR Calculate the average of a group of numbers Applies to: Microsoft Office Excel 2003 Let's say you want to find the average number of days to complete a milestone in a project or the average temperature on a particular day over a 10-year time span. There are several ways to calculate the average of a group of numbers. The AVERAGE function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are: Average which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5. Median which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4. Mode which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different. What do you want to do? Calculate the average of numbers in a contiguous row or column Calculate the average of numbers not in a contiguous row or column Calculate a weighted average Calculate the average of numbers, ignoring zero (0) values
Calculate the average of numbers in a contiguous row or column 1. Click a cell below or to the right of the numbers for which you want to find the average. 2. Click the arrow next to AutoSum on the Standard toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), and then click Average and press ENTER.
Calculate the average of numbers not in a contiguous row or column To do this task, use the AVERAGE function. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
1 2 3 4 5 6 7 A Data 10 7 9 27 0 4 Formula Description (Result) =AVERAGE(A2:A7) Averages all of numbers in list above (9.5) =AVERAGE(A2:A4,A7) Averages the top three and the last number in the list (7.5) =AVERAGE(IF(A2:A7<>0, A2:A7,"")) Averages the numbers in the list except those that contain zero, such as cell A6 (11.4)
Note The last formula in the example must be entered as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). After copying the example to a blank worksheet, select the cell A11. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.
Function details AVERAGE Calculate a weighted average To do this task, use the SUMPRODUCT and SUM functions. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. This example calculates the average price paid for a unit across three purchases, where each purchase is for a different number of units at a different price per unit.
1 2 3 4 A B Price per unit Number of units 20 500 25 750 35 200 Formula Description (Result) =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4) Divides the total cost of all three orders by the total number of units ordered (24.66)
Function details SUM SUMPRODUCT Calculate the average of numbers, ignoring zero (0) values .To do this task, use the AVERAGE and IF functions. Example: The example may be easier to understand if you copy it to a blank worksheet. How to copy an example1.Create a blank workbook or worksheet. 2Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 1. Press CTRL+C. 2. In the worksheet, select cell A1, and press CTRL+V. 3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 5 6 7 A Data 10 7 9 27 0 4 Formula Description (Result) =AVERAGE(IF(A2:A7<>0, A2:A7,"")) Averages the numbers in the list except those that contain zero, such as cell A6 (11.4)
Note The formula in the example must be entered as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). After copying the example to a blank worksheet, select the cell A9. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned. Function details AVERAGE IF Round a number Applies to: Microsoft Office Excel 2003 Let's say you want to round a number to the nearest whole number because decimal values are not significant to you or round a number to multiples of 10 to simplify an approximation of amounts. There are several ways to round a number. What do you want to do? Change the number of decimal places displayed, without changing the number Round a number up Round a number down Round a number to the nearest number Round a number to a near fraction Round a number to a significant digit above 0 Round a number to a specified multiple Change the number of decimal places displayed, without changing the number On a worksheet 1. Select the cells that you want to format. 2. To display more or fewer digits after the decimal point, click Increase Decimal or Decrease Decimal on the Formatting toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.). In a built-in number format 1. On the Format menu, click Cells, and then click the Number tab. 2. In the Category list, click Currency, Accounting, Percentage, or Scientific. 3. In the Decimal places box, enter the number of decimal places that you want to display.
Round a number up To do this task, use the ROUNDUP, EVEN, or ODD functions. Example The example may be easier to understand if you copy it to a blank worksheet. How to copy an example 1. Create a blank workbook or worksheet. 2. Select the example in the Help topic. Note Do not select the row or column headers. Selecting an example from Help 3. Press CTRL+C. 4. In the worksheet, select cell A1, and press CTRL+V. 5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
| | | |
| صلاحيات هذا المنتدى: | لاتستطيع الرد على المواضيع في هذا المنتدى
| |
| |
| |