Function | Description | Input Value | Result | ||||||||||||
SUMPRODUCT | Multiplies the arrays together and returns the sum of the products. | =SUMPRODUCT(A1:A3,B1:B3) | Sum product = 56 (10+18+28) 2*5 =10 3*6 = 18 4*7 = 28 | ||||||||||||
SUMPRODUCT and IF | Multiplies the arrays together and returns the sum of products if the specified condition is satisfied. | =SUMPRODUCT(if(B2:B4>=10,1,0),B2:B4,C2:C4) | Sample table
Result: 135 |
| Function | Description | Input Value | Result |
| AVERAGE | Calculates the average of the arguments | =AVERAGE(385, 455, 675, 225) | 435 |
| COUNT | Counts the number of items given as arguments | =COUNT(385, 455, 675, 225) | 4 |
| MAX | Maximum value in the given range | =MAX(385, 455, 675, 225) | 675 |
| MIN | Minimum value in the given range | =MIN(385, 455, 675, 225) | 225 |
| SUM | Sum of all the numbers in the range | =SUM(140,456) | 596 |
| Function | Description | Input Value | Result | |||||||||||||||
| AND | Returns 1 if all arguments are TRUE and 0 if FALSE | =AND(SUM(456,140) > 600) =AND(SUM(456,140)<600) | 1 0 | |||||||||||||||
| IF | Returns value with respect to a specific condition. | =IF((B2>50),20,0) | If B2's value is greater than 50, then 20 will be returned. If B2's value is lesser than 50, then 0 will be returned. | |||||||||||||||
| NOT | Evaluates to 0 if argument is TRUE and evaluates to 1 if argument is FALSE | =NOT(SUM(456,140) > 600)=NOT(SUM(456,140) < 600) | 1 0 | |||||||||||||||
| OR | Evaluates to 1 if at least one of the arguments is TRUE and 0 if both arguments are FALSE | =OR(10>1, 1>10) =OR(10<1, 11<10) | 1 0 | |||||||||||||||
| SUMIF | Returns a sum of all the numbers in a range based on a condition. | =SUMIF(B2:B5, 5, C2:C5) | Sample table
Output: 9,000 |
Function | Description | Input Value | Result |
DATEDIF | Calculate the difference between two dates in days, months, or years. | =DATEDIF(start_date, end_date, unit) |
Function | Description | Input Value | Result |
NPV | Calculate the present value of a series of cash flows over a period of time, considering a discount rate | =NPV(discount_rate, cashflow1, [cashflow2, ...]) | |
PV | Calculate the present value of a future amount, considering an interest rate and number of periods | =PV(rate, number_of_periods, payment_amount, future_value, end_or_beginning) | |
FV | Calculate the future value of an investment based on a series of periodic payments, considering an interest rate and number of periods | =FV(rate, number_of_periods, payment_amount, present_value, end_or_beginning) |
| Function | Description | Input Value | Result |
| ABS | Calculates the absolute value of the expression given in parentheses | =ABS(456-596) | 140 |
| INT | Rounds the argument down to the nearest integer | =INT(10.69) | 10 |
| MOD | Returns the remainder of the first argument divided by the second | =MOD(5, 2) | 1 |
| POWER | Returns a number raised to a power | =POWER(2,6) | 64 |
| PRODUCT | Returns the product of the numbers in the given range | =PRODUCT(78,89) | 6942 |
| ROUND | Rounds the argument to the nearest integer | =ROUND(10.69) | 11 |
| SIGN | Returns -1 if the argument evaluates to a number less than 0, 0 if the argument evaluates to zero, 1 if the argument evaluates to a positive number | =SIGN(456-596) =SIGN(656-656) =SIGN(596-456 | -1 0 1 |
| SQRT | Returns the square root of a positive number | =SQRT(25) | 5 |
Formula | Description | Syntax | Input Value | Result |
STRING | Converts a value to text format. | =STRING(value) | =STRING(42) | 42 |
CONCAT | Combines multiple text values into a single string. | =CONCAT(text1, text2, ...) | =CONCAT("Hello", " ", "World") | Hello World |
LEN | Returns the number of characters in a text string. | =LEN(text) | =LEN("Hello") | 5 |
UPPER | Converts text to uppercase. | =UPPER(text) | =UPPER("hello") | HELLO |
LOWER | Converts text to lowercase. | =LOWER(text) | =LOWER("HELLO") | hello |
LEFT | Returns the specified number of characters from the beginning of a string. | =LEFT(text, num_chars) | =LEFT("Hello", 3) | Hel |
RIGHT | Returns the specified number of characters from the end of a string. | =RIGHT(text, num_chars) | =RIGHT("Hello", 3) | llo |
MID | Returns characters from the middle of a string, starting at a given position. | =MID(text, start_num, num_chars) | =MID("Hello World", 7, 5) | World |
TRIM | Removes leading and trailing spaces from text. | =TRIM(text) | =TRIM(" hi ") | hi |
FIND | Returns the starting position of one text string within another. | =FIND(find_text, within_text) | =FIND("lo", "Hello") | 4 |
SUBSTITUTE | Replaces occurrences of text within a string. | =SUBSTITUTE(text, old_text, new_text) | =SUBSTITUTE("aabbcc", "bb", "XX") | aaXXcc |
PARSENUM | Parses a locale-formatting string (with currency symbols, thousand separators, etc.) into a number | =PARSENUM(value [, decimal Sep [, thousandSep]]) | =PARSENUM("$1,234.56") | 1234.56 |