Analyzing Data Using Formulas B
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Analyzing Data Using Formulas B as PDF for free.
More details
- Words: 832
- Pages: 13
Design by : izahusni
MICROSOFT EXCEL 2007 TRAINING Analyzing Data Using Formulas Topic:E. F. G. H.
Construct formulas using named ranges Build a logical formula with IF function Build a logical formula with AND function Calculate payments with the PMT function
Objective:Formulas and function help you to analyze worksheet data. As you learn how to use different types of formulas and functions, you will discover more valuable uses for Excel.
1
Design by : izahusni
E. Constructing Formulas Using named Ranges. To make your worksheet easier to follow, you can assign names to cells and ranges. You can also use names in formulas to make them easier to build and reduce formula errors. For example, a formula named revenue-cost is easier to understand than the formula A5-A8. Names can use uppercase or lowercase letters as well as digits, but cannot have spaces. After you name a cell or range, you can define its scope or the worksheet where it can be used. 1. Open the file.xlsx from the drive and folder where you store your Data Files.
2. Click cell B4,click the Formulas tab if necessary, click the Define Name button in the Defined Names group.
3. Type current_date in the Name text box,click the Scope list arrow,click Jan Orders,then click OK.
2
Design by : izahusni
4.
Select the range B7:B12,click the Define Name button in the Defined Names group.
Enter order_date in the Name text box,click the Scope list arrow,click Jan Orders,then click OK.
5. Click cell C7,type = ,click the Use in Formula button in the Defined Names group,click order_date, type -.
3
Design by : izahusni
6. Click the Use in Formula button,click current_date,then click the Enter button on the formula bar.
7. The formulas will appear as below.
8. The result as shown below.
4
Design by : izahusni
9. Drag the fill handle to copy the formula in cell C7 into the range C8:C13.
10. Save the workbook.
F. Building a Logical Formula with the IF Function. You can build a logical formula using an IF function. A Logical Formula makes calculations based on criteria that you create,called stated conditions. For example, you can build a formula to calculate bonuses based on a person’s performance rating. 1. Click cell F7,on the Formulas tab,click the Logical button in the Function Library group,then click IF.
5
Design by : izahusni
2. With the insertion point in the Logical_test box,click cell D7,type >,click cell E7,then press [Tab].
3. With the insertion point in the Value_if_false text box,then click OK.
6
Design by : izahusni
4. Enter None in the Value_if_false text box,then click OK.
5. Drag the fill handle to copy the formula in cell F7 into the range F8:F12.
6. Save the workbook. 7
Design by : izahusni
G. Building a Logical Formula with the AND function. You can also build a logical function using the AND function. The AND function evaluates all of its arguments and returns or displays, TRUE if every logical test in the formula is true. The AND function returns a value of FALSE if one or more of its logical tests is false. The AND function arguments can include text, numbers or cell references. 1. Click cell C7,click the Logical button in the Function Library Group,then click AND.
2. With the insertion point in the Logical1 text box,click cell F7,type<>,type”None”,then press [Tab].
8
Design by : izahusni 3. With the insertion point in the Logical2 text box,click cell C7,type <21,then click OK.
The function is complete,and the result,TRUE,appears in cell G7.
4. Drag the fill handle to copy the formula in cell G7 into the range G8:G12.
5. Save the workbook. 9
Design by : izahusni
H. Calculating Payments with the PMT function. PMT is a financial function that calculates the periodic payment amount for money borrowed. For example,if you want to borrow money to buy a car,and you know the principal amount,interest rate,and loan term,the PMT function can calculate your monthly payment. 1. Click the Loan sheet tab,click cell F5,click the Formulas tab,click the Financial button in the Function Library group,scroll down the list of functions,then click PMT.
2. With the insertion point in the Rate text box,click cell D5 on the worksheet,type /12,then press [Tab].
10
Design by : izahusni
3. With the insertion point in the Nper text box,click cell E5,click the Pv text box,click cell B5,then click OK.
4. Double click cell F5 and edit it so it reads = PMT (D5/12,E5,-B5) ,then click the Enter button on the formula bar. 11
Design by : izahusni
5. With cell F5 selected,drag the fill handle to fill the range F6:F7.
6. Click cell G5,type =,click cell E5,type *,click cell F5,then press [Tab].
7. In cell H5,type =,click cell G5,type -.
8. Click cell B5,then click . 12
Design by : izahusni
9. The result as shown below.
10. Copy the formulas in cells G5:H5 into the range G6:H7,then click cell A1.
11. Save the workbook.
Good Luck!!
13
MICROSOFT EXCEL 2007 TRAINING Analyzing Data Using Formulas Topic:E. F. G. H.
Construct formulas using named ranges Build a logical formula with IF function Build a logical formula with AND function Calculate payments with the PMT function
Objective:Formulas and function help you to analyze worksheet data. As you learn how to use different types of formulas and functions, you will discover more valuable uses for Excel.
1
Design by : izahusni
E. Constructing Formulas Using named Ranges. To make your worksheet easier to follow, you can assign names to cells and ranges. You can also use names in formulas to make them easier to build and reduce formula errors. For example, a formula named revenue-cost is easier to understand than the formula A5-A8. Names can use uppercase or lowercase letters as well as digits, but cannot have spaces. After you name a cell or range, you can define its scope or the worksheet where it can be used. 1. Open the file.xlsx from the drive and folder where you store your Data Files.
2. Click cell B4,click the Formulas tab if necessary, click the Define Name button in the Defined Names group.
3. Type current_date in the Name text box,click the Scope list arrow,click Jan Orders,then click OK.
2
Design by : izahusni
4.
Select the range B7:B12,click the Define Name button in the Defined Names group.
Enter order_date in the Name text box,click the Scope list arrow,click Jan Orders,then click OK.
5. Click cell C7,type = ,click the Use in Formula button in the Defined Names group,click order_date, type -.
3
Design by : izahusni
6. Click the Use in Formula button,click current_date,then click the Enter button on the formula bar.
7. The formulas will appear as below.
8. The result as shown below.
4
Design by : izahusni
9. Drag the fill handle to copy the formula in cell C7 into the range C8:C13.
10. Save the workbook.
F. Building a Logical Formula with the IF Function. You can build a logical formula using an IF function. A Logical Formula makes calculations based on criteria that you create,called stated conditions. For example, you can build a formula to calculate bonuses based on a person’s performance rating. 1. Click cell F7,on the Formulas tab,click the Logical button in the Function Library group,then click IF.
5
Design by : izahusni
2. With the insertion point in the Logical_test box,click cell D7,type >,click cell E7,then press [Tab].
3. With the insertion point in the Value_if_false text box,then click OK.
6
Design by : izahusni
4. Enter None in the Value_if_false text box,then click OK.
5. Drag the fill handle to copy the formula in cell F7 into the range F8:F12.
6. Save the workbook. 7
Design by : izahusni
G. Building a Logical Formula with the AND function. You can also build a logical function using the AND function. The AND function evaluates all of its arguments and returns or displays, TRUE if every logical test in the formula is true. The AND function returns a value of FALSE if one or more of its logical tests is false. The AND function arguments can include text, numbers or cell references. 1. Click cell C7,click the Logical button in the Function Library Group,then click AND.
2. With the insertion point in the Logical1 text box,click cell F7,type<>,type”None”,then press [Tab].
8
Design by : izahusni 3. With the insertion point in the Logical2 text box,click cell C7,type <21,then click OK.
The function is complete,and the result,TRUE,appears in cell G7.
4. Drag the fill handle to copy the formula in cell G7 into the range G8:G12.
5. Save the workbook. 9
Design by : izahusni
H. Calculating Payments with the PMT function. PMT is a financial function that calculates the periodic payment amount for money borrowed. For example,if you want to borrow money to buy a car,and you know the principal amount,interest rate,and loan term,the PMT function can calculate your monthly payment. 1. Click the Loan sheet tab,click cell F5,click the Formulas tab,click the Financial button in the Function Library group,scroll down the list of functions,then click PMT.
2. With the insertion point in the Rate text box,click cell D5 on the worksheet,type /12,then press [Tab].
10
Design by : izahusni
3. With the insertion point in the Nper text box,click cell E5,click the Pv text box,click cell B5,then click OK.
4. Double click cell F5 and edit it so it reads = PMT (D5/12,E5,-B5) ,then click the Enter button on the formula bar. 11
Design by : izahusni
5. With cell F5 selected,drag the fill handle to fill the range F6:F7.
6. Click cell G5,type =,click cell E5,type *,click cell F5,then press [Tab].
7. In cell H5,type =,click cell G5,type -.
8. Click cell B5,then click . 12
Design by : izahusni
9. The result as shown below.
10. Copy the formulas in cells G5:H5 into the range G6:H7,then click cell A1.
11. Save the workbook.
Good Luck!!
13
Related Documents
Analyzing Data Using Formulas B
June 2020 7
Analyzing Data Using Formulas A
June 2020 6
Analyzing Data Using Access
May 2020 8
Using Interprtg Data Formulas
November 2019 8
Formulas And Words B
July 2020 3