KOS 1110: COMPUTERS IN SCIENCE

ASSIGNMENT 1-QUESTIONS IN EXCEL

DUE DATE: SATURDAY,22 NOVEMBER 2003,10am

By:Siti Marhana bt. Razali

0325574

BSC(Math)

Section 1

Dr.Ibrahim Ali Batcha

marhana_84@yahoo.com.

Questions:

1. What is the spreadsheet program? How does it differ from a word

processing program?

A spreadsheet program is a program which work with numbers, perform calculations and laid out in rows and columns. While word processing programs are mainly do not do calculation, and it just for writing reports and documents.

2. What is the difference between a worksheet and a workbook?

A worksheet is a single sheet while a workbook is more than a single sheet represented by tabs at the end of the sheets.

3. How do you copy and move cell contents?

At Menu bar, select Copy, move to the cell where you want to copy. Click paste. You also can use Copy and Paste icon. Others, you also can use the Fill Handle Method. In order to move or cut cell you can use the same method as Copy method.

4. How do you use the fill handle to copy content of cells and to create a sequence? What type of sequences can be created using the fill handle?

Use the Fill handle method to copy contents, the mouse pointer must be move over the square in the lower right corner of the selected cell, wait until the mouse pointer changes to a cross hair symbol, hold down the left mouse button and drag to copy.

Fill handle method also can be use to create sequence. You have to enter the pattern. The pattern must be consecutive number. Click the lower right side of the mouse containing the pattern, hold the right mouse button drag to

select the area to be filled with the matching data. When the menu open select the type of data that you want to be filled in the cells.

There are a few kind of sequence that can be created such as:

*consecutive numbers.

*numbers in a pattern such as odd or even numbers

*days of the days, weeks, months, years.

5. How do you create custom headers and footers? You have to go to the view, then select headers and footers, choose the type you want from the page setup.

6. What is a relative cell address reference? How do you change a relative reference to an absolute reference? What is the mixed cell address reference?

Relative cell address reference are the default in Excel. Which the addresses are automatically adjusted when the calculation are copied to new cells.

Absolute Cell Address Reference will copy exactly the same things. It will be not change when you copied to the new location.

Mixed Cell Address Reference will allow only the column portion or the row portion of the cell reference to be adjusted.

7. What is the formula bar? What is the name box?

A formula bar is located below the formatting and standard toolbar. Where the formula you used will be appear when the certain cell is selected. While the name box is an active cell reference on the left side of the formula bar. You have to click the name box in order to select any cell. After chosen the cell you can enter the cell reference.

8. What is the deference between erasing the content of a column and deleting the column?

When you want to erase the content of the column you have to select the range that want to be erased then press delete. Or in other way, you can also using Undo or Redo. If you want to delete the column, select column letters then at the Menu bar choice

Delete column. Others you can also using the Right click of the mouse. Then click at the Delete.

9. How will you freeze a part of the worksheet?

To freeze the worksheet(rows and column), select the worksheet to be frozen then at the Menu bar select Window, then Freeze Panes.

10. What is the maximum number of significant digits possible in MS Excel?

15 significant numbers.

11. What is the difference between a bar chart and the histogram?

A bar chart is used to represent the data without doing the data analysis while the histogram has frequency and also data analysis.

12. How will you put the error bars in the x-y plot? Explain using sample data. (hint: See Help facility in Excel)

At the data series which you want to add error bar click there. Then at the Format menu, choose Selected Data Series. At the X Error bars tab or at the Y Error bars tab, you can click at the option you want.

13. Use of Help facilities in Excel: You have seen the usage of some simple function such as log, exp, and sin in Excel. Go through the Help facilities in Excel and study

several different types of functions. Explain the use of at least one an common function with the suitable example.

Minverse is a function returns the inverse of a selected matrix. The way to write it is: MINVERSE (array)

Array is a numerical array with an equal number of rows and columns. Array can be given as a cell range such as A1:C3;as an array constant such as {1,2,3,4,5,6,7,8,9}; or as a name for either of these. If any cell in array are empty or contain text, MINVERSE returns the =VALUE! Error value. MINVERSE also returns the =VALUE! Error value if array does not have an equal numbers of rows and columns. Formulas that return array must be entered as array formulas Inverse matrices, like determinants, are generally used for solving system of mathematical equations involving several variables. The product of the matrix and its inverse is the identity matrix-the square array in which the diagonal values equal 1, and all other value equal 0.

14. Calculation using Formulas: Select any formula ( as complicated as possible) from any one of your text books. Use Excel to calculate this formula, by entering the constant and the variables separately.

Volume=(1*Gas Constant*Temperature)/Pressure

15. Multiplot: Write down any one formula with two constants ( a and b ) and one variable (x). (eg.y=a sin (bx) or y= ax + b log (x)… etc) 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.

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

16. Solving Simultaneous Equations: Write a set of five equations with five variables

( eq. p, q, r, s, t, and u ) and solve them using Excel. Verify your answer by back substitution.

17. Solver exercise: The following data set is obtained in an experiment:

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

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

Where a, b and c are constant 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. Lot the experimental and the calculated data in

18. 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.

19. Computerization of the laboratory report: Present any of your 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 lab report that you would submit after completing your experiment.

Objective: To study the mass and the accelaretion using an Atwood’s apparatus

Apparatus:Science Workshop Interface, table clamp, universal, smart pulley, thread, mass net.

Procedure:

Part1:Computer Set Up

Part2: Sensor Calibrating and Equipment Set Up

Part3:Data Recording Constant Total Mass

Result:

Data Table 1: Constant Total Mass

20.Exercises done in the computer lab: Present a complete report of all the Excel exercises done during the Week 2 lab hours.

Exercise1: Graphing with MS Excel.

Exercise2: Non Linear Curve Fitting.

Exercise3: Scientific Applications with Excel.

back to K.O.S