This chapter lists code examples of performing numerical calculations, including Null value judgment, Random values, Constants of various data types, Signs of numbers, Involution and evolution, Decimal truncation and rounding, Continued multiplication & factorial, Accumulated sum, Greatest common divisor & least common multiple, Permutation & combination, Pi, Trigonometric functions, Logarithmic functions, Compute expressions with different conditions, Use temp variable in expressions and Logic operations.
|
|
A |
|
|
1 |
=null |
|
|
2 |
=0 |
0 |
|
3 |
=if(A1==null,"null","not null") |
null |
|
4 |
=if(!A1,"null","not null") |
null |
|
5 |
=if(A2!=null,"not null","null") |
not null |
|
6 |
=[,1,2,3].ifn() |
1 |
|
7 |
=["",,,0,3].nvl() |
0 |
|
|
A |
|
|
1 |
=rand() |
Get a random value between 0 and 1 |
|
2 |
=rand(1000) |
Get random integer values between 0 and 1,000 inclusive |
|
|
A |
|
|
1 |
time |
String type "time" |
|
2 |
-3415 |
32-bit integer -3415 |
|
3 |
3.1415927 |
Floating point number 3.1415927 |
|
4 |
1101022000L |
64-bit long integer 1101022000 |
|
5 |
12345678901 |
An integer exceeding the value range of a 32-bit integer will be automatically parsed into the 64-bit long integer 12345678901 |
|
6 |
35% |
0.35, which is floating point number represented by a percentage |
|
7 |
0x33 |
The value is 51, a hexadecimal long integer headed by 0x |
|
8 |
'345+6 |
The value is string "345+6"; the sign ' marks a string type constant |
|
|
A |
|
|
1 |
=sign(45) |
Return 1 for a positive number |
|
2 |
=sign(-100.34) |
Return -1 for a negative number |
|
3 |
=sign(0) |
Return 0 for zero |
|
4 |
=abs(-4.6) |
Return the absolute value 4.6 |
|
|
A |
|
|
1 |
=power(2,3) |
Cube |
|
2 |
=power(-2,3) |
Cube |
|
3 |
=power(4,0.5) |
Square root |
|
4 |
=sqrt(8,2) |
Square root |
|
5 |
=power(27,1/3) |
Cube root |
|
6 |
=sqrt(8,3) |
Cube root |
|
|
A |
|
|
1 |
=round(3451251.274,1) |
Round down to 1 decimal place |
|
2 |
=round(3451251.274,2) |
Round down to 2 decimal places |
|
3 |
=ceil(3450001.003,-2) |
Carry the remaining figures to the column of hundreds |
|
4 |
=ceil(3450001.003,2) |
Carry the remaining figures and round off to 2 decimal places |
|
5 |
=floor(3451291.234,-2) |
Round to the column of hundreds and discard all the remaining figures |
|
6 |
=floor(3451281.238,2) |
Round off to 2 decimal places and discard the remaining figure |
|
|
A |
|
|
1 |
=product(2, 3, 5, 7) |
210, the value of calculating 2*3*5*7 |
|
2 |
=product([7, 4, 4]) |
112, the value of continual multiplication of the numbers in the given sequence |
|
3 |
=fact(5) |
120, the value of factorial 5 |
|
4 |
=fact(0) |
1 |
|
|
A |
|
|
1 |
=[1,2,3,4].(cum(~)) |
[1,3,6,10], iterative sum |
|
|
A |
|
|
1 |
=gcd(2000, 875, 325) |
25, gcd of the 3 numbers |
|
2 |
=gcd([1001, 28]) |
7, gcd of the members of the sequence |
|
3 |
=gcd(20005, 1234) |
1; the two numbers are coprime |
|
4 |
=lcm(10, 35, 28) |
140, lcm of the 3 numbers |
|
5 |
=lcm(1001, 111) |
111111, lcm of the members of the sequence， |
|
|
A |
|
|
1 |
=combin(10, 3) |
120, the number of combinations of selecting 3 from 10 elements |
|
2 |
=combin(5, 4) |
5, the number of combinations of selecting 4 from 5 elements |
|
3 |
=permut(10, 3) |
720, the number of permutations of selecting 3 from 10 elements |
|
4 |
=permut(5, 4) |
120, the number of permutations of selecting 4 from 5 elements |
|
|
A |
|
|
1 |
=pi() |
π |
|
2 |
=pi(4) |
4*π |
|
|
A |
|
|
1 |
=inf() |
Positive infinity |
|
2 |
=-inf() |
Negative infinity |
|
|
A |
|
|
1 |
=sin(pi(30/180)) |
Sine |
|
2 |
=cos(pi()/2) |
Cosine |
|
3 |
=tan(pi()/4) |
Tangent |
|
4 |
=asin(0.5) |
Arc sine |
|
5 |
=acos(-0.5) |
Arc cosine |
|
6 |
=atan(1) |
Arc tangent |
|
7 |
=sinh(1) |
Hyperbolic sine |
|
8 |
=cosh(4) |
Hyperbolic cosine |
|
9 |
=tanh(0.5) |
Hyperbolic tangent |
|
10 |
=asinh(10) |
Inverse hyperbolic sine |
|
11 |
=acosh(10) |
Inverse hyperbolic cosine |
|
12 |
=atanh(0.5) |
Inverse hyperbolic tangent |
|
|
A |
|
|
1 |
=lg(10000) |
Logarithm to base 10 |
|
2 |
=lg(8,2) |
Logarithm to base 2 |
|
3 |
=ln(1000) |
Natural logarithm |
|
4 |
=exp(A2) |
e to the nth power |
|
|
A |
|
|
1 |
3000 |
|
|
2 |
=if(A1>10000:1*0.45+450, A1>5000:A1*0.15+150;A1*0.05) |
150.0 |
|
3 |
manager |
|
|
4 |
=case(A3,"president":500,"manager":300,"employee":150) |
300 |
|
|
A |
|
|
1 |
=(a=1,b=a*3,b+4) |
7 |
|
2 |
=a |
1 |
|
3 |
=b |
3 |
|
|
A |
|
|
1 |
=and(6,10) |
2, bitwise AND |
|
2 |
=or(3,5) |
7, bitwise OR |
|
3 |
=not(6) |
-7, bitwise NOT |
|
4 |
=xor(6,11) |
13, bitwise XOR |
|
5 |
=shift(13,2) |
3, Shift right two bit positions |
|
6 |
=shift(13,-2) |
52, Shift left two bit positions |