Assignment
1 - Questions in Excel
Due on or
before Friday,
- What is a spreadsheet program? How does it differ from a word processing program?
Spreadsheet program is a program which laid out in rows and coloumns, work with numbers and perform calculations. The difference between it and processing program is that it can do some calculations while word processing program cannot.
- What is the difference between a worksheet and a workbook?
Worksheet is a single sheet in spreadsheet program while workbook is the combination of worksheets or multiple of it that represented by tabs at the bottom of the Excel screen.
- How do you copy and move cell contents?
First method, from the Menu Bar, selects edit and then selects Copy. Then move to the cells where the information wishes to be copied. After that go back to the Menu Bar, select Edit and then select Paste. The second method is right click the mouse and uses the Copy and Paste icons. Another method is use the Fill Handle Method. Move the Mouse Pointer over the square in the lower right corner of the selected cell or cell range. The mouse pointer changes to a cross-hair symbol (+). Hold down the left mouse button and drag to copy the content. To move or cut cells, go to Menu Bar and select Edit then select Cut. Then click where the cells are to be moved. After that select Edit and then Paste.
- How do you use the fill handle to copy contents of cells and to create a sequence? What type of sequences can be created using the fill handle?
To copy contents using the Fill Handle Method and creating a sequence, type a number in a cell then move the Mouse Pointer over the square in the lower right corner of the selected cell or cell range. The mouse pointer changes to a cross-hair symbol (+). Hold down the left mouse button and drag to copy the content. The types of sequences that can be created using the fill handle method are additional sequences(+1,+2), multiple sequences(*2*3),square sequences (2^2,3^2) and etc.
- How do you create custom headers and footers?
From menu bar, click View and select Header and Footer. Then, a bar will appear on top of the page. Finally, insert name, time, date, pages number or whatever appropriate.
- What is a relative cell address reference? How do you change a relative reference to an absolute reference? What is a mixed cell address reference?
Relative cell address reference is the default in Excel, for example: C2, address is automatically adjusted when calculations are copied to new cells.
To change relative reference to an absolute reference, type in ($) sign in front of row and column address of a cell, example $C$2.
Mixed cell address reference is a reference that only allowed the adjustment of the row portion or column portion of the cell reference. For instance, $C2: it is only row can be adjusted, C$2: it is only column can be adjusted.
- What is
the formula bar? What is the name box?
Formula bar is the bar at the top of the excel window that used to edit or enter formulas or values in cells or charts. It displays the constant value or formula stored in the active cells.
Name
box is the box where can name or insert the source of a point of the graph. Name
box also is the box to the left of the Edit Formula and Formula Boxes.
- What is
the difference between erasing the contents of a column and deleting the
column?
The difference between erasing the
contents of a column and deleting the column is that erasing the contents of a column means deleting a single thing in
the column one by one but deleting the
column is deleting the whole thing in that column simultaneously.
- How will
you freeze a part of the worksheet?
To freeze a part of the worksheet,
firstly select the rows or columns to be frozen. Then go to menu bar to select
Window and then choose Freeze Panes.
- What is
the maximum number of significant digits possible in MS Excel?
15 significant numbers.
- What is
the difference between a bar chart and a histogram?
Bar chart only shows the whole data bar,
comparing the height to each and every data inserted without analyze it. While
a histogram is the analysis tool
calculates individual and cumulative frequencies for a cell range of data and
data bins.
- How will you put the error bars in the x-y plot? Make a table with data points along with their error values, and draw a graph with errors bars shown. (hint: See Help facility in Excel)
First, click selected data from the table. Next, go to the Format menu. Click Selected Data series. Finally, select the options desired at the X Error Bars tab or or the Y Error Bars tab.
|
velocity(ms^-1) |
time(s) |
|
2 |
1 |
|
4 |
2 |
|
6 |
3 |
|
7 |
4 |
|
8 |
5 |
|
10 |
6 |
|
12 |
7 |
- How will
you protect a workbook in MS Excel?
Firstly,click Tools, then select
Protection. Now, choose Workbook. The cells which are not unlocked as above are
protected from any modification.
- How to
protect only selected cells in MS Excel?
Select only those cells in which the
data values need to be input and unlock them by clicking Format from Menu Bar .After that, click
Cells, Protection and choose Clear the lock.
- Explain
the different forms of log
functions available in MS Excel.
There are several form of Log Function; Log worksheet function, Log10 worksheet function, Exp worksheet function, Ln worksheet function and Loginv worksheet function.
1)L
OG(number,base) j avascript:hhobj_1.Click()
Number is the positive real number for which you want the logarithm. Base is the base of the logarithm. If base is omitted, it is assumed to be 10.
2) LOG10(number)
Number is the positive real number for which you want the base-10 logarithm.
3) EXP(number)
Number is the exponent applied to the base e.
4) LN(number)
Number is the positive real number for which you want the natural logarithm.
LN is the inverse of the EXP function.
5) LOGINV(probability,mean,standard_dev)
Probability is a probability associated with the lognormal distribution. Mean is
the mean of ln(x). Standard_dev is
the standard deviation of ln(x).
- In MS
Excel, the argument of the sin functions should be in radians. Assume that you are provided with a set
of angles in degrees. Use radians function in MS Excel to
express the angles in radians and then find out the sin of these functions.
- Use
of Help facilities in Excel:
Go through the Help facilities in Excel and study several different
types of functions. Explain the
application of three different uncommon functions using your own examples.
(1) BIN20CT(number,places)
Number is the binary number you want to
convert. Number cannot contain more than
10 characters(10 bits). The most significant bit of number is the sign bit. The
remaining 9 bits are magnitude
bits. Negative numbers are represented using two’s-complement
notations.
Places
are the number of characters to use. If places are omitted, BIN2OCT uses the minimum number of characters necessary. Places is
useful for padding the return value
with leading )s(zeros).
Eg: =BIN2OCT(1100100), - convert binary 1100100 to octal (144).
(2) HEX2BIN(number,places)
Number is the hexadecimal number you want to convert. Number cannot contain more than 10 characters. The most significant bit of number is the sign bit (40th bit from the right). The remaining 9 bits are magnitude bits. Negative numbers are represented using two's-complement notation.
Places is the number of characters to use. If places is omitted, HEX2BIN uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).
Eg: =HEX2BIN("F", 8) -Converts hexadecimal F to binary, with 8
characters (00001111)
(3) MEXP(inumber)
Inumber is a complex number for which you want the exponential.
Use COMPLEX to convert real and imaginary coefficients into a complex number. If inumber is not in the form x + yi or x + yj, IMEXP returns the #NUM! error value. The exponential of a complex number is:
Eg: IMEXP ("1+i") - Exponential of the complex number 1+i
(1.468694 + 2.287355i)
- Calculation
using Formulas: Select any formula (as complicated as possible)
from any one of your textbooks. Use Excel to calculate this formula, by
entering the constants and the variables separately. (For
example:
i.
Gas constant
= … units
ii.
Temperature =
…. units
iii.
Pressure =
.. units
iv. Voulme = formula)
19. Multiplot: Write down any one formula with two constants (a and b) and one variable (x). (eg. y=a sin(bx) or y=a x + b log (x) …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=… |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Plot all the four curves in the same plot and label them differently. Comment on the effect of the constants on these plots.
20. 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.
21. Solver exercise: 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?
22. 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.
23. 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 lab report that you would submit after completing your experiment.
24. Exercises done in the computer lab: Present a complete report of all the excel exercises done during the lab hours.
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
DINA BTE. MOHD KAMAL
0432716
SECTION 1
DR. IBRAHIM ALI NOORBATCHA
BMATHSC
ASSIGNMENT 1
-2005-