Unit-2
Functions: Computing Sum, Average, Count, Max and Min, Computing Weighted Average,
Trigonometric functions, Exponential Functions, Using The CONVERT Function to Convert
Units
Computing Sum, Average, Count, Max and Min
To compute Sum, Average, Count, Max, and Min in Excel, you can use built-in functions.
Here are the steps:
1. SUM:
• Select the cell where you want the sum to appear.
• Use the formula: =SUM(range)
2. AVERAGE:
• Select the cell where you want the average to appear.
• Use the formula: =AVERAGE(range)
3. COUNT:
• Select the cell where you want the count to appear.
• Use the formula: =COUNT(range)
4. MAX (Maximum value):
• Select the cell where you want the maximum value to appear.
• Use the formula: =MAX(range)
5. MIN (Minimum value):
• Select the cell where you want the minimum value to appear.
• Use the formula: =MIN(range)
Numbers Result
1.416 SUM 12.67
1.334 Average 1.267
1.515 Count 10
1.328 Maximum 1.515
1.101 Minimum 0.84
1.319
1.264
1.245
0.84
1.308
Computing Weighted Average
To compute a Weighted Average in Excel, you follow these steps:
1. Organize your data:
o Place your values in one column (e.g., Column A).
o Place the corresponding weights in another column (e.g., Column B).
2. Multiply each value by its weight:
• In a new column (e.g., Column C), multiply each value by its corresponding weight.
3. Sum the weighted values:
• In another cell, sum the weighted values
4. Sum the weights:
• In another cell, sum the weights
5. Calculate the Weighted Average:
• Finally, divide the sum of the weighted values by the sum of the weights
Combined Formula:
You can also calculate the weighted average in a single formula without the intermediate steps:
=SUMPRODUCT(A2:A4, B2:B4) / SUM(B2:B4)
Computing Weighted Average
Voltage(V) Observations(n) V*n Sum of Obsevations 37
1.1 1 1.1 Sum of V*N 61.8
1.2 1 1.2 Weighted Average V 1.67027
1.3 2 2.6
1.4 2 2.8
1.5 3 4.5
1.6 6 9.6
1.7 9 15.3
1.8 6 10.8
1.9 3 5.7
2 2 4
2.1 2 4.2 −
∑(𝑉𝑖 ∗ 𝑛𝑖 )
𝑉=
∑ 𝑛𝑖
Trigonometric functions
To compute trigonometric functions such as sin, cos, and tan in Excel when an angle is given,
follow these steps:
Important Note:
Excel uses radians for trigonometric functions. If your angle is in degrees, you'll need to
convert it to radians first.
Steps:
1. Enter the Angle:
o In a cell, input the angle in degrees (e.g., cell A1 contains the angle 30 degrees).
2. Convert Degrees to Radians (if your angle is in degrees):
o Use the RADIANS function to convert the angle from degrees to radians.
3. Calculate the Sine (sin):
• To calculate the sine of an angle in degrees, use the SIN function and apply it to the
angle in radians. =SIN(RADIANS(A1))
4. Calculate the Cosine (cos):
• Use the COS function. =COS(RADIANS(A1))
5. Calculate the Tangent (tan):
• Use the TAN function. =TAN(RADIANS(A1))
Trignometric Functions
Angles SIN(angle) COS(angle) TAN(angle)
0 0 1 0
30 0.5 0.8660254 0.57735027
60 0.8660254 0.5 1.73205081
90 1 6.126E-17 1.6325E+16
-
120 0.8660254 -0.5 1.73205081
-
150 0.5 -0.8660254 0.57735027
-1.2251E-
180 1.225E-16 -1 16
210 -0.5 -0.8660254 0.57735027
240 -0.8660254 -0.5 1.73205081
270 -1 -1.838E-16 5.4415E+15
-
300 -0.8660254 0.5 1.73205081
-
330 -0.5 0.8660254 0.57735027
-2.4503E-
360 -2.45E-16 1 16
Exponential Functions
To compute exponential functions such as ene^nen, natural logarithm (ln), common
logarithm (log), and logarithm with a custom base (e.g., log base 2) in Excel, follow these
steps:
1. Exponential Function(Euler’s Number Raised to Power n)
• In Excel, use the EXP function to compute ene^nen.
• Formula: =EXP(n)
2. Natural Logarithm (ln or log base eee)
• Use the LN function to compute the natural logarithm.
• Formula: =LN(number)
3. Common Logarithm (log base 10)
• Use the LOG10 function to compute the logarithm base 10.
• Formula: =LOG10(number)
4. Logarithm with a Custom Base (e.g., log base 2)
• Use the LOG function to compute the logarithm for any base.
• Formula: =LOG(number, base)
Exponential functions
𝑒𝑛 𝑙𝑜𝑔2 (𝑛)
n ln(n) log(n)
2 7.389056 0.693147 0.30103 1
5 148.4132 1.609438 0.69897 2.321928
8 2980.958 2.079442 0.90309 3
10 22026.47 2.302585 1 3.321928
15 3269017 2.70805 1.176091 3.906891
Convert Unit
In Excel, you can use the CONVERT function to convert units, including converting
millimeters (mm) to meters (m) and other unit conversions. Here’s how you can use the
CONVERT function:
Syntax of the CONVERT Function: =CONVERT(number, from_unit, to_unit)
number: The value you want to convert.
from_unit: The unit you are converting from (enclosed in quotation marks).
to_unit: The unit you are converting to (enclosed in quotation marks).
inch m
3.13 0.079502
4.1 0.10414
0.25 0.00635
6.38 0.162052