Tip #4: Five important statistical functions, and when to use them.

Tip #4: Five important statistical functions, and when to use them.


=AVERAGE (number1; number2; ... number30)


The sample mean is probably the easiest piece of information one can extract from a data set. To calculate sample mean in Zoho Sheet, all you have to do is key-in =AVERAGE and select the range of cells you'd like to find the statistical mean for. 

Zoho Sheet supports a few handy variations of the average function as well.

=TRIMMEAN (number_list, fraction)


Say, for instance, you want the sample mean. However, you want to exclude the maximum and minimum values of the sample to make your mean free of outliers. This can be achieved using =TRIMMEAN. 


=STDEV (number1; number2; ... number30)


Standard deviation is one of the most common, yet an important statistical tool. Standard deviation shows how spread out the data points are from the mean. While =STDEV gives you the deviation in the sample, to calculate the standard deviation of a population, you can use =STDEVP. 


=CORREL (x; y)


Correlation is a statistical tool used to decide if variable X is affected by variable Y and by what degree. The correlation coefficient also tells us if the relationship is positive or negative. If the coefficient is positive, this means both the variables are related, and are positively related. Meaning, an increase in the independent variable will be met with an increase in the dependent variable as well. A negative coefficient means that the variables are related, but negatively. So, any increase in the independent variable will be met with a decrease in the dependent variable.

For instance, consider two data sets, X and Y. While X consists of the marks scored by a set of students, Y marks the students' attendance. 



The positive correlation represents that with a higher attendance, students tend to perform well. 

=VAR (number1; number2; ... number30)


Just like standard deviation and correlation, variance is one of the most basic tests done to understand data better. Variance is simply the square of standard deviation, and is measured in square units. It gives you an estimate of how far the sample spreads from its actual mean. While =VAR provides you the variance of the given sample, =VARP provides you the variance of the given population.

=FORECAST (new_xvalue; yvalues; xvalues)


Given a set of dependent (X) and independent (Y) variables and a few new X variables, the =FORECAST function in Zoho Sheet will help predict the corresponding Y values. 

=TREND (yvalues; xvalues; new_xvalues; type)


A variation of the =FORECAST function is the =TREND function. TREND does exactly what FORECAST does, but with a Y intercept if you wish to furnish. 

For instance, consider the relation between the workforce and the cost incurred. FORECAST will help you predict your cost based on your future workforce, if you do not incur cost without any workforce. If you do incur some cost even without any force, this fixed cost becomes your Y intercept, and TREND can help you with these predictions.

So, in seconds, we arrive at the mean, standard deviation, variation, and the correlation coefficient of data sets. We were able to achieve all of this by using formulas. There's no need to enable special tools, no codes, no errors. Nothing but results! 

Use these simple, but effective functions to understand your data better! For more such Tips and Tricks, follow our Zoho Sheet community. Learn more about all the other functions offered by Zoho Sheet here.



    • Sticky Posts

    • Tip #20 - Three things you probably didn't know you can do with picklists

      Hello Zoho Sheet users! We’re back with another quick tip to help you make your spreadsheets smarter. Picklists are a great tool to maintain consistency in your spreadsheet. Manually entering data is time-consuming and often leaves typos and irregular

    • Tip #18: 6 Trendlines and when to use them in your spreadsheet data?

      Charts are a great tool for visualizing and interpreting large chunks of data in spreadsheets. Zoho Sheet offers you 35+ chart options, along with AI-powered chart recommendations based on the data set you select. There are various chart elements that

    • Tip #17: Easily share spreadsheets with non-Zoho account users

      With efficient, collaboration-friendly options, Zoho Sheet is a popular choice among users. Sheet's external share links help you collaborate more effectively by sharing your spreadsheets with anyone outside your organization, including users who do not

    • Tip #16: Ask Zia your data-related questions and get powerful insights

      Zoho Sheet provides a variety of tools and functionalities to help you analyze your data from end to end. But what if you could analyze all your data sets with a single AI-powered tool? Meet Zia, our smart virtual assistant who will answer all your data-related

    • Tip #7: Four things you didn't know you can do with Conditional Formatting

      Conditional formatting helps you highlight and visualize data in your spreadsheet based on provided rules. Zoho Sheet offers three types of conditional formatting: Classic, Color Scales, and Icon Sets. If you're already familiar with how Conditional Formatting works, here are some bonus tips for Classic formats: Customize rules with formulas Zoho Sheet allows you to create personalized rules based on formulas. For example, in a task list you can use customized formulas to highlight tasks which are

    • Recent Topics

    • Shift-Centric View for Assigning and Managing Shifts in Zoho People

      Hello Zoho People Product Team, Greetings and hope you are doing well. This feature request is related to Zoho People - please don't move it to zoho one! We would like to submit a feature request regarding the shift assignment and management view in Zoho

    • CRM function REST API response format

      Is there a way to control the JSON response returned by the CRM function REST API? If I call a function using either OAuth or an API key it returns a 200 OK response with a string in the format shown below. I am using a particular feature of an external

    • Add Employee Availability Functionality to Zoho People Shift Module

      Hello Zoho People Product Team, Greetings and hope you are doing well. We would like to submit a feature request to enhance the Zoho People Shift module by adding employee availability management, similar to the functionality available in Zoho Shifts.

    • Using MPN across multiple SKUs and inventory tracking

      I have several different SKU's that share a common MPN and would like to track inventory by MPN. SKU1 has MPN1 assigned SKU2 has MPN1 assigned Here is an example If I start with 5 of MPN 1 in stock I want each SKU1 and SKU2 to show as 5 in stock, If I

    • Unable to Access Application:

      Whenever I try to access my application from the desktop, say I am editing it and want to test something in the desktop environment I get: An error has occurred. An internal error has occurred. Please check the URL , or try refreshing the page I can edit

    • PDF Annotation is here - Mark Up PDFs Your Way!

      Reviewing PDFs just got a whole lot easier. You can now annotate PDFs directly in Zoho Notebook. Highlight important sections, add text, insert images, apply watermarks, and mark up documents in detail without leaving your notes. No app switching. No

    • CRM gets location smart with the all new Map View: visualize records, locate records within any radius, and more

      Hello all, We've introduced a new way to work with location data in Zoho CRM: the Map View. Instead of scrolling through endless lists, your records now appear as pins on a map. Built on top of the all-new address field and powered by Mappls (MapMyIndia),

    • Link Contacts to Billed Accounts

      Hello, I want to do a survey on all my customers of 2025. For that I want to select all contacts linked to accounts who where billed in 2025. How to I create this link to I can then use Zoho Survey with this database of contacts?

    • Zoho Creator customer portal limitation | Zoho One

      I'm asking you all for any feedback as to the logic or reasoning behind drastically limiting portal users when Zoho already meters based on number of records. I'm a single-seat, Zoho One Enterprise license holder. If my portal users are going to add records, wouldn't that increase revenue for Zoho as that is how Creator is monetized? Why limit my customer portal to only THREE external users when more users would equate to more records being entered into the database?!? (See help ticket reply below.)

    • Filter in fields from Jira extension

      We have installed the Jira extension so we can maken Jira issues from Zoho desk. In Zoho desk I can also see the Jira issue status for example but I can not filter on this field. I would like to setup an filter showing me the closed Jira issues. How can

    • Cannot see Application from Lookup field

      Hi all, I am trying to access data for an application on our account via a lookup field; however, the application doesn't appear in the dropdown at all. Can anyone shed any light on this, please? I have asked Zoho support; however, they're just as confused,

    • Cannot see correct DNS config for mail after moving domain to another provider

      I have moved my domain from one provider to another and after that zoho mail stopped working (expected). Problem is, zoho mail admin panel still shows (10 hours after move) that all records are correct while I haven't changed anything in my domain DNS

    • Zoho CRM Meetings Module Issues

      We have a use-case that is very common in today's world, but won't work in Zoho CRM. We have an SDR (Sales Development Rep) who makes many calls per day to Leads and Contacts, and schedules meetings for our primary Sales Reps. He does this by logging

    • Zoho Books integration sync from Zoho CRM does not work

      Hi Zoho Community & Zoho Support We just tried to get a sync some products into Zoho Books from CRM using the native sync and we're getting an error: "It looks like some mandatory fields you're trying to map are empty. Please provide valid field names

    • P & L Sub-categorized accounts

      How can I show sub-categorized Income and Expense accounts on the P & L report?

    • Report showing Bill Details with Project and Sales Invoice Number

      Hi There, I am hoping that someone can help, I am looking for report that can show the bill and expense details along with project its as assigned to and the invoice number that the sales has been raised in. The goal is I can filter a customer/project

    • Advanced Payment for Inventory Items with serial numbers

      Hello, We sell equipment that we track the unique serial numbers on using Sales Orders. We can charge the customers an advanced payment, then the balance on delivery. We cannot figure out a way to do this in Books/Inventory: - Cannot part invoice a SO

    • Is it possible to restrict ZCRM user to see only custom views created by administrator

      I have segmented data in my CRM and I want to allow different users to be able to see only parts of it based on some criteria. I've tried to create and share a custom view, but then there is always an option for user to see all open lead for example.

    • Issues Logging into ZOHO

      Hello, one of my coworkers is having issues logging into ZOHO, she has requested a code when entering and the email is correct but she has not received the code. can you help us with this?

    • Google Fonts Integration in Pagesense Popup Editor

      Hello Zoho Pagesense Team, We hope you're doing well. We’d like to submit a feature request to enhance Zoho Pagesense’s popup editor with Google Fonts support. Current Limitation: Currently, Pagesense offers a limited set of default fonts. Google Fonts

    • Add Popup Rejection Metrics to Reports

      Hello Zoho PageSense Team, We would like to request improved reporting for popup interactions. Current Limitation: PageSense currently provides conversion data, but there is no clear visibility into: Popup rejections Popup closes (✕ button clicks) Dismissals

    • Ability to Reset / Reinitialize Popup Cookies

      Hello Zoho PageSense Team, We would like to request the ability to manually reset popup cookies. Current Limitation: At the moment, it is not possible to initiate a new popup cookie from the our side. Visitors who rejected or closed a popup will not see

    • Control Popup Cookie Expiration Duration

      Hello Zoho PageSense Team, We would like to request an enhancement related to popup cookie management. Current Limitation: Currently, PageSense popup cookies remain active for 365 days, and this duration cannot be modified by us. If a visitor closes or

    • Clone / Export Popup Design Across PageSense Projects

      Hello Zoho PageSense Team, We hope you’re doing well. We would like to request an enhancement that allows popup designs to be reused across different PageSense projects. Problem Statement: Currently, Zoho PageSense allows popups to be duplicated only

    • Are there settings for hyperlinks?

      Clicking a hyperlinked cell in Sheet creates this little pop-up with the actual hyperlink inside. Is it possible to have a 1-click link where if you click the cell it opens the link directly with no pop-up?

    • Automatically include all ticket attachments in the ticket resolution email

      Hello Zoho Community, We are implementing Zoho Desk in a real customer-facing production environment and have run into a limitation that is becoming a blocking requirement for our clients. The problem When a ticket is closed or resolved, Zoho Desk sends

    • adding several team members to an Opportunity

      How can we add several team members to one opportunity for collaboration? I have researched and only found something called Deal Team which I cannot find in my CRM to configure.

    • Finding text within a ticket: Expand All or Search this Ticket

      The auto-collapse feature within a ticket is nice for screen scrolling, however it makes it difficult to find text within the ticket if the email is collapsed. In fact you cannot find text if it is collapsed. I would like to propose a feature that allows

    • Books & Desk. Client mapping

      Hi, I’ve been using Zoho Books for several years and am now looking to improve my customer service. I'm experimenting with Zoho Desk and want to sync and map my client data from Zoho Books. However, it seems that mapping requires both contacts to have

    • Bulk update Profile Permissions

      Dears, What should we do if we add new forms or reports and need to update more than 20 permissions? Updating them one by one feels pretty harsh, doesn’t it?

    • String handling

      If I cut a currency string from a quote and try and paste it into the Deal "Amount", it will fail unless I manually delete any commas. Dollar signs are no problem, but comma's seem to fail. Please correct this Input Validation error.

    • text length in list report mobile/tablet

      Is there a way to make the full text of a text field appear in the list report on mobile and tablet? With custom layouts, the text is always truncated after a certain number of characters.

    • email address somehow still not verified (?!)

      L.S. After creating a new email template in CRM I was about to send a group email to my clients, then Zoho CRM announced that they would change the sender address to some kind of Zoho-e-ddress because my email address "has not been verified". Not only

    • What's new in Zoho Sheet: Simplify data entry and collaboration

      Hello, Zoho Sheet community! Last year, our team was focused on research and development so we could deliver updates that enhance your spreadsheet experience. This year, we’re excited to deliver those enhancements—but we'll be rolling them out incrementally

    • WebDAV / FTP / SFTP protocols for syncing

      I believe the Zoho for Desktop app is built using a proprietary protocol. For the growing number of people using services such as odrive to sync multiple accounts from various providers (Google, Dropbox, Box, OneDrive, etc.) it would be really helpful

    • Consolidated report for multi-organisation

      I'm hoping to see this feature to be available but couldn't locate in anywhere in the trial version. Is this supported? The main aim to go to ERP is to have visibility of the multi-organisation in once place. I'm hopeful for this.

    • Feature Request - Allow Customers To Pick Meeting Duration

      Hi Bookings Team, It would be great if there was an option to allow customers to pick a duration based on a max and minimum amount of time defined by me and in increments defined by me. For example, I have some slots which are available for customers

    • Error Logs / Failure logs for Client Scripts Functions

      Hi Team, While we are implementing client scripts for the automation, it is working fine in few accounts but not working for others. So, it would be great if we can have error Logs for client scripts also just like custom functions. Is there any way that

    • Welcome to the Zoho ERP Community Forum

      Hello everyone, We are thrilled to launch Zoho ERP (India edition), a software to manage your business operations from end to end. We’ve created this community forum as a space for you to ask questions, comment answers, provide feedback, and share your

    • Support for Custom Fonts in Zoho Recruit Career Site and Candidate Portal

      Dear Zoho Recruit Team, I hope you're doing well. We would like to request the ability to use custom fonts in the Zoho Recruit Career Site and Candidate Portal. Currently only the default fonts (Roboto, Lato, and Montserrat) are available. While these

    • Next Page