Excel 2007

birol_mumcu · 30 Kasım 2006

5.BIN2DEC
Converts a binary number to decimal.

Syntax

BIN2DEC(number)

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 notation.

Remark

If number is not a valid binary number, or if number contains more than 10 characters (10 bits), BIN2DEC returns the #NUM! error value.

Example

=BIN2DEC(1100100) Converts binary 1100100 to decimal (100)
=BIN2DEC(1111111111) Converts binary 1111111111 to decimal (-1)

birol_mumcu · 30 Kasım 2006

6. BIN2HEX
Converts a binary number to hexadecimal.

Syntax

BIN2HEX(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 notation.

Places is the number of characters to use. If places is omitted, BIN2HEX uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number is not a valid binary number, or if number contains more than 10 characters (10 bits), BIN2HEX returns the #NUM! error value.
If number is negative, BIN2HEX ignores places and returns a 10-character hexadecimal number.
If BIN2HEX requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, BIN2HEX returns the #VALUE! error value.
If places is negative, BIN2HEX returns the #NUM! error value.
Example

=BIN2HEX(11111011, 4) Converts binary 11111011 to hexadecimal with 4 characters (00FB)
=BIN2HEX(1110) Converts binary 1110 to hexadecimal (E)
=BIN2HEX(1111111111) Converts binary 1111111111 to hexadecimal (FFFFFFFFFF)

birol_mumcu · 30 Kasım 2006

7.BIN2OCT
Converts a binary number to octal.

Syntax

BIN2OCT(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 notation.

Places is the number of characters to use. If places is omitted, BIN2OCT uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number is not a valid binary number, or if number contains more than 10 characters (10 bits), BIN2OCT returns the #NUM! error value.
If number is negative, BIN2OCT ignores places and returns a 10-character octal number.
If BIN2OCT requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, BIN2OCT returns the #VALUE! error value.
If places is negative, BIN2OCT returns the #NUM! error value.
Example

=BIN2OCT(1001, 3) Converts binary 1001 to octal with 3 characters (011)
=BIN2OCT(1100100) Converts binary 1100100 to octal (144)
=BIN2OCT(1111111111) Converts binary 1111111111 to octal (7777777777)

birol_mumcu · 30 Kasım 2006

8.COMPLEX
Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj.

Syntax

COMPLEX(real_num,i_num,suffix)

Real_num is the real coefficient of the complex number.

I_num is the imaginary coefficient of the complex number.

Suffix is the suffix for the imaginary component of the complex number. If omitted, suffix is assumed to be "i".

Note All complex number functions accept "i" and "j" for suffix, but neither "I" nor "J". Using uppercase results in the #VALUE! error value. All functions that accept two or more complex numbers require that all suffixes match.

Remarks

If real_num is nonnumeric, COMPLEX returns the #VALUE! error value.
If i_num is nonnumeric, COMPLEX returns the #VALUE! error value.
If suffix is neither "i" nor "j", COMPLEX returns the #VALUE! error value.
Example

=COMPLEX(3,4) Complex number with 3 and 4 as the real and imaginary coefficients (3 + 4i)
=COMPLEX(3,4,"j") Complex number with 3 and 4 as the real and imaginary coefficients, and j as the suffix (3 + 4j)
=COMPLEX(0,1) Complex number with 0 and 1 as the real and imaginary coefficients (i)
=COMPLEX(1,0) Complex number with 1 and 0 as the real and imaginary coefficients (1)

birol_mumcu · 30 Kasım 2006

9.CONVERT
Converts a number from one measurement system to another. For example, CONVERT can translate a table of distances in miles to a table of distances in kilometers.

Syntax

CONVERT(number,from_unit,to_unit)

Number is the value in from_units to convert.

From_unit is the units for number.

To_unit is the units for the result. CONVERT accepts the following text values (in quotation marks) for from_unit and to_unit.

Weight and mass From_unit or to_unit
Gram "g"
Slug "sg"
Pound mass (avoirdupois) "lbm"
U (atomic mass unit) "u"
Ounce mass (avoirdupois) "ozm"

Distance From_unit or to_unit
Meter "m"
Statute mile "mi"
Nautical mile "Nmi"
Inch "in"
Foot "ft"
Yard "yd"
Angstrom "ang"
Pica (1/72 in.) "Pica"

Time From_unit or to_unit
Year "yr"
Day "day"
Hour "hr"
Minute "mn"
Second "sec"

Pressure From_unit or to_unit
Pascal "Pa" (or "p")
Atmosphere "atm" (or "at")
mm of Mercury "mmHg"

Force From_unit or to_unit
Newton "N"
Dyne "dyn" (or "dy")
Pound force "lbf"

Energy From_unit or to_unit
Joule "J"
Erg "e"
Thermodynamic calorie "c"
IT calorie "cal"
Electron volt "eV" (or "ev")
Horsepower-hour "HPh" (or "hh")
Watt-hour "Wh" (or "wh")
Foot-pound "flb"
BTU "BTU" (or "btu")

Power From_unit or to_unit
Horsepower "HP" (or "h")
Watt "W" (or "w")

Magnetism From_unit or to_unit
Tesla "T"
Gauss "ga"

Temperature From_unit or to_unit
Degree Celsius "C" (or "cel")
Degree Fahrenheit "F" (or "fah")
Kelvin "K" (or "kel")

Liquid measure From_unit or to_unit
Teaspoon "tsp"
Tablespoon "tbs"
Fluid ounce "oz"
Cup "cup"
U.S. pint "pt" (or "us_pt")
U.K. pint "uk_pt"
Quart "qt"
Gallon "gal"
Liter "l" (or "lt")

The following abbreviated unit prefixes can be prepended to any metric from_unit or to_unit.

Prefix Multiplier Abbreviation
exa 1E+18 "E"
peta 1E+15 "P"
tera 1E+12 "T"
giga 1E+09 "G"
mega 1E+06 "M"
kilo 1E+03 "k"
hecto 1E+02 "h"
dekao 1E+01 "e"
deci 1E-01 "d"
centi 1E-02 "c"
milli 1E-03 "m"
micro 1E-06 "u"
nano 1E-09 "n"
pico 1E-12 "p"
femto 1E-15 "f"
atto 1E-18 "a"

Remarks

If the input data types are incorrect, CONVERT returns the #VALUE! error value.
If the unit does not exist, CONVERT returns the #N/A error value.
If the unit does not support an abbreviated unit prefix, CONVERT returns the #N/A error value.
If the units are in different groups, CONVERT returns the #N/A error value.
Unit names and prefixes are case-sensitive.
Example

=CONVERT(1.0, "lbm", "kg") Converts 1 pound mass to kilograms (0.453592)
=CONVERT(68, "F", "C") Converts 68 degrees Fahrenheit to Celsius (20)
=CONVERT(2.5, "ft", "sec") Data types are not the same so an error is returned (#N/A)
=CONVERT(CONVERT(100,"ft","m"),"ft","m") Converts 100 square feet into square meters (9.290304).

birol_mumcu · 30 Kasım 2006

10.DEC2BIN
Converts a decimal number to binary.

Syntax

DEC2BIN(number,places)

Number is the decimal integer you want to convert. If number is negative, valid place values are ignored and DEC2BIN returns a 10-character (10-bit) binary number in which the most significant bit is the sign bit. 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, DEC2BIN uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number < -512 or if number > 511, DEC2BIN returns the #NUM! error value.
If number is nonnumeric, DEC2BIN returns the #VALUE! error value.
If DEC2BIN requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, DEC2BIN returns the #VALUE! error value.
If places is zero or negative, DEC2BIN returns the #NUM! error value.
Example

=DEC2BIN(9, 4) Converts decimal 9 to binary with 4 characters (1001)
=DEC2BIN(-100) Converts decimal -100 to binary (1110011100)

birol_mumcu · 30 Kasım 2006

11.DEC2HEX
Converts a decimal number to hexadecimal.

Syntax

DEC2HEX(number,places)

Number is the decimal integer you want to convert. If number is negative, places is ignored and DEC2HEX returns a 10-character (40-bit) hexadecimal number in which the most significant bit is the sign bit. The remaining 39 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, DEC2HEX uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number < -549,755,813,888 or if number > 549,755,813,887, DEC2HEX returns the #NUM! error value.
If number is nonnumeric, DEC2HEX returns the #VALUE! error value.
If DEC2HEX requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, DEC2HEX returns the #VALUE! error value.
If places is negative, DEC2HEX returns the #NUM! error value.
Example

=DEC2HEX(100, 4) Converts decimal 100 to hexadecimal with 4 characters (0064)
=DEC2HEX(-54) Converts decimal -54 to hexadecimal (FFFFFFFFCA)

birol_mumcu · 30 Kasım 2006

12.DEC2OCT
Converts a decimal number to octal.

Syntax

DEC2OCT(number, places)

Number is the decimal integer you want to convert. If number is negative, places is ignored and DEC2OCT returns a 10-character (30-bit) octal number in which the most significant bit is the sign bit. The remaining 29 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, DEC2OCT uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number < -536,870,912 or if number > 536,870,911, DEC2OCT returns the #NUM! error value.
If number is nonnumeric, DEC2OCT returns the #VALUE! error value.
If DEC2OCT requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, DEC2OCT returns the #VALUE! error value.
If places is negative, DEC2OCT returns the #NUM! error value.
Example

=DEC2OCT(58, 3) Converts decimal 58 to octal (072)
=DEC2OCT(-100) Converts decimal to octal (7777777634)

birol_mumcu · 30 Kasım 2006

13.DELTA
Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise. Use this function to filter a set of values. For example, by summing several DELTA functions you calculate the count of equal pairs. This function is also known as the Kronecker Delta function.

Syntax

DELTA(number1,number2)

Number1 is the first number.

Number2 is the second number. If omitted, number2 is assumed to be zero.

Remarks

If number1 is nonnumeric, DELTA returns the #VALUE! error value.
If number2 is nonnumeric, DELTA returns the #VALUE! error value.
Example

=DELTA(5, 4) Checks whether 5 equals 4 (0)
=DELTA(5, 5) Checks whether 5 equals 5 (1)
=DELTA(0.5, 0) Checks whether 0.5 equals 0 (0)

birol_mumcu · 30 Kasım 2006

14.ERF
Returns the error function integrated between lower_limit and upper_limit.

Syntax

ERF(lower_limit,upper_limit)

Lower_limit is the lower bound for integrating ERF.

Upper_limit is the upper bound for integrating ERF. If omitted, ERF integrates between zero and lower_limit.

Remarks

If lower_limit is nonnumeric, ERF returns the #VALUE! error value.
If lower_limit is negative, ERF returns the #NUM! error value.
If upper_limit is nonnumeric, ERF returns the #VALUE! error value.
If upper_limit is negative, ERF returns the #NUM! error value.

Example

=ERF(0.74500) Error function integrated between 0 and 0.74500 (0.707929)
=ERF(1) Error function integrated between 0 and 1 (0.842701)

birol_mumcu · 30 Kasım 2006

15.ERFC
Returns the complementary ERF function integrated between x and infinity.

Syntax

ERFC(x)

X is the lower bound for integrating ERF.

Remarks

If x is nonnumeric, ERFC returns the #VALUE! error value.
If x is negative, ERFC returns the #NUM! error value.

Example

=ERFC(1) Complementary ERF function of 1 (0.1573)

birol_mumcu · 30 Kasım 2006

16.GESTEP
Returns 1 if number ≥ step; returns 0 (zero) otherwise. Use this function to filter a set of values. For example, by summing several GESTEP functions you calculate the count of values that exceed a threshold.

Syntax

GESTEP(number,step)

Number is the value to test against step.

Step is the threshold value. If you omit a value for step, GESTEP uses zero.

Remark

If any argument is nonnumeric, GESTEP returns the #VALUE! error value.

Example

=GESTEP(5, 4) Checks whether 5 is greater than or equal to 4 (1)
=GESTEP(5, 5) Checks whether 5 is greater than or equal to 5 (1)
=GESTEP(-4, -5) Checks whether -4 is greater than or equal to -5 (1)
=GESTEP(-1, 0) Checks whether -1 is greater than or equal to 0 (0)

birol_mumcu · 30 Kasım 2006

17.HEX2BIN
Converts a hexadecimal number to binary.

Syntax

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).

Remarks

If number is negative, HEX2BIN ignores places and returns a 10-character binary number.
If number is negative, it cannot be less than FFFFFFFE00, and if number is positive, it cannot be greater than 1FF.
If number is not a valid hexadecimal number, HEX2BIN returns the #NUM! error value.
If HEX2BIN requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, HEX2BIN returns the #VALUE! error value.
If places is negative, HEX2BIN returns the #NUM! error value.
Example

=HEX2BIN("F", 8) Converts hexadecimal F to binary, with 8 characters (00001111)
=HEX2BIN("B7") Converts hexadecimal B7 to binary (10110111)
=HEX2BIN("FFFFFFFFFF") Converts hexadecimal FFFFFFFFFF to binary (1111111111)

birol_mumcu · 30 Kasım 2006

18.HEX2DEC
Converts a hexadecimal number to decimal.

Syntax

HEX2DEC(number)

Number is the hexadecimal number you want to convert. Number cannot contain more than 10 characters (40 bits). The most significant bit of number is the sign bit. The remaining 39 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Remark

If number is not a valid hexadecimal number, HEX2DEC returns the #NUM! error value.

Example

=HEX2DEC("A5") Converts hexadecimal A5 to decimal (165)
=HEX2DEC("FFFFFFFF5B") Converts hexadecimal FFFFFFFF5B to decimal (-165)
=HEX2DEC("3DA408B9") Converts hexadecimal 3DA408B9 to decimal (1034160313)

birol_mumcu · 30 Kasım 2006

19.HEX2OCT
Converts a hexadecimal number to octal.

Syntax

HEX2OCT(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. The remaining 39 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, HEX2OCT uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number is negative, HEX2OCT ignores places and returns a 10-character octal number.
If number is negative, it cannot be less than FFE0000000, and if number is positive, it cannot be greater than 1FFFFFFF.
If number is not a valid hexadecimal number, HEX2OCT returns the #NUM! error value.
If HEX2OCT requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, HEX2OCT returns the #VALUE! error value.
If places is negative, HEX2OCT returns the #NUM! error value.
Example

=HEX2OCT("F", 3) Converts hexadecimal F to octal with 3 characters (017)
=HEX2OCT("3B4E") Converts hexadecimal 3B4E to octal (35516)
=HEX2OCT("FFFFFFFF00") Converts hexadecimal FFFFFFFF00 to octal (7777777400)

birol_mumcu · 30 Kasım 2006

20.IMABS
Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format.

Syntax

IMABS(inumber)

Inumber is a complex number for which you want the absolute value.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The absolute value of a complex number is:

z = x + yi
Example

=IMABS("5+12i") Absolute value of 5+12i (13)

birol_mumcu · 30 Kasım 2006

21.IMAGINARY
Returns the imaginary coefficient of a complex number in x + yi or x + yj text format.

Syntax

IMAGINARY(inumber)

Inumber is a complex number for which you want the imaginary coefficient.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
Example

=IMAGINARY("3+4i") Imaginary coefficient of the complex number 3+4i (4)
=IMAGINARY("0-j") Imaginary coefficient of the complex number 0-j (-1)
=IMAGINARY(4) Imaginary coefficient 4 (0)

birol_mumcu · 30 Kasım 2006

22.IMARGUMENT
Returns the argument (theta), an angle expressed in radians, such that:

Syntax

IMARGUMENT(inumber)

Inumber is a complex number for which you want the argument .

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
IMARGUMENT is calculated as follows:

z = x + yi
Example

=IMARGUMENT("3+4i") Theta argument of 3+4i, in radians (0.927295)

birol_mumcu · 30 Kasım 2006

23.IMCONJUGATE
Returns the complex conjugate of a complex number in x + yi or x + yj text format.

Syntax

IMCONJUGATE(inumber)

Inumber is a complex number for which you want the conjugate.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The conjugate of a complex number is:

Example

=IMCONJUGATE("3+4i") Complex conjugate of 3+4i (3 - 4i)

birol_mumcu · 30 Kasım 2006

24.IMCOS
Returns the cosine of a complex number in x + yi or x + yj text format.

Syntax

IMCOS(inumber)

Inumber is a complex number for which you want the cosine.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
If inumber is a logical value, IMCOS returns the #VALUE! error value.
The cosine of a complex number is:

Example

=IMCOS("1+i") Cosine of 1+i (0.83373 - 0.988898i)