Astra

Engineering Functions in Excel | Excel for Engineers

Estimated reading: 15 minutes

Excel is a capable spreadsheet application that is popular across many industries, including engineering. Engineers and other professionals in technical domains depend on it since it offers a wide range of features that can be utilized to carry out sophisticated calculations and analysis. These engineering routines can assist engineers with unit conversion, computations, data analysis, and other tasks. Engineers can focus on other crucial areas of their work by using these functionalities effectively, which can save them a lot of time and effort. 

CLICK HERE TO DOWNLOAD ENGINEERING FUNCTION  EXAMPLES – EXCEL  SPREADSHEETS

Type #1: CONVERT Function in Excel

You can convert a value from one unit of measurement to another using Excel’s CONVERT function, which is a powerful function. It can be used to convert between several unit types, including those for time, temperature, mass, and distance.

Syntax of the CONVERT Function:

=CONVERT(number,from_unit,to_unit)

Argument of CONVERT Function:

number: the value you wish to convert, denoted by the number.

from_unit: The value’s original unit of measurement.

to_unit – The unit of measurement you want to convert the value to is specified by the parameter to unit.

Unit List of CONVERT Function

Click Here to download unit list PDF version
Engineering Functions in Excel units of convert function
Engineering Functions in Excel prefix of convert function

Examples: Simple Application of CONVERT Function

  • Miles to kilometers conversion: 
=CONVERT (10,"km","mi") //10 kilometers can be converted to miles.
  • Fahrenheit to Celsius conversion: 
=CONVERT (25,"C","F") //25 degrees Celsius will be converted to Fahrenheit.
  • Pounds to kilos conversion: 
=CONVERT (150,"lbm","kg") //150 pounds will be converted to kilos
  • Liters to US gallons conversion: 
=CONVERT (20,"L","gal")  //20 liters can be converted to US gallons.
  • Calculating kilometers per hour from meters per second:
=CONVERT(50,"m/s","km/h") //50 meters per second will be converted to kilometers per hour
Click here to Download Unit Converter in Excel for Engineers

Type #2: Bessel Functions in Excel

The Bessel functions are employed in many technical and scientific applications, including the solution of differential equations that mimic physical events, the simulation of heat transfer in materials, and the study of the behavior of electromagnetic waves. It is an effective tool for evaluating complicated systems and can assist scientists and engineers in resolving issues that would be challenging or impossible to resolve using other approaches.

However, Excel helps you to calculate the Bessel values by using the following formulas:

Function BESSELI BESSELJ BESSELK BESSELY
Syntax =BESSELI(ν, x) =BESSELJ(ν, x) =BESSELK(ν, x) =BESSELY(ν, x)
Argument
  • ν is a real number whose value is to be evaluated
  • x is the order of the Bessel function and positive real number 
Return In(x) (modified Bessel function) Jn(x) (Bessel function) Kn(x) (modified Bessel function ) Yn(x) (Bessel function)
Common Errors #VALUE! error value if x and ν are not a number. #NUM! error value if ν is less than 0.

Examples: Application of Bessel functions

Type #3:  Converting Between Number System

Functions to convert Binary to Decimal, Hexadecimal, Octal Number System

Function BIN2DEC  BIN2HEX BIN2OCT
Syntax =BIN2DEC(N) =BIN2HEX(N,places) =BIN2OCT(N,places)
Argument
  • N is a binary number whose value is to be converted, no more than 10 characters or bits may be inserted.
  • The minimum number of characters you want the outcome to have is “places” (optional). 
Return Decimal Number Hexadecimal Number Octal Number
Common Errors
  • #NUM! error value if N is not a valid binary number or more than 10 bits. #NUM! error value if the places is less than 0.
  • #VALUE! If the places is not a numeric value, error ensues.

Examples: Converting  Binary Number to Decimal, Hexadecimal, Octal Number in Excel

Example #1: BIN2DEC in Excel for Large Number / More than 10 bits (16 bits and 32 bits)

Converting 16 bit Binary Number to Decimal Number

=BIN2DEC(LEFT(B3,8))*2^8+BIN2DEC(RIGHT(B3,8))
BIN2DEC in Excel for Large Number

Explanation:

  • The binary number in cell B3’s first 8 characters (bits) are extracted using the formula LEFT(B3,8). This produces a text value that represents the binary number’s bits.
  • The text value is converted to its decimal counterpart by BIN2DEC(LEFT(B3,8)). This provides the binary number’s 1st 8 bits’ decimal value.
  • The binary number in cell B3’s last 8 characters (bits) are extracted using the formula RIGHT(B3,8). This produces a text value representing the binary number’s last 8 bits. BIN2DEC(RIGHT(B3,8)) converts those to a decimal value.
  • BIN2DEC(LEFT(B3,8))*2^8 means that the first eight bits are moved left by eight locations as a result of this multiplication by two and raising to the power of eight.
  • The decimal equivalent of the original binary number is obtained by adding the decimal values acquired using the formula BIN2DEC(LEFT(B3,8))*2^8+BIN2DEC(RIGHT(B3,8)).

Converting 32 bit Binary Number to Decimal Number

Similarly, you can convert 32 bit Binary Number to Decimal Number applying the following formula:

=BIN2DEC(MID(B6,1,8))*2^24+BIN2DEC(MID(B6,9,8))*2^16+BIN2DEC(MID(B6,17,8))*2^8+BIN2DEC(MID(B6,25,8))
BIN2DEC in Excel for Large Number 32 bit

Functions to Convert Decimal to Binary, Hexadecimal, Octal Number System

Function DEC2BIN DEC2HEX DEC2OCT
Syntax =DEC2BIN(N,places) =DEC2HEX(N,places) =DEC2OCT(N,places)
Argument
  • N is a Decimal number whose value is to be converted, no more than 10 characters or bits may be inserted.
  • The minimum number of characters you want the outcome to have is “places” (optional). 
Return Binary Number Hexadecimal Number Octal Number
Common Errors
  • #NUM! error value if N is less than -512 or greater than 511.
  • #NUM! error value if the places is less than 0 or greater than 10.
  • #VALUE! If the places is not a numeric value, error ensues.

Similarly, you can apply the following Functions

  • HEX2BIN -Hexadecimal to binary conversion 
  • HEX2DEC -Hexadecimal to decimal conversion 
  • HEX2OCT -Hexadecimal number to an octal number
  • OCT2BIN -converts an octal number to binary
  • OCT2DEC -converts an octal number to decimal
  • OCT2HEX -Hexadecimal to octal number conversion

Examples:

Type #4:  Formula for BIT Wise Logics

Function BITAND BITOR BITXOR
Syntax =BITAND( N1, N2 ) =BITOR( N1, N2 ) =BITXOR( N1, N2 )
Argument N1 and N2 are positive integers
Return Common bits of N1 & N2 in decimal system bits of N1 & N2 returned in the decimal system after applying OR logic   bits of N1 & N2 returned in the decimal system after applying XOR logic  
Common Errors
  • #NUM! error value if N1 or N2 is not an integer.
  • #NUM! error value if the numbers  are less than 0 or greater than 2^48-1
  • #VALUE! If the numbers are not numeric values, error ensues.

Examples:

Explanation:

  • BITAND: Only when both bits of N1 and N2 in the same position are 1, the BITAND function returns 1.

For columns A & B, the values of N1 & N2 are 1  so BITAND Function returns 1

For column C, the values of N1 & N2 are 0  so BITAND Function returns 0

For column D, the values of N1 & N2 are not the same so BITAND Function returns 0

For better understanding you can follow the article on AND Function

  • BITOR: returns 1 if any one or both bit of N1 and N2 in the same position is 1

For the 5th column (from left), N1 = 1 and N2 =0, so according to the operation of OR logic 1+0 = 1 is returned.

For better understanding you can follow the article on OR Function

  • BITXOR: returns 0 only if both bit of N1 and N2 in the same position is 1 or 0. In other words, BITXOR returns 0 if both bit of N1 and N2 in the same position has the same value.

For better understanding you can follow the article on XOR Function

Type #5:  Formula for BIT Wise Shifting

The bitwise operator, represented by the BITLSHIFT and BITRSHIFT functions, is helpful for low-level programming tasks like working with binary data or changing bits in hardware.

Function BITLSHIFT BITRSHIFT
Syntax =BITLSHIFT( N1, N2 ) =BITRSHIFT( N1, N2 )
Argument
  • N1 is a positive integer
  • N2 is a positive integer and refers to the number of bits wants to be shifted
Return Number in Decimal number after shifting bits 
Common Errors
  • #NUM! error value if N1 or N2 is not an integer.
  • #NUM! error value if the numbers  are less than 0 or greater than 2^48-1
  • #VALUE! If the numbers are not numeric values, error ensues.

Examples:

Explanation:

BITLSHIFT: The function shifts the binary digits of the number 5 (101) two positions to the left while adding zeros to the right. It then translates the number 5’s decimal representation to binary. (resulting in 10100). After that, 20 is obtained by converting the shifted binary representation back to a decimal number. Hence, the formula outputs the value 20.

BITRSHIFT: The function moves the binary digits (101) of the decimal number 5 two positions to the right, deleting the rightmost bits, before converting it to its binary form (resulting in 1). The next step is to translate the shifted binary representation back to a decimal value, which is 1. As a result, the formula outputs 1

Type #6: Functions to Compare Numeric Values:

Function DELTA GESTEP
Syntax =DELTA( N1, N2 ) =GESTEP( N1, N2)
Argument
  • N1 is a positive integer
  • N2 is a positive integer and refers to the number that N2 will be compared.
Return Returns TRUE if N1 = N2, otherwise, FALSE Returns TRUE if N1 > N2 or N1=N2, Otherwise, FALSE
Common Errors #VALUE! If the numbers are not numeric values, error ensues.

Examples: Simple application of DELTA, GSTEP Function

Type #7: Functions for Complex Number system

COMPLEX Function: Format Complex Numbers in Excel

A complex number is one that can be written in the formula a + bi, where i is the imaginary unit, which is equal to the square root of -1, and a, b are real integers.

A complex number has two parts: a and b, the real and imaginary halves respectively.

The built-in COMPLEX function allows you to calculate complex numbers.

Syntax of Complex Function

=COMPLEX(real_num, [i_num], [suffix])

Argument of COMPLEX Function

real_num: is the complex number’s real component

i_num(optional): is its optional imaginary component (if omitted, it is taken to be zero)

suffix (optional): is a suffix that denotes the complex number’s format (either “i” or “j”; default is “i”).

Examples:

Format complex numbers in excel

Function to Separate Imaginary and Real Parts of a Complex Number

Function IMREAL IMAGINARY
Syntax =IMREAL( N ) =IMAGINARY(N )
Argument N is a valid complex number.
Return Real part of a complex number  Imaginary part of a complex number 
Common Errors
  • #VALUE! occurs If the number N contains any logical value
  • #NUM! occurs If the number N is not a valid complex number

Examples:

Function IMABS:  Find Magnitude (absolute value) of a Complex Number in Excel

Magnitude (absolute value) of a Complex Number in Excel

Syntax of IMABS Function

=IMABS(N )

Syntax of IMABS Function

N is a valid complex number.

Return of IMABS Function

Returns the magnitude(z) of complex number

Examples:

Function IMARGUMENT : Find the Phase of a Complex Number in Excel

The IMARGUMENT function allows you to determine a complex number’s phase or argument. The angle formed in the complex plane between the positive real axis and the line leading from the origin to the complex number is known as the phase of a complex number.

Argument or phase of Complex Number = θ = Tan-1(b/a)

Syntax of  IMARGUMENT function 

=IMARGUMENT(N)

Argument of IMARGUMENT Function

N is the complex number you wish to determine the phase for. 

The argument must be a complex number, either as a direct value surrounded in double quotes or as a reference to a cell that holds a complex number.

Examples:

Find the Phase of a Complex Number in Excel

Function IMCONJUGATE: Find the conjugate number of a complex number in Excel.

If a + bi, where i is the imaginary unit, can be written, it is a complex number.

The formula z* = a – bi yields the complex conjugate of the complex number z.

Syntax of  IMCONJUGATE Function

=IMCONJUGATE( N )

Argument of IMCONJUGATE Function

N is a valid complex number.

Examples:

Functions for Addition and subtraction of Complex numbers in Excel

To add or subtract complex numbers, we simply add or subtract their real and imaginary parts separately.
For example, to add two complex numbers z1 = a1 + b1i and z2 = a2 + b2i, we add their real parts and imaginary parts separately:
z1 + z2 = (a1 + a2) + (b1 + b2)i
Similarly, to subtract two complex numbers z1 and z2, we subtract their real parts and imaginary parts separately:
z1 – z2 = (a1 – a2) + (b1 – b2)i

Let’s see how Excel makes addition and subtraction simple.

Function IMSUM IMSUB
Syntax IMSUM( N1, N2, … ) IMSUB( N1, N2 )
Argument N1, N2 are the valid complex numbers. 2 to 255 complex numbers can be inserted in a single formula.
Returns the resulting complex number after multiplication. the resulting complex number after subtracting.
Common Errors
  • #NUM! error occurs if N1, and N2 are not valid complex numbers.
  • #VALUE! If the numbers contain logical values.

Examples:

Addition and subtraction of Complex numbers in Excel

Functions for Multiplication and Division of Complex Numbers in Excel

FunctionIMPRODUCTIMDIV
SyntaxIMPRODUCT( N1, N2, … )IMDIV( N1, N2 )
ArgumentN1, N2 are the valid complex numbers.
2 to 255 complex numbers can be inserted in a single formula.		N1 is the valid complex number and numerator
N2 is the valid complex number and the denominator
Returnsthe resulting complex number after multiplication.the resulting complex number after dividing.
Common Errors#NUM! error occurs if N1 and N2 are not valid complex numbers.
#VALUE! If the numbers contain logical values.		#NUM! error occurs if N2 =0
#VALUE! If the numbers contain logical values.

Examples: 

Multiplication and Division of Complex Numbers in Excel

Functions to Find power and Squire Root of Complex numbers in Excel

Function IMSQRT(N ) IMPOWER
Syntax =IMSQRT(N ) =IMPOWER( N1, N2 )
Argument N is a valid complex number. N1 is a valid complex number.  N2 is a number at which power to be raised on N1
Return Square root of a complex number  the Complex number after raising to power N2
Common Errors
  • #VALUE! occurs If numbers contain any logical value
  • #NUM! occurs If the number is not a valid complex number

Example:

Find power and Squire Root of Complex numbers in Excel

Formula to find Sine and Cosine of a complex number in Excel

Function IMSIN IMCOS
Syntax =IMSIN( N) =IMCOS( N)
Argument N is a valid complex number.
Return Sine of a complex number  Cosine of a complex number 
Common Errors
  • #VALUE! occurs If numbers contain any logical value
  • #NUM! occurs If the number is not a valid complex number

Similarly, you can find the hyperbolic sine and hyperbolic cosine of any complex number by  IMSINH( N) and IMCOSH( N) functions, respectively.

Examples:

Formula to Find the Tangent and Cotangent of a Complex Number in Excel

Function IMTAN IMCOT
Syntax =IMTAN( N) =IMCOT( N)
Argument N is a valid complex number.
Return Tangent of a complex number  Cotangent of a complex number 
Common Errors
  • #VALUE! occurs If numbers contain any logical value
  • #NUM! occurs If the number is not a valid complex number

Examples:

Formula to Find the Secant and Cosecant of a Complex Number in Excel

Function IMSEC IMCSC
Syntax =IMSEC( N) =IMCSC( N)
Argument N is a valid complex number.
Return secant of a complex number  Cosecant of a complex number 
Common Errors
  • #VALUE! occurs If numbers contain any logical value
  • #NUM! occurs If the number is not a valid complex number

Similarly, you can find the hyperbolic secant and hyperbolic cosecant of any complex number by the IMSECH(N) and IMCSCH(N) functions, respectively.

Examples:

Formula to Find the Exponential and Logarithms of a Complex Number in Excel

Function IMEXP IMLN IMLOG10 IMLOG2
Syntax =IMEXP( N) =IMLN( N) =IMLOG10(N) =IMLOG2(N)
Argument N is a valid complex number.
Return complex number’s exponential complex number’s natural logarithms  base 10 logarithms of a complex number  base 2 logarithms of a complex number  
Common Errors
  • #VALUE! occurs If numbers contain any logical value
  • #NUM! occurs If the number is not a valid complex number

Examples:

Example #2 : Convert Complex Numbers to Polar Form in Excel

To convert the complex number in rectangular format to polar format, apply the following formula:

=IMABS(B3)&"(cos "&IMARGUMENT(B3)&" + isin "&IMARGUMENT(B3)&")"

Apply DEGREES Function as below to convert the radians to degrees

=IMABS(B3)&"(cos "&DEGREES(IMARGUMENT(B3))&" + isin "&DEGREES(IMARGUMENT(B3))&")"

Apply ROUND Function as below to make a better version of output

=ROUND(IMABS(B3),2)&"(cos "&ROUND(DEGREES(IMARGUMENT(B3)),2)&"°"&" + isin "&ROUND(DEGREES(IMARGUMENT(B3)),2)&"°)"

Explanation:

Using the IMABS and IMARGUMENT functions, which return the complex number’s magnitude and argument, respectively. To round the data to two decimal places, use the ROUND function.

The “&” operator is then used in the formula to concatenate the magnitude and angle parts of the polar form with formatted text. Magnitude(cos angle° + isin angle°) is the format of the resulting string.

Type #8: Engineering Error Functions in Excel

Function ERF ERF.PRECISE ERFC ERFC.PRECISE
Syntax =ERF( N1, N2 ) =ERF.PRECISE( N ) =ERFC(N) =ERFC.PRECISE(N)
Argument

N1 implies the lower limit of the integral

N2 implies the upper limit of the integral (if omitted, N2=0)

 N implies the lower limit of the integral N implies the lower limit of the integral N implies the lower limit of the integral
Return Calculated error function Calculated error function of integral taking the lower limit to N and another to zero Calculated complementary error function of integral taking the lower limit to N and upper limit to zero Calculated complementary error function of integral taking the lower limit to N and upper limit to infinite 
Common Errors #VALUE! occurs If numbers are not numeric

Examples:

Engineering error function in excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Share this Doc
Jump to Topics

Powered By SOLVED EXCEL

SOLVED EXCEL
Scroll to Top