Skip to content
geeksforgeeks
  • Courses
    • DSA to Development
    • Get IBM Certification
    • Newly Launched!
      • Master Django Framework
      • Become AWS Certified
    • For Working Professionals
      • Interview 101: DSA & System Design
      • Data Science Training Program
      • JAVA Backend Development (Live)
      • DevOps Engineering (LIVE)
      • Data Structures & Algorithms in Python
    • For Students
      • Placement Preparation Course
      • Data Science (Live)
      • Data Structure & Algorithm-Self Paced (C++/JAVA)
      • Master Competitive Programming (Live)
      • Full Stack Development with React & Node JS (Live)
    • Full Stack Development
    • Data Science Program
    • All Courses
  • Tutorials
    • Data Structures & Algorithms
    • ML & Data Science
    • Interview Corner
    • Programming Languages
    • Web Development
    • CS Subjects
    • DevOps And Linux
    • School Learning
  • Practice
    • Build your AI Agent
    • GfG 160
    • Problem of the Day
    • Practice Coding Problems
    • GfG SDE Sheet
  • Contests
    • Accenture Hackathon (Ending Soon!)
    • GfG Weekly [Rated Contest]
    • Job-A-Thon Hiring Challenge
    • All Contests and Events
  • Excel Tutorial
  • Excel Formulas
  • Excel Shortcut Keys
  • Data Analysis in Excel
  • Formatting in Excel
  • Excel Workbooks
  • Statistical Functions
  • Data Visualization in Excel
  • Pivot Tables in Excel
  • MS Excel Quiz
  • Excel Interview Questions
  • Advance Excel
Open In App
Next Article:
How to calculate MOVING AVERAGE in a Pandas DataFrame?
Next article icon

How to average a range of data ignoring zero in Excel?

Last Updated : 14 May, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

Excel is a tool for storing, organizing, and managing a large volume of data. It uses rows and columns to store the data. This tool is used for the analysis of data to get insights from it with the use of functions and formulas.

Generally, this is used by the financial professional for accounting purposes but it can be used by anyone as it is very easy to use. Data analysis can be done in various ways such as visual analysis using graphs, mathematical or trend analysis using formulas, etc.

Let's learn how can we calculate the average of numbers excluding zero values.

How to get an average of numbers excluding zero values

Suppose we have the following data values: 10, 20, 0, 0, 10, 20. The table below will show the average of the given data including and excluding zero values:

Dataset
Excel WorkSheet

We can perform the above task using AVERAGEIF() function.

AVERAGEIF() function

This function calculates the average of numbers in a range that meets the criteria as supplied. Function criteria can include logical operators (>,<,<>,=) and wildcards (*,?).

In order to ignore zero values, the criteria supplied is "<>0", which means "not equal to zero".

Syntax:

=AVERAGEIF(range, "<>0")
  • For the purpose of demonstration, we will use the below-given data to calculate the average ignoring zero values.
Values-given
  • Formula applied: AVERAGEIF(range, "<>0")
Average ignoring zero
Averaging a range of data ignoring zero in Excel
  • Output:
Output
Output

Conclusion

In conclusion, learning how to average a range of data while ignoring zeros in Excel is crucial for accurate calculations. This article has shown you how to use the AVERAGEIF function to achieve this effectively.


Next Article
How to calculate MOVING AVERAGE in a Pandas DataFrame?
author
romy421kumari
Improve
Article Tags :
  • Excel
  • News
  • Microsoft Office
  • Excel-functions
  • ExcelGuide

Similar Reads

  • How to Normalize Data in Excel?
    The term "normalization" is a popular buzzword among professionals in fields like Machine Learning, Data Science, and statistics. It refers to the process of scaling down values to fit within a specific range. The term is often misunderstood and is sometimes used interchangeably with "standardisatio
    6 min read
  • How to Calculate an Exponential Moving Average in Python?
    Moving Averages are financial indicators which are used to analyze stock values over a long period of time. i.e. Average value for that long period is calculated. Exponential Moving Averages (EMA) is a type of Moving Averages. It helps users to filter noise and produce a smooth curve. In Moving Aver
    3 min read
  • How to Calculate Weighted Average in Excel
    Understanding how to calculate a weighted average in Excel can be a game-changer for anyone dealing with data analysis, financial modeling, or academic grading. Unlike a simple average, a weighted average considers the relative importance of each data point, providing a more accurate reflection of t
    5 min read
  • How to Compute a Moving Average in PL/SQL?
    A moving average is a technique we use to analyze and determine some specific trends in our provided data. Through moving averages, we can analyze and determine trends in our data along with some other benefits like noise reduction in our data. In this article, we are going to learn about "how to co
    5 min read
  • How to calculate MOVING AVERAGE in a Pandas DataFrame?
    Calculating the moving average in a Pandas DataFrame is used for smoothing time series data and identifying trends. The moving average, also known as the rolling mean, helps reduce noise and highlight significant patterns by averaging data points over a specific window. In Pandas, this can be achiev
    7 min read
  • How to calculate the average of a set of numbers?
    Answer: The formula for calculating the average is: Average=Sum of all values/Number of valuesThe mean or average of the given data distribution is the calculated central value. It is used to determine the average of the given data. It gives a measure of the central tendency of the data. The central
    6 min read
  • How to Calculate the Midrange in Excel?
    MS Excel is a spreadsheet developed by the company Microsoft. Excel provides various kinds of functions and we can insert the data in form of rows and columns and perform operations on the data and yield the results we desired. The Midrange of the dataset in other terms can be specified as average o
    2 min read
  • How to Calculate Moving Averages in Python?
    In this discussion we are going to see how to Calculate Moving Averages in Python in this discussion we will write a proper explanation What is Moving Averages?Moving Averages, a statistical method in data analysis, smooths fluctuations in time-series data to reveal underlying trends. Calculating th
    11 min read
  • How to Create a Tolerance Chart in Excel?
    A tolerance chart shows how a particular data item compares to the maximum and minimum permissible values. In this article, we'll analyze the average results of the students of a class using a tolerance chart. Steps for creating a Tolerance Chart Follow the below steps to create a Tolerance chart in
    2 min read
  • How to Calculate Mean, Median and Mode in Excel
    Understanding the concepts of mean, median and mode in Excel can grow your mathematics and Excel skills. Understanding how to calculate mean in Excel, work out mean on Excel, how to calculate median in Excel, find median in Excel, and calculate mode in Excel can revolutionize how you interpret data.
    9 min read
geeksforgeeks-footer-logo
Corporate & Communications Address:
A-143, 7th Floor, Sovereign Corporate Tower, Sector- 136, Noida, Uttar Pradesh (201305)
Registered Address:
K 061, Tower K, Gulshan Vivante Apartment, Sector 137, Noida, Gautam Buddh Nagar, Uttar Pradesh, 201305
GFG App on Play Store GFG App on App Store
Advertise with us
  • Company
  • About Us
  • Legal
  • Privacy Policy
  • In Media
  • Contact Us
  • Advertise with us
  • GFG Corporate Solution
  • Placement Training Program
  • Languages
  • Python
  • Java
  • C++
  • PHP
  • GoLang
  • SQL
  • R Language
  • Android Tutorial
  • Tutorials Archive
  • DSA
  • Data Structures
  • Algorithms
  • DSA for Beginners
  • Basic DSA Problems
  • DSA Roadmap
  • Top 100 DSA Interview Problems
  • DSA Roadmap by Sandeep Jain
  • All Cheat Sheets
  • Data Science & ML
  • Data Science With Python
  • Data Science For Beginner
  • Machine Learning
  • ML Maths
  • Data Visualisation
  • Pandas
  • NumPy
  • NLP
  • Deep Learning
  • Web Technologies
  • HTML
  • CSS
  • JavaScript
  • TypeScript
  • ReactJS
  • NextJS
  • Bootstrap
  • Web Design
  • Python Tutorial
  • Python Programming Examples
  • Python Projects
  • Python Tkinter
  • Python Web Scraping
  • OpenCV Tutorial
  • Python Interview Question
  • Django
  • Computer Science
  • Operating Systems
  • Computer Network
  • Database Management System
  • Software Engineering
  • Digital Logic Design
  • Engineering Maths
  • Software Development
  • Software Testing
  • DevOps
  • Git
  • Linux
  • AWS
  • Docker
  • Kubernetes
  • Azure
  • GCP
  • DevOps Roadmap
  • System Design
  • High Level Design
  • Low Level Design
  • UML Diagrams
  • Interview Guide
  • Design Patterns
  • OOAD
  • System Design Bootcamp
  • Interview Questions
  • Inteview Preparation
  • Competitive Programming
  • Top DS or Algo for CP
  • Company-Wise Recruitment Process
  • Company-Wise Preparation
  • Aptitude Preparation
  • Puzzles
  • School Subjects
  • Mathematics
  • Physics
  • Chemistry
  • Biology
  • Social Science
  • English Grammar
  • Commerce
  • World GK
  • GeeksforGeeks Videos
  • DSA
  • Python
  • Java
  • C++
  • Web Development
  • Data Science
  • CS Subjects
@GeeksforGeeks, Sanchhaya Education Private Limited, All rights reserved
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox
Improvement
Suggest Changes
Help us improve. Share your suggestions to enhance the article. Contribute your expertise and make a difference in the GeeksforGeeks portal.
geeksforgeeks-suggest-icon
Create Improvement
Enhance the article with your expertise. Contribute to the GeeksforGeeks community and help create better learning resources for all.
geeksforgeeks-improvement-icon
Suggest Changes
min 4 words, max Words Limit:1000

Thank You!

Your suggestions are valuable to us.

What kind of Experience do you want to share?

Interview Experiences
Admission Experiences
Career Journeys
Work Experiences
Campus Experiences
Competitive Exam Experiences