Understanding formulae supported in Zoho Writer
Array formulas
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 |
Aggregate formulas
| 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 |
Conditional logic formulas
| 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 |
Date formulas
Function | Description | Input Value | Result |
DATEDIF | Calculate the difference between two dates in days, months, or years. | =DATEDIF(start_date, end_date, unit) |
Financial formulas
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) |
Math formulas
| 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 |
String formulas
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 |
Related Articles
Access your files securely from anywhere
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
All-in-one knowledge management and training platform for your employees and customers.
Create. Review. Publish.
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
Zoho Sheet Resources
Zoho Forms Resources
Zoho Sign Resources
Sign, Paperless!
Zoho TeamInbox Resources
Design. Discuss. Deliver.
Related Articles
Configure workflow in Zoho Books with Writer's mail merge
Overview Use Zoho Writer with Zoho Books to create branded PDFs and automate document generation from your Books data. You can either use the Zoho Writer Templates extension in Zoho Books for one‑click document generation, or configure ...Configure workflow in Zoho Sprints with Writer's mail merge
The integration between Zoho Sprints and Zoho Writer simplifies the process of document generation, making it more efficient for teams managing projects. By utilizing template functions, you can create dynamic document templates that pull in data ...FAQ's on Zoho Writer's ChatGPT extension
This extension allows you to seamlessly integrate ChatGPT with Zoho Writer to chat with the bot and use its responses in your content. This guide will take you through the steps needed to: Install the extension Interact with the ChatGPT bot from ...AI in Zoho Writer
Zia, Zoho Writer's AI-driven writing assistant, has built-in functionalities to design different types of documents, enhance your content quality, and protect the integrity of your document's content. Here's a quick overview of AI functionalities in ...How to fill default values in fillable fields in Zoho Writer before sharing them?
Overview Zoho Writer’s fillable templates allow you to create forms pre-filled with data from various sources, saving recipients time and effort by having much of the form already completed. Prerequisites A Fillable Template created Access to the ...