Excel AVERAGE function – things to remember! As you’ve just seen, using the AVERAGE function in Excel is easy. However, it does have a few specificities that you need to be aware of. Cells with zero values (0) are included in the average. Cells containing text strings, Boolean values of TRUE and FALSE, and empty cells are ignored.
The AVERAGE function ignores blank cells automatically. However, it will include the zeros which are returned by formulas or other ways but display as blank. To ignoring both blank and zeros when calculating average, we can use the AVERAGEIF function. =AVERAGEIF(range,”<>0″) Replace “range” to the specified range reference based on your need.
The formula, in this case, would be; =AVERAGEIF (A1:A11,”<>0″) This formula eliminates zero values as a result of the criteria expression and Blank cells as default functionality of AVERAGEIF function, so it only counts cells in Excel average without zeros and average if not blank.
What is averageif in Excel?
Excel AVERAGEIFS – average cells that match several criteria. You use the AVERAGE function in Excel to return the average (arithmetic mean) of the specified cells.
Excel has special functions to calculate the average of the number in a range of cells and also calculate the average of cells based on specified criteria, like AVERAGE and AVERAGEIF functions. But there are situations where cells in a range are Blank or may contain zeros, so it may affect the result.
Will excel print blank cells?
By default, Excel won’t print anything if the sheet is completely blank. If you go to File > Print (or use the keyboard shortcut CTRL + P), you’ll see that there is no print preview, as Excel considers a sheet without any value a blank sheet. To print gridlines on an empty sheet, follow these steps: 1.
A cell on the page contains an error, and errors have been selected to print as blank. There are completely blank pages ahead of any pages that contain data (including the first two conditions stated here), which print as blank.
You can press Ctrl + ~ keys on the keyboard to show all the formulas in the worksheet, and you can check the blank value formulas and delete them if you do not need them. Then print the worksheet. You can specify a printing selection to print only. Open the worksheet and select the range you want to print only.
How to print only rows with data in Excel?
Click the File (or Office Button) > Print to print only rows with data in active worksheet or selected range. Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now Sometimes, you may want to print cells with data only with ignoring all blank cells in Excel.