Analyzing Data Using Formulas A
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 A as PDF for free.
More details
- Words: 1,247
- Pages: 17
Design by : izahusni
MICROSOFT EXCEL 2007 TRAINING Analyzing Data Using Formulas
Topic:A. B. C. D.
Formatting data using text functions. Sum a data range based on conditions Consolidate data using a formula Check formulas for errors
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.
A. Formatting Data Using Text Function. Often,data you import needs restructuring or reformatting to be understandable and attractive,or to match the formatting of other data in your worksheet. Instead of handling these task manually in each cell,you can use Excel conversion tools and text functions to perform these tasks automatically for a range of cell data. 1.
Start Excel
2. Open the file from the drive and folder where you store your data files,then save as Sales Data.
Page 1 of 17
Design by : izahusni
3. On the worksheet, select the range A4-A15,click the Data tab,then click the Text to column button in the Data Tools group.
Range A4-A15
4. The convert Text to Columns wizard opens, as shown below.
Text to Columns Data tab
5. The data fields on your worksheet separated by commas,which will act as delimiters.
Page 2 of 17
Design by : izahusni
6. If necessary,click the Delimited option button to select it,click Next.
7.
In the Delimiters area of the dialog box click the Comma check box to select it if necessary,click any other selected check boxes to deselect them,then click Next. -you instructed Excel to separate your data at the comma delimiter-
8.
Click the Text option button in the Column data format area,click the General column to select it in the Data preview area,click the Text option button in the column data format area,then click Finish. Page 3 of 17
Design by : izahusni
-the data are separated into 3 columns of text-
9. Click cell D4,click the Formulas tab.
10. Click the Text button in the Function Library group,click PROPER,with the insertion point in the Text box,click cell A4,then click OK.
Page 4 of 17
Design by : izahusni
11. The name is copied from cell A4 to cell D4 with the correct uppercase letters for proper names. The remaining names and the cities are still in lowercase letters.
12. Drag the fill handle to copy the formula in cell D4 to cell E4,then copy the formulas in cells D4:E4 into the range D5:E15
Page 5 of 17
Design by : izahusni
13. Click cell F4,click the Text button in the Function Library Group,click CONCATENATE.
14. With the insertion point in the Text1 text box,click cell C4,press [Tab],with the insertion point in the Text2 text box,press [Spacebar],type Years,then click OK.
Page 6 of 17
Design by : izahusni
15. Copy the formula in cell F4 into the range F5:F15.
16. Click the Insert tab,click the Header & Footer button in the Text group.
17. Click the Go to Footer button in the Navigation group,enter your name in the center text box,click cell A1,then click the Normal button in the status bar.
Page 7 of 17
Design by : izahusni
18. Save your workbook.
B. Summing a Data Range Based on Conditions. You have learned how to use the SUM,COUNT and EVERAGE functions for data ranges. You can also use Excel functions to sum,count and average data in a range based on criteria or conditions you set. The SUMIF function conditionally totals cells in a sum range that meet given criteria. Similarly, the COUNTIF functions counts cells and the AVERAGEIF function averages cells in a range based on a specific condition. 1. Click the Branch Sales sheet tab,click cell G7.
2. Click the Formulas tab,click the More Functions button in the Function Library group,point to Statistical,then click COUNTIF.
Page 8 of 17
Design by : izahusni
3. With the insertion point in the Range text box,select the range A6:A12,press [F4],press [Tab],with the insertion point in the Criteria text box,click cell F7,then click OK.
4. Then the result as shown below:-
5. Click cell H7,click the Math & Trig button in the Function Library group,scroll down the list of functions,then click SUMIF.
Page 9 of 17
Design by : izahusni
6. With the insertion point in the Range text box,select the range A6:A12,press [F4],press [Tab],with the insertion point in the Criteria text box,click cell F7,press [Tab],with the insertion point in the Sum_range text box,select the range B6:B12,press [F4],then click OK.
7. The result as shown below:-
Page 10 of 17
Design by : izahusni
8. Click cell I7,click the More Functions button in the Functions button in the Function Library group,point to Statistical,then click AVERAGEIF.
9. With the insertion point in the Range text box,select the range A6:A12,press [F4],press[Tab],with the insertion point in the Criteria text box,click cell F7,press [Tab],with the insertion point in the Average_range text box,select the range B6:B12,press [F4],then click OK.
Page 11 of 17
Design by : izahusni
10. The result as shown below:-
11. Select the range G7:I7,then drag the fill handle to fill the range G8:I10.
12. Save your workbook.
C. Consolidating Data Using a Formula. Page 12 of 17
Design by : izahusni
When you want to summarize similar data that exits in different sheets or workbooks, you can consolidate, or combine and display,the data in one sheet. 1. Click the Motor Summary Jan sheet tab
The worksheet must have difference sheet of data.
2. Click cell B7,click the Formulas tab,click the AutoSum button in the Function Library group,click the WSSB sheet tab,press and hold [Shift] and click the CCP sheet tab,click cell G7,then click the Enter button on the formula bar.
Page 13 of 17
Design by : izahusni
Drag the fill handle to copy the formula in cell B7 to cell C7
3. In the Motor Summary Jan sheet, with the range B7:C7 selected,drag the fill handle to fill the range B8:C9.
4. Save the workbook.
D. Checking Formulas for Errors. When formulas result in errors,Excel displays an error value based on the error type. 1. Click cell B10, click the Formulas tab,click the AutoSum button in the Function Library group,then click the Enter button on the formula bar. The number of cars sold,18 appears in cell B10.
Page 14 of 17
Design by : izahusni
2. Drag the fill handle to copy the formula in cell B10 into cell C10.
The cars revenue total of 304347 appears in cell C10.
3. Click cell B14,click the Logical button in the Function Library group,click IFERROR.
Page 15 of 17
Design by : izahusni
4. With the insertion point in the Value text box,click cell C7,type /,click cell C10,press [Tab],in the Value_if_error text box,type ERROR,then click OK.
The percentage of Honda revenue of 35.58% appears in cell B14. 5. Drag the fill handle to copy the formula in cell B14 into the range B15:B16
The ERROR value appears in cell B14:B15. The errors are a result of the relative address for C10 in the denominator of the copied formula. Changing the relative address of C10 in the copied formula to an absolute address of $C$10 will correct the errors. Page 16 of 17
Design by : izahusni
6. Double click cell B14,select C10 in the formula,press [F4],then click on the formula bar.
7. Copy the corrected formula in cell B14 into the range B15:B17.
The car revenue percentages now appear in all cell without error messages.
8. Save the workbook.
Good Luck!!
Page 17 of 17
MICROSOFT EXCEL 2007 TRAINING Analyzing Data Using Formulas
Topic:A. B. C. D.
Formatting data using text functions. Sum a data range based on conditions Consolidate data using a formula Check formulas for errors
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.
A. Formatting Data Using Text Function. Often,data you import needs restructuring or reformatting to be understandable and attractive,or to match the formatting of other data in your worksheet. Instead of handling these task manually in each cell,you can use Excel conversion tools and text functions to perform these tasks automatically for a range of cell data. 1.
Start Excel
2. Open the file from the drive and folder where you store your data files,then save as Sales Data.
Page 1 of 17
Design by : izahusni
3. On the worksheet, select the range A4-A15,click the Data tab,then click the Text to column button in the Data Tools group.
Range A4-A15
4. The convert Text to Columns wizard opens, as shown below.
Text to Columns Data tab
5. The data fields on your worksheet separated by commas,which will act as delimiters.
Page 2 of 17
Design by : izahusni
6. If necessary,click the Delimited option button to select it,click Next.
7.
In the Delimiters area of the dialog box click the Comma check box to select it if necessary,click any other selected check boxes to deselect them,then click Next. -you instructed Excel to separate your data at the comma delimiter-
8.
Click the Text option button in the Column data format area,click the General column to select it in the Data preview area,click the Text option button in the column data format area,then click Finish. Page 3 of 17
Design by : izahusni
-the data are separated into 3 columns of text-
9. Click cell D4,click the Formulas tab.
10. Click the Text button in the Function Library group,click PROPER,with the insertion point in the Text box,click cell A4,then click OK.
Page 4 of 17
Design by : izahusni
11. The name is copied from cell A4 to cell D4 with the correct uppercase letters for proper names. The remaining names and the cities are still in lowercase letters.
12. Drag the fill handle to copy the formula in cell D4 to cell E4,then copy the formulas in cells D4:E4 into the range D5:E15
Page 5 of 17
Design by : izahusni
13. Click cell F4,click the Text button in the Function Library Group,click CONCATENATE.
14. With the insertion point in the Text1 text box,click cell C4,press [Tab],with the insertion point in the Text2 text box,press [Spacebar],type Years,then click OK.
Page 6 of 17
Design by : izahusni
15. Copy the formula in cell F4 into the range F5:F15.
16. Click the Insert tab,click the Header & Footer button in the Text group.
17. Click the Go to Footer button in the Navigation group,enter your name in the center text box,click cell A1,then click the Normal button in the status bar.
Page 7 of 17
Design by : izahusni
18. Save your workbook.
B. Summing a Data Range Based on Conditions. You have learned how to use the SUM,COUNT and EVERAGE functions for data ranges. You can also use Excel functions to sum,count and average data in a range based on criteria or conditions you set. The SUMIF function conditionally totals cells in a sum range that meet given criteria. Similarly, the COUNTIF functions counts cells and the AVERAGEIF function averages cells in a range based on a specific condition. 1. Click the Branch Sales sheet tab,click cell G7.
2. Click the Formulas tab,click the More Functions button in the Function Library group,point to Statistical,then click COUNTIF.
Page 8 of 17
Design by : izahusni
3. With the insertion point in the Range text box,select the range A6:A12,press [F4],press [Tab],with the insertion point in the Criteria text box,click cell F7,then click OK.
4. Then the result as shown below:-
5. Click cell H7,click the Math & Trig button in the Function Library group,scroll down the list of functions,then click SUMIF.
Page 9 of 17
Design by : izahusni
6. With the insertion point in the Range text box,select the range A6:A12,press [F4],press [Tab],with the insertion point in the Criteria text box,click cell F7,press [Tab],with the insertion point in the Sum_range text box,select the range B6:B12,press [F4],then click OK.
7. The result as shown below:-
Page 10 of 17
Design by : izahusni
8. Click cell I7,click the More Functions button in the Functions button in the Function Library group,point to Statistical,then click AVERAGEIF.
9. With the insertion point in the Range text box,select the range A6:A12,press [F4],press[Tab],with the insertion point in the Criteria text box,click cell F7,press [Tab],with the insertion point in the Average_range text box,select the range B6:B12,press [F4],then click OK.
Page 11 of 17
Design by : izahusni
10. The result as shown below:-
11. Select the range G7:I7,then drag the fill handle to fill the range G8:I10.
12. Save your workbook.
C. Consolidating Data Using a Formula. Page 12 of 17
Design by : izahusni
When you want to summarize similar data that exits in different sheets or workbooks, you can consolidate, or combine and display,the data in one sheet. 1. Click the Motor Summary Jan sheet tab
The worksheet must have difference sheet of data.
2. Click cell B7,click the Formulas tab,click the AutoSum button in the Function Library group,click the WSSB sheet tab,press and hold [Shift] and click the CCP sheet tab,click cell G7,then click the Enter button on the formula bar.
Page 13 of 17
Design by : izahusni
Drag the fill handle to copy the formula in cell B7 to cell C7
3. In the Motor Summary Jan sheet, with the range B7:C7 selected,drag the fill handle to fill the range B8:C9.
4. Save the workbook.
D. Checking Formulas for Errors. When formulas result in errors,Excel displays an error value based on the error type. 1. Click cell B10, click the Formulas tab,click the AutoSum button in the Function Library group,then click the Enter button on the formula bar. The number of cars sold,18 appears in cell B10.
Page 14 of 17
Design by : izahusni
2. Drag the fill handle to copy the formula in cell B10 into cell C10.
The cars revenue total of 304347 appears in cell C10.
3. Click cell B14,click the Logical button in the Function Library group,click IFERROR.
Page 15 of 17
Design by : izahusni
4. With the insertion point in the Value text box,click cell C7,type /,click cell C10,press [Tab],in the Value_if_error text box,type ERROR,then click OK.
The percentage of Honda revenue of 35.58% appears in cell B14. 5. Drag the fill handle to copy the formula in cell B14 into the range B15:B16
The ERROR value appears in cell B14:B15. The errors are a result of the relative address for C10 in the denominator of the copied formula. Changing the relative address of C10 in the copied formula to an absolute address of $C$10 will correct the errors. Page 16 of 17
Design by : izahusni
6. Double click cell B14,select C10 in the formula,press [F4],then click on the formula bar.
7. Copy the corrected formula in cell B14 into the range B15:B17.
The car revenue percentages now appear in all cell without error messages.
8. Save the workbook.
Good Luck!!
Page 17 of 17
Related Documents
Analyzing Data Using Formulas A
June 2020 6
Analyzing Data Using Formulas B
June 2020 7
Analyzing Data Using Access
May 2020 8
Using Interprtg Data Formulas
November 2019 8
Analyzing A Case Study
November 2019 35