Zoho Sheet Functions

 

Zoho Sheet is always looking for ways to make your spreadsheet more of a data analysis tool and less of just a database. As part of the process, we’ve added 9 new functions to our 400+ function gallery. These new additions are designed to help you create more exhaustive reports and more intuitive spreadsheets.

1.   Dynamic CURRENCY conversion 

Working globally means you will have to deal with multiple currencies at once. The CURRENCY function will simplify things by converting currencies dynamically, using live exchange rates. You don’t have to look up a conversion rate ever again!

CURRENCY(value, from, to)

2. FILTER with expanded power  

In a huge data set, filtering based on various criteria can help you slice and dice your information better. While the traditional filter options allow you to do that, the FILTER function also lets you pull the filtered list to a new range, independent of the original data. This means you get to analyze these results further without affecting the actual data. You can also nestle FILTER inside other common functions like SUM or COUNT and make them more effective.

FILTER(range, condition, [condition1], [condition2]...)

 3. Combine multiple cells with TEXTJOIN  

Have a bunch of text entries you’d like to put into one cell? Combining different cells using “&”s to prepare your spreadsheet dashboards? Well, TEXTJOIN can help dynamically combine two or more cells or independent text strings. You can also mention the delimiters you want between these strings in the output cell.

TEXTJOIN(delimiter, ignore_blank, text1, text2, [text31], [text32]...)

4&5. MAXIFS and MINIFS: a twist on classic functions 

These handy variations of MAX and MIN functions will help you filter out the maximum or minimum values from a given data range, provided the values satisfy the specified conditions. The function also allows you to filter out the MAX or MIN results from a cell range based on the values in other corresponding cell ranges.

MAXIFS(range, criteria_range, criterion, [criteria_range1], [criterion1], [criteria_range2], [criterion2]...)

6. Cut through repetitive entries with UNIQUE 

Lists can be tricky to track, especially when they’re long. In a mile long list of repetitive entries, the UNIQUE function will pick and deliver all unique entries, making it easier to understand your data better.

UNIQUE(range)

7. Test multiple conditions with XOR 

XOR (exclusive, or) is a function that lets you test multiple conditions at once. It’s sometimes referred to as the “one or the other, but not both” function. It returns TRUE if one of the conditions is satisfied, or it returns FALSE if both conditions are satisfied or neither is satisfied.

XOR(logical, [logical1], [logical2]...)

 

8. Find the difference between dates with DATEDIF 

Though simple, DATEDIF proves to be one of the most helpful functions of all time. It lets you find the difference between two dates while letting you choose how to represent the difference (days, months, or years) or to ignore the years or months altogether.

DATEDIF(start_date;end_date;type)

9.  IFS for when there is more than one criterion  

As an extension of the IF function, IFS helps you evaluate multiple criteria to arrive at a conclusion.

IFS(test, value, [test1], [value1], [test2], [value2]...)

We hope these new functions can help pull better insights out of your data sets and build simple and better spreadsheets. Follow our community for more interesting updates.

This site uses Akismet to reduce spam. Learn how your comment data is processed.