Assignments 7

Subject: «Spreadsheet processor MS Excel. Data entry and editing.
Logical functions»
Description of the basic functions.

Category Math.
ABS (number) - the module number.
COS (number) - the cosine of a predetermined number.
EXP (number) - returns the number of e raised to the specified power.
LN (number) - returns the natural logarithm of a number.
SIN (number) - returns the sine of the given number.
TAN (number) - returns the tangent of the given number.
SQRT (number) - returns the positive square root.
PI () - Returns the number of π up to 15 digits.
POWER (number, power) - returns the result of the exponentiation;
SUM (number1; number2; ...) - returns the sum of all the numbers included in the list of
arguments;
Learn how to use Excel's logical functions such as the IF, AND and OR function.
If Function
The IF function checks whether a condition is met, and returns one value if TRUE and
another value if FALSE.
1. Select cell C1 and enter the following function.
The IF function returns Correct because the value in cell A1 is higher than 10.
And Function
The AND Function returns TRUE if all conditions are true and returns FALSE if any of
the conditions are false.
1. Select cell D1 and enter the following formula.
The AND function returns FALSE because the value in cell B1 is not higher than 5. As
a result the IF function returns Incorrect.
Or Function
The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all
conditions are false.
1. Select cell E1 and enter the following formula.
The OR function returns TRUE because the value in cell A1 is higher than 10. As a
result the IF function returns Correct.
Exercise 1.
Create the table that calculates the n-th term and the sum
of the arithmetic progression. The formula of the n-th
member of the arithmetic progression: an=a1+d(n-l) and
the sum formula of the first n terms of the arithmetic
progression: Sn=(a1+an)*n/2, where a1 is the first term of
the progression, a d is the difference of the arithmetic
progression, using the absolute reference. The first term,
which is equal to -2, and the difference is 0,725.
1. Click on Sheet1. Rename it to Exercize1.
2. Write down the formulas yourself.
Exercise 2. Construct a graph of the function y = cos2 (2x) sin (x) + e -x│x│ for x in steps of
0.1.
1. Create the following table on sheet 2: fill in the
values of the variable x with the fill marker.
In cell B3 enter the formula: = cos (2 * A3) ^ 2 *
sin (A3) + EXP (-A3) * ABS (A3), copy the
formula to the remaining cells.
2. Select the values for the function and start the
Chart Wizard.
3. Select the chart type - Graph.
4. In the Row tab, in the X-Axis signatures, select
the range of X values.
6. Place the chart in this sheet and rename sheet 2
as the Function Graph.
Exercise 3.
Attestation was conducted on 3 subjects and it is necessary to determine whether a
student is certified in all subjects.
1. Go to Sheet3. Rename it to Ex3.
2. Fill the table according to the sample.
3. The values must be written in percentages. For this: Select the range B3: D7. Clickl the
context menu → Format cells. Go to the Numbers tab. Number format select Percentage, Set
the number of decimal places to 0.
4. In cell F3, click the IF function.
5. At the level of the formula line on the left side, click on
6. In the list, select Other functions, in the Logical category, select the function
AND.
7. In the field
Logical_value1: B3> = 50%,
Logical_value2: C3> = 50%,
Logical_value3: D3> = 50%.
8. Click in the formula bar on the IF.
9. In the field Value if true, type the words: "attestation"
10. In the Value field, if you want to type words: "not attestation"
11. Click on OK
12. The result should be stretched for other cells.
Exercise 4. Building 3D Surface Plots
z 3 x 2  2 sin 2 ( y ) y 2 x, y   1;1
1. Create a worksheet called Ex_4.
2. Enter the values of x in 0.2 steps as a column, starting from cell A2;
3. Enter the values of y in steps of 0.2 from cell B1;
4. In cell B2, write the formula: = 3 * A2 ^ 2 - 2 * SIN (B1) ^ 2 * B1 ^ 2;
After writing the formula and copying it to the remaining cells, observe the correct
change of addresses, conclude: the values of x must always be in column A, so in
order not to change the column, it is preceded by an absolute address, ie $ A, and y
The address of the first line changes to $ 1 according to the values.
5. Accordingly, we reduce the formula in cell B2 to: = 3 * $ А2 ^ 2 - 2 * SIN (B $
1) ^ 2 * B $ 1 ^ 2;
6. Subtract the remaining values of z using the fill marker;
7. To do this, mark the range B2: B12 and stretch the fill marker until the
corresponding rectangular area is filled.
9-сурет
Table x, y, z. mark with values;
Call the Chart Wizard, select Surface from the chart type;
Change the chart settings;
Place the chart on the left page and rename it Surface.
Tasks for independent work:
You should build х, у =[-1;1], Z= surface according to the following tasks
Variant Tasks
2 2 2 y
1 Z=5x cos (y) –2y e
2 Z=2x2cos2(x) –2y2
3 Z=2e0.2xx2 –2y4
4 Z=x2 –2e0.2yy2
5 Z=3x2sin2(x) –5e2yy
Control questions:
1. What is a spreadsheet?
2. What types of addressing you know? How do they difference from each other?
3. What is a chart?
4. How can you create charts? Could you tell about the types of charts?
5. What kind of ways can you enter a function in Excel?
6. What categories of functions do you know?
7. Give me examples of statistical functions, which you know
8. Give me examples of math functions, which you know
9. Give me examples of logical functions, which you know

Assignments 7

Uploaded by

Copyright:

Available Formats

Assignments 7

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Assignments 7

Uploaded by

Copyright:

Available Formats

Subject: «Spreadsheet processor MS Excel. Data entry and editing.

Description of the basic functions.

You might also like