Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
Int ((6 - 1 + 1) * Rnd + 1) Result: random number between 1 and 6 Int ((200 - 150 + 1) * Rnd + 150) Result: random number between 150 and 200 Int ((999 - 100 + 1) * Rnd + 100) Result: random number between 100 and 999For example:
Dim LRandomNumber As Integer LRandomNumber = Int ((300 - 200 + 1) * Rnd + 200)
RANDBETWEEN( bottom, top )
=RANDBETWEEN(A1,A2) Result: random number (between 1 and 5) =RANDBETWEEN(1,5) Result: random number (between 1 and 5) =RANDBETWEEN(B1,B2) Result: random number (between 10 and 20) =RANDBETWEEN(100,200) Result: random number (between 100 and 200) =RANDBETWEEN(200,100) Result: #NUM! (because bottom is greater than top)
RAND( )
=RAND()*(b-a)+aWhere a is the smallest number and b is the largest number that you want to generate a random number for. Please note, that this formula will never generate a number at the highest end of the range.
=RAND()*(25-10)+10The formula above would generate a random number between 10 and 24.9999999.
=RAND()*(A2-A1)+A1 Result: random number (between 50 and 149.9999999) =RAND()*(A3-A1)+A1 Result: random number (between 50 and 199.9999999) =RAND()*(200-100)+100 Result: random number (between 100 and 199.9999999) =INT(RAND()*(200-100)+100) Result: random number (between 100 and 199, integer value)
RADIANS( degrees )
=RADIANS(A1) Result: 2.094395102 =RADIANS(A2) Result: 1.570796327 =RADIANS(A3) Result: 0.09424778 =RADIANS(54.6) Result: 0.952949772
PRODUCT( number1, number2, ... number_n )
=PRODUCT(A1, A2) Result: 12 =PRODUCT(A1, A2, A3) Result: 54 =PRODUCT(A1, A2, A3, A4) Result: -366.12 =PRODUCT(A1, A2, A3, A4, -2) Result: 732.24
POWER( number, power )
=POWER(A1, A2) Result: 81 =POWER(A1, A3) Result: 140.2961154 =POWER(A2, 2) Result: 16
PI( )
=PI() Result: 3.141592654 =PI() * A1 Result: 59.69026042 =PI() / A2 Result: 0.184799568 =PI() + A3 Result: -9.858407346
ODD( number )
=ODD(A1) Result: 211 =ODD(A2) Result: 3 =ODD(A3) Result: -3 =ODD(7) Result: 7 =ODD(8) Result: 9
N( value )
=N(A1) Result: 35 =N(A2) Result: 0 =N(A3) Result: 1 =N(A4) Result: 0 =N(A5) Result: #DIV/0! =N(107) Result: 107 =N(DATE(2012,8,16)) Result: 41137
MOD( number, divisor )
=MOD(A1, A2) Result: 2 =MOD(A1, A3) Result: -7 =MOD(34, A2) Result: 0 =MOD(34, 0) Result: #DIV/0!
MMULT( array1, array2 )
=MMULT(A1:B2, A5:B6)
Result: 6.23
=MMULT(A1:C3, A5:C7)
Result: #VALUE!
=MMULT({1,2;3,4},{5,6;7,8})
Result: 19
MINVERSE( array )
=MINVERSE(A1:B2)
Result: 5.966850829
=MINVERSE(A1:C3)
Result: #VALUE!
=MINVERSE({1,2;3,4})
Result: -2
=MINVERSE({1,2,1;3,4,2;1,1,2})
Result: -2
MINA( value1, value2, ... value_n )
=MINA(A2, A3) Result: 0.23 =MINA(A2:A4) Result: 0.23 =MINA(A2:A6) Result: 0 (as TRUE evaluates to 1 and FALSE evaluates to 0) =MINA(A2:A6, -35) Result: -35 =MINA(A2, A3, -1.6) Result: -1.6
MIN( number1, number2, ... number_n )
=MIN(A2, A3) Result: 7.2 =MIN(A3, A5, -3) Result: -3 =MIN(A2:A6) Result: 5.4 =MIN(A2:A6, 3.6) Result: 3.6
MEDIAN( number1, [number2, ... number_n] )
=MEDIAN(A2, A3) Result: 8.85 =MEDIAN(A3, A5, 45) Result: 7.2 =MEDIAN(A2:A6) Result: 8.1 =MEDIAN(1, 3, 13, 14, 15) Result: 13
MDETERM( array )
=MDETERM(A1:B2)
Result: 1.81
=MDETERM(A1:C3)
Result: #VALUE!
=MDETERM({1,2;3,4})
Result: -2
=MDETERM({1,2,1;3,4,2;1,1,2})
Result: -3
MAXA( value1, [value2, ... value_n] )
=MAXA(A2, A3) Result: 0.5 =MAXA(A2:A4) Result: 0.891 =MAXA(A2:A6) Result: 1 (as TRUE evaluates to 1 and FALSE evaluates to 0) =MAXA(A2, A3, 1.6) Result: 1.6
MAX( number1, [number2, ... number_n] )
=MAX(A2, A3) Result: 10.5 =MAX(A3, A5, 45) Result: 45 =MAX(A2:A6) Result: 200 =MAX(A2:A6, 325) Result: 325
LOG10( number )
=LOG10(A1) Result: 1.301029996 =LOG10(A2) Result: 1.397940009 =LOG10(A3) Result: 2 =LOG10(200) Result: 2.301029996
LOG( number, [base] )
=LOG(A1) Result: 1.301029996 =LOG(A1, 10) Result: 1.301029996 =LOG(A1, A2) Result: 0.930676558 =LOG(A2, 5.1) Result: 1.975690972 =LOG(200, 3) Result: 4.822736302
LN( number )
=LN(A1) Result: 2.995732274 =LN(A2) Result: 3.218875825 =LN(A3) Result: 4.605170186 =LN(200) Result: 5.298317367
y = mx + b (for a single range of x values) y = m1x1 + m2x2 + ... + b (for multiple ranges of x values)
LINEST( y_values, [x_values], [constant], [additional_statistics] )
=LINEST(A2:A6,B2:B6,TRUE,FALSE)The LINEST functions are written as array formulas. When creating your array formula, you need to do the following:
{=LINEST(A2:A6,B2:B6,TRUE,FALSE)}
You can see how the formula look in the images below.
=LINEST({2,3,5,7,10},{4,6,10,14,20},TRUE,FALSE)
The LINEST functions are written as array formulas. When creating your array formula, you need to do the following:{=LINEST({2,3,5,7,10},{4,6,10,14,20},TRUE,FALSE)}
LARGE( array, nth_position )
=LARGE(A1:A5, 1)
Result: 32
=LARGE(A1:A5, 2)
Result: 8
=LARGE(A1:A5, 3)
Result: 5.7
=LARGE(A1:A5, 4)
Result: 4
=LARGE(A1:A5, 5)
Result: -2.3
=LARGE({6, 23, 5, 2.3}, 2)
Result: 6
INT( expression )
=INT(A1) Result: 210 =INT(A2) Result: 2 =INT(A3) Result: -3 =INT(-4.5) Result: -5
FREQUENCY( data, intervals )
=FREQUENCY(B2:B10,D2) Result: 2 =FREQUENCY(B2:B10,D3) Result: 3 =FREQUENCY(B2:B10,D4) Result: 5 =FREQUENCY(B2:B10,D5) Result: 7 =FREQUENCY(B2:B10,89) Result: 7 (same as previous)
Format ( expression, [ format ] )
| Format | Explanation |
|---|---|
| General Number | Displays a number without thousand separators. |
| Currency | Displays thousand separators as well as two decimal places. |
| Fixed | Displays at least one digit to the left of the decimal place and two digits to the right of the decimal place. |
| Standard | Displays the thousand separators, at least one digit to the left of the decimal place, and two digits to the right of the decimal place. |
| Percent | Displays a percent value - that is, a number multiplied by 100 with a percent sign. Displays two digits to the right of the decimal place. |
| Scientific | Scientific notation. |
| Yes/No | Displays No if the number is 0. Displays Yes if the number is not 0. |
| True/False | Displays True if the number is 0. Displays False if the number is not 0. |
| On/Off | Displays Off if the number is 0. Displays On is the number is not 0. |
Format(210.6, "#,##0.00") Result: '210.60' Format(210.6, "Standard") Result: '210.60' Format(0.981, "Percent") Result: '98.10%' Format(1267.5, "Currency") Result: '$1,267.50'For example:
Dim LValue As String LValue = Format(0.981, "Percent")
FLOOR( number, significance )
=FLOOR(A1, 1) Result: 169 =FLOOR(A1, 0.05) Result: 169.65 =FLOOR(A2, 0.05) Result: 169.6 =FLOOR(A3, 2) Result: 2 =FLOOR(A4, 2) Result: #NUM! =FLOOR(-4.5, -1) Result: -4
Fix( expression )
Fix(210.67) Result: 210 Fix(2.98) Result: 2 Fix(-2.98) Result: -2For example:
Dim LNumber As Double LNumber = Fix(210.67)
FACT( number )
=FACT(A1) Result: 6 =FACT(A2) Result: 6 =FACT(A3) Result: 1 =FACT(A4) Result: #NUM! =FACT(4) Result: 24
EXP( number )
=EXP(A1) Result: 20.08553692 =EXP(A2) Result: 22.19795128 =EXP(A3) Result: 0.049787068 =EXP(-4.5) Result: 0.011108997
EVEN( number )
=EVEN(A1) Result: 212 =EVEN(A2) Result: 4 =EVEN(A3) Result: -4 =EVEN(6121) Result: 6122
DEGREES( radians )
=DEGREES(A1) Result: 11.4591559 =DEGREES(A2) Result: 20.05352283 =DEGREES(A3) Result: -180.4817055 =DEGREES(2.11) Result: 120.8940948 =DEGREES(Pi()) Result: 180
COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )
=COUNTIFS(A2:A9,"=2013") Result: 4 (applies 1 criteria) =COUNTIFS(A2:A9,"=2013",B2:B9,"=Oranges") Result: 2 (applies 2 criteria) =COUNTIFS(A2:A9,">=2009",B2:B9,"=Oranges", A2:A9,"<=2012") Result: 1 (applies 3 critiera) =COUNTIFS(A2:A9,">=2009",B2:B9,"=B*") Result: 2 (Uses the * wildcard to match on all products that start with B) =COUNTIFS(A2:A9,">=2009",B2:B9,"=B?nanas") Result: 2 (Uses the ? wildcard to match on a single character, ie: Bananas, Benanas, Binanas, Bonanas, and so on)
COUNTIF( range, criteria )
=COUNTIF(A2:A7, D2) Result: 1 =COUNTIF(A:A, D2) Result: 1 =COUNTIF(A2:A7, ">=2001") Result: 4
COUNTBLANK( range )
=COUNTBLANK(A1:A4) Result: 1 =COUNTBLANK(A1:A6) Result: 3 =COUNTBLANK(A1:B4) Result: 3
COUNTA( argument1, [argument2, ... argument_n] )
=COUNTA(A1:A6) Result: 5 =COUNTA(A1:A6, 129) Result: 6 =COUNTA(A1:A6, 129, "techonthenet") Result: 7 =COUNTA(A1:A6, 129, "techonthenet", -2) Result: 8
COUNT( argument1, [argument2, ... argument_n] )
=COUNT(A1:A6) Result: 3 =COUNT(A1:A6, 129) Result: 4 =COUNT(A1:A6, 129, "techonthenet") Result: 4 =COUNT(A1:A6, 129, "techonthenet", -2) Result: 5
COSH( number )
=COSH(A1) Result: 1.020066756 =COSH(A2) Result: 1.061877819 =COSH(A3) Result: 11.68945835 =COSH(2.11) Result: 4.184739626
COS( number )
=COS(A1) Result: 0.980066578 =COS(A2) Result: 0.939372713 =COS(A3) Result: -0.999964658 =COS(200) Result: 0.487187675
Dim LNumber As Double LNumber = Cos(210)
CONVERT( number, from_units, to_units )
| Value | Measurement Unit | Measurement Type |
|---|---|---|
| "m" | Meter | Length/Distance |
| "mi" | Mile | Length/Distance |
| "Nmi" | Nautical mile | Length/Distance |
| "in" | Inch | Length/Distance |
| "ft" | Foot | Length/Distance |
| "yd" | Yard | Length/Distance |
| "ang" | Angstrom | Length/Distance |
| "pica" | Pica | Length/Distance |
| "g" | Gram | Weight/Mass |
| "sg" | Slug | Weight/Mass |
| "lbm" | Pound | Weight/Mass |
| "u" | Atomic unit | Weight/Mass |
| "ozm" | Ounce | Weight/Mass |
| "yr" | Year | Time |
| "day" | Day | Time |
| "hr" | Hour | Time |
| "mn" | Minute | Time |
| "sec" | Second | Time |
| "C" or "cel" | Celsius | Temperature |
| "F" or "fah" | Fahrenheit | Temperature |
| "K" or "kel" | Kelvin | Temperature |
| "tsp" | Teaspoon | Liquid Volume |
| "tbs" | Tablespoon | Liquid Volume |
| "oz" | Fluid ounce | Liquid Volume |
| "cup" | Cup | Liquid Volume |
| "pt" or "us_pt" | US pint | Liquid Volume |
| "uk_pt" | UK pint | Liquid Volume |
| "qt" | Quart | Liquid Volume |
| "gal" | Gallon | Liquid Volume |
| "l" or "lt" | Liter | Liquid Volume |
| "N" | Newton | Force |
| "dyn" or "dy" | Dyne | Force |
| "lbf" | Pound Force | Force |
| "Pa" or "p" | Pascal | Pressure |
| "atm" | Atmosphere | Pressure |
| "mmHg" | mm of Mercury | Pressure |
| "HP" or "h" | Horsepower | Power |
| "W" or "w" | Watt | Power |
| "J" | Joule | Energy |
| "e" | Erg | Energy |
| "c" | Thermodynamic calorie | Energy |
| "cal" | IT calorie | Energy |
| "eV" or "ev" | Electron volt | Energy |
| "HPh" or "hh" | Horsepower-hour | Energy |
| "Wh" or "wh" | Watt-hour | Energy |
| "flb" | Foot-pound | Energy |
| "BTU" or "btu" | BTU | Energy |
| "T" | Tesla | Magnetism |
| "ga" | Gauss | Magnetism |
=CONVERT(A1,"in","ft") Result: 2 =CONVERT(A2,"F","C") Result: -15 =CONVERT(A3,"mn","sec") Result: 120 =CONVERT(1,"mn","sec") Result: 60
COMPLEX( real_coefficient, imaginary_coefficient, [suffix] )
=COMPLEX(A2,B2) Result: 3+5i =COMPLEX(A2,B2,"i") Result: 3+5i =COMPLEX(A2,B2,"j") Result: 3+5j =COMPLEX(A3,B3) Result: 2+i =COMPLEX(A3,B3,"i") Result: 2+i =COMPLEX(A3,B3,"j") Result: 2+j
COMBINA( number, chosen )
=COMBINA(A1, A2) Result: 2002 =COMBINA(A1, A3) Result: 220 =COMBINA(21, 5) Result: 53130
