Siti Munirah binti Munabir (0616552)10/4/20186:41:44 AM

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?

The difference is Microsoft Excel laid out in rows and columns, work with numbers and allow calculations while Microsoft Word contain plain layout that work with text data to produce a letter, a report, a memo, an email massage in a form of words, sentences, paragraphs and pages of text.

Advantages

  • Word can manipulate data to form a letter, a report, a memo and email massage. Besides, it can Word allows creating, modifying, storage, retrieving and printing part or the entire document.
  • Excel perform faster and more accurate data entry, can quickly edit and format data, able to simple and complex calculation and create powerful graphs, do data analysis by changing independent values and support simultaneous use of multiple spreadsheets.

Disadvantages

  • Word does not support for mathematical calculation and do data analysis.
  • Functions more focus on calculation and data analysis
  1. 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?

Diffences

Cell-the intersection of a column and row where the information is typed

Worksheet-a single page of sheet

Workbook-collection of worksheet in one file

The way to color code sheet tabs to make them easier to be recognized:

Point a cursor to a code sheet tabs and right clicks a mouse. Then choose color tabs. Get the wanted color and click OK.

View different parts of a worksheet at the same time:

Using split panes

Point a cursor to the split box at the top of the ventricle scroll or at the right end of the horizontal scroll bar. Drag the split box to the wanted position when the pointer changed to a split pointer.

Using freeze panes

Select the row and column that are wanted to be displayed. Then, click Freeze Panes on the Window menu.

View different worksheets at the same time:

Click New Window on the Window menu. Switch to the new window and click a sheet that is wanted to be viewed. Then, click Arrange at the Window menu to choose type of sheets arrangement.

  1. 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?

Hide parts of workbook will make data removed from view but is not deleted from the workbook.

When printing a workbook, the hidden parts will not be printed.

To prevent others from displaying hidden sheets, rows or columns, the worksheet or sheet can be protected with password.

Applying‘hide’ command:

  • Workbook and sheets
  • Rows and columns
  1. 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

Transfer the inserted content from one cell to another:

Double click the cell that contains the data that wanted to be copied. Select the content and click Copy on the Standard toolbar. Then, click the cell to paste the content. Click Paste. Press ENTER.

Replicate a sequence (days):

Type the first day or date in the list in the cell. Select the cell and use the fill handle to fill a list of consecutive days.

Other kinds of data sequence:

Numbers

Time

Day

Date

Month

Year

Alphabet

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

Merge scenarios from another worksheet

Open the workbooks that contain the scenarios that are wanted to be merged. Switch to the worksheet where the scenarios is merged. Click Scenarios on the Tools menu. Click Merge. Choose a workbook name in the Book box. Click the name that contains the wanted scenarios to merge in the Sheet box and click OK.

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

Create headers and footers:

Click Header and Footer on the View menu. Fill the requirement and click OK.

Insert a graphics is allowed in header and footer

  1. 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?

Types of reference styles:

The A1 reference style-default style, which refers to columns with letters and rows with numbers. In example, A2 means the cell in column A and row 10 while A10:E20 means the range of cells in columns A through E and rows 10 through 20.

Relative and Absolute references

A relative cell reference in a formula is based on the relative position of the cell that contains the formula and the reference refers to. If the position of the cell that contains the formula changes, the reference is changed.

An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the formula is copied, the absolute references remain the same.

The 3-D reference style-includes the cell and range cell reference, preceded by a range of worksheet name. For example, =SUM (Sheet2:Sheet13!B5).

The R1C1 reference style-indicates as the location of a cell with an “R” followed by a row number and a “C” followed by a column number.

Reference style refers to columns with letters and rows with numbers:

A1 reference style.

Reference style that can be used to analyze data in the same cell or range of cells on multiple worksheets within the workbook:

3-D reference style

Reference style indicates the location of a cell with an ‘R’ followed by a row and a ‘C’ followed by a column number:

R1C1 reference style

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

Operator symbols are the symbols that are used for mathematical operation, to compare two values, as text concatenation and as reference operator

Symbols and its functions:

a) Arithmetic operators-basic mathematical operations

+(plus sign)-addition

- (minus sign)-subtraction/negations

* (asterisk)-multiplication

/ (forward dash)-division

% (percent sign)-percent

^ (caret)-exponentiation

b) Comparison operators-comparing two values

= equal sign

> greater than

< less than

>= greater than or equal to

<= less than or equal to

> not equal to

c) Text concatenation operators

& (ampersand)-connects or concatenates two values to produce one continuous text value

d) Reference operators

: (colon)-range operator

, (comma)-union operator

(space)-intersection operator

  1. 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?

The difference is the formula bar display cell content or formula in cell while name box display name or active cell or range of cell.

Only relative cell reference will change not absolute cell reference when copying formula.

When selecting a range of cells, the name box will display the name of selecting cell in A1 reference style, example A1:D2.

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

Erasing the content of a column will remove the contents but does not remove comments or cell formats but deleting a column will also remove comments and column formats besides the contents itself.

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

Freeze panes allow data that remains visible when scrolling in a sheet to be selected.

Freezing an external data range will retain the data but not its underlying query, so that a frozen external data range cannot be refreshed.

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

a) maximum number of significant digit =15 digits

b) largest allowed negative number = -1E-307

c) smallest allowed positive number = 2.229E-308

d) maximum length of formula contents = 1,024 characters

  1. 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?

Trendline is a graphic representation of trends in data series. It is used for the study regression analysis.

Adding a trendline to a chart:

Click the data to which a trendline is wanted to be added. Click Add Trendline on the Chart menu. Lastly, click the type of regression trendline or moving average that is wanted on the Type tab. Fill all the requirements and click OK.

Obtaining the R-squared value and the equation through trendline command:

Yes, it is possible. After add trendline to the chart, put pointer at the line and click Format Trendline. Choose Options and choose Display equation on chart and Display R-squared value on chart. Click OK.

  1. 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?

Protect a workbook can be done by:

Protect workbook elements

On the Tools menu, point to Protection and then click Protect Workbook. Choose type of protection that is wanted, Structure or Windows. Then adding password to prevent others from removing workbook protection and lastly, click OK.

Protect a shared workbook

On the Tools menu, point to Protection and click Protect and Share Workbook. Select the Sharing with track changes check box. Add password to Password box if want.

Protect workbook from viewing or editing

On the File menu, click Save As. On Tools menu, click General Options. Choose the type of ways that is wanted. If want other to enter a password before viewing workbook, type password in Password to open box, if want other to enter a password before save changes to the workbook, type password in the Password to modify box. For any options, click OK after inserting password. Retype password when prompted to confirm it. Click Save. When prompted, click Yes.

Protect worksheet elements

Choose wanted worksheet. Selecting cell or range of cell to be protected. Click Cells on the format menu and then click Protection tab. After that, clear the Locked check box. Next, hide formulas that are wanted to be invisible by selecting the cells with the formulas. Click the Cells on the Format menu and click at the Protection tab. Then, choose Hidden check box.

To unlock graphic objects to be able to change, on the Tools menu, click Protect Sheet from the Protection menu. Type a password for the sheet. Then, choose elements that are wanted for users to be able to change from the Allow all users of this worksheet to list. Lastly, click OK and retype it if prompted.

Kinds of limitations that are imposed upon fellow users when protect a structure within a workbook:

Protect workbook elements-prevent users from adding or deleting worksheets or displaying hidden worksheet. It also prevents users from changing the sizes or positions of the windows that are being set up to display a workbook.

Protecting a shared workbook-user cannot return it to exclusive use or delete the change history log.

Protecting workbook from viewing and editing-restrict who can open and use data in a workbook file.

  1. Create 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).

Answer in Excel

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

Types of SIN function:

Five.

IMSIN- the sine of a complex number in x+yi or x+yj text format, example: IMSIN (“3+4i”) means sine of 3+4i

ASIN- the arcsine of a number, example: arcsine of 0.5 in degree is 30

SIN- the sine of the given angle, example: sine of 30 degree equal to 0.5

ASINH- the inverse hyperbolic sine of a number where the number is any real number, example: ASINH (10) is equal to 2.998223 in radians

SINH- hyperbolic sine of a number where the number is any real number, example: SINH (-1) equal to 1.175201194 in radians

For next questions, the answers are in Excel workbook

  1. 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).

(a)180°

(b)45°

(c)75°

(d)36°

(e)236°

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

  1. By using any of your selected formula, perform calculations by entering the constants and variables separately. For example, force =…..units, density = ….
  1. Write a set of four simultaneous equations with four variables and solve them using Excel. Verify your answer by back substitution. (HINT: Use Solver)
  1. 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.
  1. 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.
  1. 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)
  1. 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?

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

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
  1. 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.
  2. 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.

1