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.



    Access your files securely from anywhere

        Zoho Developer Community




                                  Zoho Desk Resources

                                  • Desk Community Learning Series


                                  • Digest


                                  • Functions


                                  • Meetups


                                  • Kbase


                                  • Resources


                                  • Glossary


                                  • Desk Marketplace


                                  • MVP Corner


                                  • Word of the Day



                                      Zoho Marketing Automation
                                              • Sticky Posts

                                              • 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 #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 #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 #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
                                              • Tip #15: Stop guessing and start estimating with Zoho Sheet's Goal Seek

                                                Goal Seek is an ideal spreadsheet tool for devising estimates, as it helps you retrieve your desired output value when the input values are not known. In other words, Goal Seek does back-solving to find input values once you've provided an output value.


                                              Manage your brands on social media



                                                    Zoho TeamInbox Resources

                                                      Zoho DataPrep Resources



                                                        Zoho CRM Plus Resources

                                                          Zoho Books Resources


                                                            Zoho Subscriptions Resources

                                                              Zoho Projects Resources


                                                                Zoho Sprints Resources


                                                                  Qntrl Resources


                                                                    Zoho Creator Resources



                                                                        Zoho Campaigns Resources


                                                                          Zoho CRM Resources

                                                                          • CRM Community Learning Series

                                                                            CRM Community Learning Series


                                                                          • Kaizen

                                                                            Kaizen

                                                                          • Functions

                                                                            Functions

                                                                          • Meetups

                                                                            Meetups

                                                                          • Kbase

                                                                            Kbase

                                                                          • Resources

                                                                            Resources

                                                                          • Digest

                                                                            Digest

                                                                          • CRM Marketplace

                                                                            CRM Marketplace

                                                                          • MVP Corner

                                                                            MVP Corner





                                                                              Design. Discuss. Deliver.

                                                                              Create visually engaging stories with Zoho Show.

                                                                              Get Started Now