FAQ - Average No Zeros

PROGRAMMING MANAGEMENT		A resource for users and developers of Microsoft Excel	Site Map	Search


			Email us your FAQ suggestion.

			Visit often ! Pages are updated daily.
	This is the Excel "How to" Frequently Asked Questions page
Answers to Frequently Asked Questions about how to do common tasks in Microsoft Excel.

The World of Atlas

Home

Excel Solutions and Examples

Access & Excel	Add-Ins	API	Charts	Class Modules	Comments	Conditional Formatting	Control Toolbox
Data Validation	Date & Time	Dialog Sheets	Edit	Errors	Events	Excel Fact & Fiction	Excel Object
Filter	Find / Replace	Format	Forms Toolbar	Formulas	Glossary	Hyperlinks	Internet & Excel
Keyboard	Menu Bar	Mouse	OLAP Cubes	Outlook & Excel	Pivot Tables	PowerPoint & Excel	Print
Ranges	Ribbon	Sheets	Sort	SQL	Tips & Tricks	User Defined Functions	UserForms
VBA & the VBE	VBA Magic	Window	Windows	Word & Excel	Workbooks	XLM "4.0 Macros"	XML

Excel Community

Excel "How to" FAQs

List of Excel "How to" FAQs with links to their answers.

Data entry: How to...		Formulas for sum, count, lookup, last entry: How to...		Worksheets and workbooks: How to...
	...identify duplicate entries in a range?		...return the last number and its cell address?		...display the worksheet tab name in a cell?
	...prevent duplicate entries in a range?		...return the last text and its cell address?		...display the workbook name in a cell?
	...delete duplicate entries in a range?		...return the last any type entry and its cell address?		...display the workbook path in a cell?
	...force letters to be UPPER CASE?		...return a table's intersecting row & column value?		...display the workbook path and name in a cell?
	...limit how many characters can be entered?		...return the second highest or lowest number?		...display the workbook path, name, sheet in a cell?
	...establish the "carriage return" affect in a cell?		...average without zeros?		...name a worksheet tab based on a cell value?
	...fill empty cells in a column with value from above?		...sum, average, or count colored cells?		...change column headers from numbers to letters?
	...stop Excel from converting an entry into a date?		...sum, average, or count to omit Min or Max?		...verify if a certain worksheet exists; create if not?
	...automatically insert colon : when entering a time?		...sum, average, or count across multiple sheets?		...verify if a certain workbook exists?
	...enter a number with a visible leading zero?		...sum, average, or count visible or unfiltered cells?		...verify if a certain workbook is open?
			...sum, average, or count with multiple conditions?		...add more rows or columns to my worksheet?
Data manipulation, extraction, parsing: How to...			...avoid #DIV/0!, #N/A, and other formula errors?		...know how many sheets my workbook can hold?
	...separate alphanumeric numbers and letters?		...use VLOOKUP to return value in column to left?		...sort sheets by tab name in alphanumeric order?
	...separate first and last names into their own cell?				...open a workbook to a certain sheet and cell?
	...extract all to the left of a certain character?	Programming Visual Basic for Applications: How to...			...prevent workbook being renamed or distributed?
	...extract all to the right of a certain character?		...create, install, and run a macro?		...save or export my workbook as an .exe file?
	...move negative sign from back to front of number?		...create, install, and utilize a User Defined Function?		...keep rows or columns visible when scrolling?
	...sort a list by more than three column fields?		...create, install, and utilize Event code?		...make a drop-down list for users to select sheets?
	...convert numeric values to their equivalent words?		...create, install, and utilize Class Module code?		...know when the workbook was created?
	...add or subtract a day, month, or year of a date?		...force another computer to enable my macros?		...know when the workbook was modified or saved?
	...delete rows where empty cells exist in a column?		...stop the screen movements while macros run?		...open all workbooks, modify them, then close all?
	...delete hyperlinks but keep their underlying text?		...close a workbook without the prompt to save it?		...recover or undo a worksheet I wrongly deleted?
			...delete a sheet without the prompt to confirm it?		...make a workbook expire by deleting itself?
Data Validation: How to...			...save as existing name without prompt to confirm?		...convert each worksheet into its own workbook?
	...make the cell's drop-down arrow always visible?		...deal with a Macro warning when no macros exist?
	...type the first letter to select that part of the list?		...create a Message Box timed to click OK by itself?	XL Zone: Assorted questions...
	...adjust the drop-down list's size, width, or font?		...establish a "carriage return" line in my MsgBox?		Why Excel asks to save when nothing changed?
	...have more than 8 visible drop down list items?		...display a progress bar while my macro runs?		Why Excel asks to update links but there are none?
	...have one list dependent on another list?		...copy to or go to the next available row or column?		Why the 'Too many different cell formats' message?
			...locate the first empty cell in a row or column?		How do I make the Reviewing Toolbar go away?
Format: How to...			...know the last row for one or more columns?		*How do I find wildcard characters "?" and "" ?**
	...conditionally format for more than 3 conditions?		...know the last column for one or more rows?		How do I keep a button always visible on the sheet?
	...colorize the active cell, row, or column?		...know the address of the sheet's used range?		How do I autoclose a workbook after inactivity?
	...format a date when concatenating it with text?		...know the address of the sheet's data range?		How can I put a running clock on my spreadsheet?
	...make a font invisible so a cell's value is not seen?		...select a range of unknown size or location?		How do I ask for input of an unambiguous date?
	...make a cell's value flash or blink to get attention?		...fill a formula down to the last used row?		How do I customize the buttons on a Message Box?
	...format a formula's returned value characters?		...find multiple entries of the same value in a range?		How do I show a MsgBox in a corner, not centered?
	...format the sum of times for hours and minutes?		...get the user name of the computer or network?		What can I do to learn more about Excel and VBA?

How to average a range of numbers while disregarding zeros?

Example

Average scenarios

Formulas

The picture shows various scenarios for conditional averages based on the numbers in range A1:A10.

In cell D1 the formula for a simple average is
=AVERAGE(A1:A10)

In cell D2, the formula to average a list of numbers while omitting zeros is
=SUMIF(A1:A10,"<>0")/COUNTIF(A1:A10,"<>0")

In cell D3, the formula to average only positive numbers is
=SUMIF(A1:A10,">0")/COUNTIF(A1:A10,">0")

In cell D5, the formula to return the maximum (largest) number is
=MAX(A1:A10)

In cell D6, the formula to return the minimum (smallest) number is
=MIN(A1:A10)

In cell D8, the formula to average all numbers except the largest number is
=(SUM(A1:A10)-MAX(A1:A10))/(COUNT(A1:A10)-1)

In cell D9, the formula to average all numbers except the smallest number is
=(SUM(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-1)

In cell D10, the formula to average all numbers except the largest number and the smallest number is
=(SUM(A1:A10)-(MIN(A1:A10)+MAX(A1:A10)))/(COUNT(A1:A10)-2)

Top of Page

List of Excel "How to" FAQs

Home

About Atlas

Excel Consulting

Excel Training

Excel Testing

Atlas Store

Contact Atlas