Catégorie : Excel function

This function returns the hyperbolic tangent of a number.

Syntax
TANH(number)

Argument

• number (required) – Any real number.

Background

• The hyperbolic tangent is part of the hyperbolic functions, which (like trigonometric functions) are defined for all real and complex numbers. However, Excel only supports real-number arguments for hyperbolic functions.
• The formula for the hyperbolic tangent is:

The last term highlights its similarity to trigonometric functions.

• The hyperbolic tangent is widely used in engineering and natural sciences for research and development (see Figure below).

Missing Cotangent Functions

• Unlike trigonometric functions (sin, cos, tan, cot), Excel does not include built-in hyperbolic cotangent (COTH) or trigonometric cotangent (COT) functions.
• However, both can be derived as reciprocals of their tangent counterparts:
  • Trigonometric cotangent:

• • Hyperbolic cotangent:

• Workaround in Excel:
  Instead of =COTH(A1) (which does not exist), use:

=1/TANH(A1)

Example: Wave Propagation Speed Calculation

Application:
The hyperbolic tangent is used to calculate the propagation speed (υυ) of waves, given:

• Gravity acceleration (gg) in [m/s²],
• Wave length (λλ) in [m],
• Water depth (hh) in [m].

Formula:​

Approximations for Shallow/Deep Water:

• Shallow water (h≪λh≪λ):

• Deep water (h≫λh≫λ):

Additional Examples

=TANH(0)    // Returns 0

=TANH(1)    // Returns 0.761594156

=TANH(-1)   // Returns -0.76159416

=TANH(10)   // Returns 1 (approaches asymptotically)

=TANH(-10)  // Returns -1 (approaches asymptotically)

Key Properties:

• Output ranges between -1 and 1.
• For large positive/negative inputs, TANH() approaches ±1.

This function returns the tangent of an angle.

Syntax
TAN(number)

Argument

• number (required) – The angle in radians for which you want the tangent.

Background

• In a right triangle, the tangent of an angle is the ratio of the length of the opposite side to the length of the adjacent side.
• The TAN() function requires the angle to be in radians. If the angle is given in degrees, convert it by multiplying by PI()/180 or using the RADIANS() function.
• For an angle α in a unit circle (r=1):
  • As α increases from 0° to 90°, the tangent increases from 00 to +∞+∞ (see Figure below).

• In a coordinate system, plotting the angle αα on the x-axis and tan⁡(α)tan(α) on the y-axis produces the curve shown in Figure below.

Tangent Relationship with Sine and Cosine:

Practical Application (Slope Calculation):

• The tangent describes the relationship between the gradient angle and the slope of a line.
  • Example: A road with a gradient angle of 12°12° has a slope of tan⁡(12°)≈0.21tan(12°)≈0.21, often displayed as 21% (21 meters elevation per 100 meters horizontally).
• Note: The tangent is undefined at 90° and −90° (vertical slope).

Example:

Key Steps:

• Convert degrees to radians (RADIANS()).
• Calculate tangent (TAN()).
• Multiply by distance and round (ROUND()).
• Add observer’s eye level.

This function returns the sum of squares of differences of corresponding values in two arrays.

Syntax
SUMXMY2(array_x; array_y)

Arguments

• array_x(required) – The first array or range of values.
• array_y(required) – The second array or range of values.

Background

• The arguments should be numbers, names, arrays, or references containing numbers.
• If an array or a reference argument contains text, logical values, or empty cells, those values are ignored. However, cells with the value zero are included.
• If array_xand array_y have a different number of values, the SUMXMY2() function returns the #N/A

The equation for the sum of squared differences is:
Σ(x – y)²

The solution of this equation is built for the example (see Figure below) as follows:

1. In two specified ranges:
   • Range A:4, 5
   • Range B:2, 3

The corresponding values are subtracted:

• First pair: 4 – 2 = 2
• Second pair: 5 – 3 = 2

2. The differences are squared and summed (see Figure 16-37):
   • 2² + 2² = 4 + 4 = 8

This function returns the sum of the sum of squares of corresponding values in two arrays. The sum of the sum of squares is a common term in many statistical calculations.

Syntax
SUMX2PY2(array_x; array_y)

Arguments

• array_x(required) – The first array or range of values.
• array_y(required) – The second array or range of values.

Background

• The arguments should be numbers, names, arrays, or references containing numbers.
• If an array or a reference argument contains text, logical values, or empty cells, those values are ignored. However, cells with the value zero are included.
• If array_xand array_y have a different number of values, the SUMX2PY2() function returns the #N/A

The equation for the sum of the sum of squares is:
Σ(x² + y²)

EXAMPLE

The solution of this equation is built for the example (see Figure below) as follows:

1. In two specified ranges:
   • Range A:4, 5
   • Range B:2, 3

The square of each value is calculated:

• Range A:16, 25
• Range B:4, 9

2. The squares of all values in each range are summed:
   • Range A:16 + 25 = 41
   • Range B:4 + 9 = 13
3. The sums are added together (see Figure 16-36):
   41 + 13 = 54

This function returns the sum of the difference of squares of corresponding values in two arrays .

Syntax

SUMX2MY2(array_x; array_y)

Arguments

• array_x (required) – The first array or range of values.
• array_y (required) – The second array or range of values.

Background

• The arguments should be numbers or names, arrays, or references containing numbers.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored. However, cells with the value zero are included.
• If array_x and array_y have a different number of values, the SUMX2MY2() function returns the #N/A error.

The equation for the sum of the difference of squares is:
Σ(x² – y²)

EXAMPLE

The solution for this equation is built for the example (see Figure below) as follows:

1. In two specified ranges:
   • Range A: 4, 5
   • Range B: 2, 3

The square of each value is calculated:

1. • Range A: 16, 25
   • Range B: 4, 9
2. The squared values are summed:
   • Range A: 16 + 25 = 41
   • Range B: 4 + 9 = 13
3. The sums are subtracted (see Figure below):
   41 – 13 = 28

Its multiplies corresponding components in given arrays and returns the sum of those products.

Syntax:
SUMPRODUCT(array1; [array2]; [array3]; …)

Arguments:

Argument	Description
array1 (required)	First array of values
array2 (required)	Second array of values
array3,… (optional)	Additional arrays (up to 255 in modern Excel)

Key Features:

1. Calculation Method:
   • Performs element-wise multiplication (a₁×b₁, a₂×b₂,…)
   • Sums all resulting products
   • Formula: Σ(array1[i] × array2[i] × …)
2. Requirements:
   • All arrays must have identical dimensions
   • Non-numeric values are treated as zero
   • Returns #VALUE! if arrays have different sizes

Example: Product Price Calculation

Common Errors:

• #VALUE!: Array size mismatch
• Incorrect results: Hidden text values treated as zero

Related Functions:

• SUM(): Simple addition
• MMULT(): Matrix multiplication
• SUMIFS(): Conditional sum with multiple criteria

Note:
While originally designed for simple array multiplication, SUMPRODUCT has become a powerful tool for complex array operations in Excel.