MS Excel questions set II



KOS 1110 Computers in Science

Assignment 1 - Questions in Excel

Due on or before Monday, 25-9-2006, 10am

1. What is the difference between Microsoft Excel and Microsoft Word? What are the functions, advantages and disadvantages of both programs?

|Microsoft |Excel |Word |

|Difference |Is an automated version of an accountant’s |Is the word processing software which is an|

| |ledger. It is simply an electronic |application whose purpose is to help the |

| |spreadsheet program that runs on Windows |user create any type of written |

| |operating system. It is composed of rows |communication in electronic form. |

| |and columns or primarily numeric data. | |

|Functions |User can use an electronic spreadsheet to |This word processor can be used to |

| |perform numeric calculations rapidly and |manipulate text data to produce a letter, a|

| |accurately, where formulas are based on |report, a memo, an email message or any |

| |cell references, and thus, so easy to |other type of correspondence. |

| |update results. It is also very easy to | |

| |store, retrieve and print results. | |

|Advantages |1. User can enter data faster and more |1. Through Microsoft Word, users can |

| |accurately than using the pencil and paper |create, modify, store, retrieve, and print |

| |method. |part or all of a document. |

| | | |

| |2. Data in the worksheet can easily be |2. User also can check their English |

| |updated and formatted to suit user’s |spelling and grammar. The processor can |

| |preference. There are various types of |automatically correct the words which are |

| |formatting that can be used to enhance the |incorrect. |

| |presentation of the worksheet such as | |

| |fonts, color and chart type. | |

| | | |

| |3. Excel can perform the calculation from | |

| |simple calculation to complex mathematical | |

| |formulas. Charts can also be created | |

| |instantly according to the required data | |

| |from the worksheet. The graphs can be in | |

| |different types and in 2-D or 3-D views. | |

| | | |

| |4. Excel has the ability to change data and| |

| |then quickly view the recalculated results.| |

| | | |

| |5. Allows working with multiple worksheets | |

| |at the same time, and therefore make it | |

| |easier for the user to link one worksheet | |

| |to another. | |

|Disadvantages |Cannot check user’s English spelling or |Cannot perform the calculation whether |

| |grammar whether it is correct or not. |simple calculation or complex mathematical |

| | |formulas. |

2. What are the differences between a cell, worksheet and a workbook? How to color code sheet tabs to make them easier to be recognized? How to view different parts of a worksheet at the same time? How to view different worksheets at the same time?

|Cell |Worksheet |Workbook |

|The intersection of a row and column in |The one that user use to create |A workbook is comprised of many sheets. |

|the worksheet and it is identified by its|spreadsheets is designed to hold text and |These sheets include worksheets, chart |

|column letter and row number. |numbers. It consists of cells, gridline, |sheets or macro sheets. |

| |rows and column heading, scroll bars and | |

| |sheet tabs. It can be maximized or | |

| |minimized, using the control window button.| |

How to color code sheet tabs to make them easier to be recognized-

How to view different parts of a worksheet at the same time- This can be accomplished by creating a new window in the workbook. Firstly, select Windows/New Window and Windows/Arrange. Then select an option to arrange the windows. The worksheet is now displayed I two different views.

How to view different worksheets at the same time-

3. What happen when you hide parts of a workbook? When it comes to printing does it print the hidden parts of a workbook? How do you prevent others from displaying hidden sheets, rows or columns? What can be hidden from applying the ‘hide’ command?

4. How do you transfer the inserted content from one cell to another? How do you replicate a sequence such as the following? What are the other kinds of data sequences that can be created?

|Monday |Tuesday |Wednesday |Thursday |Friday |

| | | | | |

I can transfer the inserted content from one cell to another by using the AutoFill which is the plus sign appear at the bottom-right corner of the cell or range. AutoFill is one feature that provides specific types of series that Microsoft Excel can fill in for me. I just need to type the first two series (Monday, Tuesday) and I can automatically fill in several types of series by selecting cells and dragging the fill handle. Other kinds of data sequences are text (names of month), increment value including date and time as well as copying the cell content.

5. How do you merge scenarios from another worksheet? In what type of conditions can you do so?

6. How do you create headers and footers in Microsoft Excel? Can you insert a graphics in header and footer?

7. Describe different types of reference styles used to refer cells and ranges of cells in MS Excel. Which reference style refers to columns with letters and rows with numbers? Which reference style can be used to analyze data in the same cell or range of cells on multiple worksheets within the workbook? Which reference style indicates the location of a cell with an ‘R’ followed by a row number and a ‘C’ followed by a column number?

8. Define operator symbols. List down the symbols used and its functions.

9. What is the difference between the formula bar and the name box? What happen when you move a formula? Would the cell reference change when you copy a formula? What happen to the name box when you select a range of cells?

10. What is the difference between deleting a column and erasing the content of a column? Explain what would happen in both conditions?

11. What is the function of ‘freeze panes’? What happen when you freeze an external data range?

To have label remain in view while user scroll the worksheet, user can use the freeze panes command because when a large worksheet contains a column or row label, user can easily get lost when the label is out of view.

12. By using Excel Help facilities, provide the following information:

a) maximum number of significant digits

b) largest allowed negative number

c) smallest allowed positive number

d) maximum length of formula contents

13. Define trendline. How do you add a trendline to a chart? Is it possible to obtain the R-squared value and the equation through this command?

A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.

To add a trendline to a chart,

1.Firstly,click the data series to which user want to add a trendline or moving average.

2. Then, on the Chart menu, click Add Trendline.

3. On the Type tab, click the type of regression trendline or moving average that user want. If user select Polynomial, enter in the Order box the highest power for the independent variable. If user select Moving Average, enter in the Period box the number of periods to be used to calculate the moving average.

14. How do you protect a workbook? How do you protect worksheet elements? When we protect a structure within a workbook, what kinds of limitations are imposed upon fellow users?

15Create a list of x and y values of different variables. Make a table with data points along with their error values, and draw a graph with errors bars shown (BEWARE OF LABELING).

2. How many types of SIN function available within MS Excel? Describe and give examples in your own words.

3. The angle for a function should be in radians. So from the following angles, use EXCEL to obtain the Tangent function. (Hint: Use fill handle to avoid re-entering of the same formula).

i. 180°

ii. 45°

iii. 75°

iv. 36°

v. 236°

4. Multiplot: Write down any one formula with three constants (a, b and c) and one variable (x). (eg. y=a sin(bx) + c or y=a x + b log (x) - c …etc). You can also make up your own formula. Use Excel to calculate your chosen formula for a range of x values at five different sets of constant values and tabulate them as x vs y with x in the first column and y in the next four columns as follows:

|No. |x |y values calculated using different set of constants |

| | |a= …. b=… |a= …. b=… |a= …. b=… |a= …. b=… |

| | | | | | |

| | | | | | |

| | | | | | |

Then, plot all the four curves in the same plot and label them differently. Comment on the effect of the constants on these plots.

5. By using any of your selected formula, perform calculations by entering the constants and variables separately. For example, force =…..units, density = ….

6. Write a set of four simultaneous equations with four variables and solve them using Excel. Verify your answer by back substitution. (HINT: Use Solver)

7. Solve the following equation: 3x3+5x2+x-16=0, with your own initial guess. Find the value of x. Select another 5 equations from any of your text books or assignments and solve it using Goal Seek.

8. Grades distribution: Consider a class consisting of 12 to 20 students. Create their mark list in their final exam. The list should contain their names and their marks in two columns. Use the nested “if condition” to determine their grades (eg. A, B ….) and list them in the third column. Present their grade distribution as a histogram and as a pie chart.

9. Solve Van der Waals's equation: (P + a/V2)(V - b)=RT, for the volume per mole (V) of an organic compound at 10 atm pressure and 400oK. The Van der Waals's constants for this particular compound are a = 40.0 liter2 atm/mole2 and b = 0.2 liter/mole. (Hint: use Goal Seek)

10. The following data set is obtained in an experiment:

|R |V |

|0.5 |127.0355 |

|1.0 |48.0715 |

|1.5 |12.1348 |

|2.0 |8.3139 |

|2.1 |5.3366 |

|2.2 |3.1064 |

|2.3 |1.5361 |

|2.4 |0.5472 |

|2.5 |0.0689 |

|2.6 |0.0374 |

|2.7 |0.3957 |

|2.8 |1.0921 |

|2.9 |2.0807 |

|3.0 |3.3199 |

|3.1 |4.7728 |

|3.2 |10.0998 |

|3.5 |20.7786 |

|4.0 |31.9924 |

|4.5 |42.6319 |

|5.0 |52.1824 |

|5.5 |60.4667 |

|6.0 |67.4911 |

Use the solver module in Excel to fit the data using the equation

V=a(1-exp(-b(R-c)))2,

where a, b and c are constants to be determined. Use solver to determine these constants. Calculate the V values using the values of a, b and c that you had found out by excel. Plot the experimental and the calculated data in the same plot and label them properly. What is the value of V at R=1.25 and R=c?

11. The Maxwell-Boltzmann distribution for molecular speeds is given by:

> [pic]

where n is the fraction of the molecules having speed between c and c+dc. Calculate and plot the values of n for a range of speeds for H2 molecule having a mass of 0.332x10-26 kg/molecule at the temperatures of 100K, 300K and 1000K. The value of the Boltzmann constant (k) is 1.38066x10-23 J/molecule. For simplicity you can ignore the units in these calculations. Use a range of 0 to 5000 m/s at the intervals of 200 m/s for the speed. Assume dc=1. Hint: Make a table such as..:

|Speed (m/s) |n for T=100 K |n for T=300 K |n for T=1000 K |

| | | | |

| | | | |

12. Computerization of the laboratory report: Present any of your experimental laboratory report involving detailed calculations and graphs, as an Excel workbook. Your report should be self-contained and contain all the details to verify your graphs and results. In short it should look as a lab report that you would submit after completing your experiment.

13. Compile all your in-lab exercises briefly and present them as a complete report which also describes your accomplishments or difficulties faced and expectations from Excel.

General instructions for exercises in Excel (Read these instructions carefully)

For maximum credits, use as many Excel options as possible. Fit all the information within the screen size. If it is more than a screen size use the next worksheet and name them creatively. All the worksheets should be self explanatory with appropriate tiles and explanations. In all the MS Word documents use the view and header/footer option to automatically include the name, date and time of the file while printing the documents. In all the worksheets use the page setup and header/footer option to automatically include the name, date and time of the file while printing the excel worksheets. Protect your workbooks (refer the last slide of my Excel presentation or Help in Excel) using the name of the file as the password. All the information except the information to be input by the users should be locked.

After you have completed each assignment put all the files (virus free) in a single folder, compress them using winzip and email (ibrahiman@iiu.edu.my) the final zipped file to me. All of your assignments should carry Assignment no, due date of the assignment, your name, matric card no, section no., degree program name, email address and the web page address of the assignment. The name of the final zipped file should have the form 1mohd.zip, where 1 refers to assignment number and mohd refers to your name. Send both the printed and the electronic versions of your assignments before the due date. Once you have built your home page you should publish your assignments in your home pages. For evaluation purposes, the date of submission of the printed version would be taken as the correct submission date. All of your assignments should have complete particulars (inside the files) such as the course name, assignment number, due date, submission date, your name, your degree program, student ID number, section number, your email address, home page address, instructors name, questions and answers. Any form of copying is completely prohibited.

Each assignment will be graded using the following scheme:

Submission on or before the due date 2 marks (ZERO marks for late submission)

Follow the instructions as above 2 marks

Answer all the questions 2 marks

Originality, creativity and critical analysis 4 marks

Total 10 marks

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download