|
| | كاتب الموضوع | رسالة |
|---|
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.
|
|  | | |
| | صلاحيات هذا المنتدى: | لاتستطيع الرد على المواضيع في هذا المنتدى
| |
| |
| |