COUNTBLANK()
by Helmut Reinke Egbert Jeschke Sara Unverhau, Eckehard Pfeifer, Bodo Fienitz, and
Microsoft® Excel® 2010 Formulas & Functions Inside Out
- Microsoft® Excel® 2010 Formulas & Functions Inside Out
- A Note Regarding Supplemental Files
- Introduction
- 1. Solving Problems with Functions
- Introducing the New Functions in Excel 2010
- Scenario and Goals
- The New Way to Work with Data, Formulas, and Functions
- Entering Test Data Fast
- Converting Formula Results into Fixed Values
- Formatting Numeric Values
- Calculating Profit Margin
- Formatting Data as a Table
- Calculating Profit Margin as a Percentage
- Applying Conditional Formatting
- Creating Meaningful Charts
- Working with PivotTables
- Using PivotCharts: Graphical Pivot
- 2. Using Functions and PowerPivot
- Using Date and Time Functions
- The Excel Date System
- Date and Time Number Formats
- Leap Years
- Analysis Functions
- Date and Time Functions in Practice
- Calculating the Start and End of Daylight Saving Time
- Identifying What Day of the Year Today Is
- Calculating with Time
- Calculating Working Hours
- Calculating Beyond the Date Limit
- Adding Time Intervals
- Calculating the Salary for Working Hours
- Rounding Time Values
- Identifying the Quarter
- Converting Normal Hours into Decimal Hours (Industrial Hours)
- Using Text and Data Functions
- Separating Text Strings Such as ZIP Code and Location
- Separating First and Last Names
- Switching the First and Last Names
- Resolving the IBAN
- Calculating the Frequency of a Character in a String
- Removing All Spaces
- Correcting the Position of Signs
- Displaying the File and Worksheet Name
- Concatenating the Content of Cells
- Breaking Lines in Concatenated Text
- Visualizing Data
- Using Logical Functions
- Using Search and Reference Functions
- Using Information Functions
- Using Statistical Functions
- Using Database Functions
- Using Cube Functions
- Using Financial Math Functions
- Using Math and Trigonometry Functions
- PowerPivot
- 3. From Numbers to Formulas
- Entering Formulas
- Operators
- References in Formulas
- Array Formulas
- Tips and Tricks
- Turning the Formula View On and Off
- Entering Formulas in Several Cells
- Entering Formulas in Several Worksheets
- Selecting Formula Cells
- Determining Which Cells Are Referenced in a Formula
- Copying and Moving Formulas
- Moving Formula Cells
- Copying Formula Cells
- Converting Formula Results into Fixed Values
- Converting Existing Values
- Protecting Formulas
- Hiding Formulas
- Specifying the Formula Calculation Type
- Analyzing Formulas
- Troubleshooting: Example 1
- Troubleshooting: Example 2
- 4. Formulas and Functions
- 5. Functions in Special Operations
- Functions in Names
- Functions for Conditional Formatting
- Highlighting Weekends in Color
- Using the MATCH() Function
- Highlighting Identical Values
- Finding the Differences Between Tables on Different Worksheets
- List Print Layout
- Emphasizing the Top Three Elements
- Highlighting Cells Containing Spaces
- Navigating in Tables with Reference Lines
- Formatting Data Groups
- Formatting Subtotal Results
- Tips for Conditional Formatting in Excel 2003
- Tips and Troubleshooting for Conditional Formatting
- Functions for Validation
- 6. Custom Functions
- 7. Date and Time Functions
- 8. Text and Data Functions
- 9. Logical Functions
- 10. Lookup and Reference Functions
- 11. Information Functions
- 12. Statistical Functions
- AVEDEV()
- AVERAGE()
- AVERAGEA()
- AVERAGEIF()
- AVERAGEIFS()
- BETA.DIST()/BETADIST()
- BETA.INV()/BETAINV()
- BINOM.DIST()/BINOMDIST()
- BINOM.INV()/CRITBINOM()
- CHISQ.DIST()
- CHISQ.DIST.RT()/CHIDIST()
- CHISQ.INV()
- CHISQ.INV.RT()/CHIINV()
- CHISQ.TEST()/CHITEST()
- CONFIDENCE.NORM()/CONFIDENCE()
- CONFIDENCE.T()
- CORREL()
- COUNT()
- COUNTA()
- COUNTBLANK()
- COUNTIF()
- COUNTIFS()
- COVAR()
- COVARIANCE.P()
- COVARIANCE.S()
- DEVSQ()
- EXPON.DIST()/EXPONDIST()
- F.DIST()
- F.DIST.RT()/FDIST()
- F.INV()
- F.INV.RT()/FINV()
- F.TEST()/FTEST()
- FISHER()
- FISHERINV()
- FORECAST()
- FREQUENCY()
- GAMMA.DIST()/GAMMADIST()
- GAMMA.INV()/GAMMAINV()
- GAMMALN()
- GAMMALN.PRECISE()
- GEOMEAN()
- GROWTH()
- HARMEAN()
- HYPGEOM.DIST()/HYPGEOMDIST()
- INTERCEPT()
- KURT()
- LARGE()
- LINEST()
- LOGEST()
- LOGNORM.DIST()/LOGNORMDIST()
- LOGNORM.INV()/LOGINV()
- MAX()
- MAXA()
- MEDIAN()
- MIN()
- MINA()
- MODE.SNGL()/MODE()
- MODE.MULT()
- NEGBINOM.DIST()/NEGBINOMDIST()
- NORM.DIST()/NORMDIST()
- NORM.INV()/NORMINV()
- NORM.S.DIST()/NORMSDIST()
- NORM.S.INV()/NORMSINV()
- PEARSON()
- PERCENTILE()
- PERCENTILE.EXC()
- PERCENTILE.INC()
- PERCENTRANK()
- PERCENTRANK.EXC()
- PERCENTRANK.INC()
- PERMUT()
- POISSON.DIST()/POISSON()
- PROB()
- QUARTILE()
- QUARTILE.EXC()
- QUARTILE.INC()
- RANK()
- RANK.AVG()
- RANK.EQ()
- RSQ()
- SKEW()
- SLOPE()
- SMALL()
- STANDARDIZE()
- STDEV.P()/STDEVP()
- STDEV.S()/STDEV()
- STDEVA()
- STDEVPA()
- STEYX()
- T.DIST()
- T.DIST.RT()
- T.DIST.2T()/TDIST()
- T.INV()
- T.INV.2T()/TINV()
- T.TEST()/TTEST()
- TREND()
- TRIMMEAN()
- VAR.P()/VARP()
- VAR.S()/VAR()
- VARA()
- VARPA()
- WEIBULL.DIST()/WEIBULL()
- Z.TEST()/ZTEST()
- 13. Database Functions
- 14. Cube Functions
- 15. Financial Functions
- ACCRINT()
- ACCRINTM()
- AMORDEGRC()
- AMORLINC()
- COUPDAYBS()
- COUPDAYS()
- COUPDAYSNC()
- COUPNCD()
- COUPNUM()
- COUPPCD()
- CUMIPMT()
- CUMPRINC()
- DB()
- DDB()
- DISC()
- DOLLARDE()
- DOLLARFR()
- DURATION()
- EFFECT()
- FV()
- FVSCHEDULE()
- INTRATE()
- IPMT()
- IRR()
- ISPMT()
- MDURATION()
- MIRR()
- NOMINAL()
- NPER()
- NPV()
- ODDFPRICE()
- ODDFYIELD()
- ODDLPRICE()
- ODDLYIELD()
- PMT()
- PPMT()
- PRICE()
- PRICEDISC()
- PRICEMAT()
- PV()
- RATE()
- RECEIVED()
- SLN()
- SYD()
- TBILLEQ()
- TBILLPRICE()
- TBILLYIELD()
- VDB()
- XIRR()
- XNPV()
- YIELD()
- YIELDDISC()
- YIELDMAT()
- 16. Mathematical and Trigonometry Functions
- Functions for Mathematical Calculations
- Functions for Trigonometry Calculations
- Other Functions
- ABS()
- ACOS()
- ACOSH()
- AGGREGATE()
- ASIN()
- ASINH()
- ATAN()
- ATAN2()
- ATANH()
- CEILING()
- CEILING.PRECISE()
- COMBIN()
- COS()
- COSH()
- DEGREES()
- EVEN()
- EXP()
- FACT()
- FACTDOUBLE()
- FLOOR()
- FLOOR.PRECISE()
- GCD()
- INT()
- LCM()
- LN()
- LOG()
- LOG10()
- MDETERM()
- MINVERSE()
- MMULT()
- MOD()
- MROUND()
- MULTINOMIAL()
- ODD()
- PI()
- POWER()
- PRODUCT()
- QUOTIENT()
- RADIANS()
- RAND()
- RANDBETWEEN()
- ROMAN()
- ROUND()
- ROUNDDOWN()
- ROUNDUP()
- SERIESSUM()
- SIGN()
- SIN()
- SINH()
- SQRT()
- SQRTPI()
- SUBTOTAL()
- SUM()
- SUMIF()
- SUMIFS()
- SUMPRODUCT()
- SUMSQ()
- SUMX2MY2()
- SUMX2PY2()
- SUMXMY2()
- TAN()
- TANH()
- TRUNC()
- 17. Engineering Functions
- A. Excel Functions (in Alphabetical Order)
- B. Excel Functions (by Category)
- C. What’s New in Excel 2007 and Excel 2010
- D. About the Authors
- Index
- About the Authors
- Copyright
Syntax. COUNTBLANK(area)
Definition. This function counts the empty cells in a cell range.
Arguments
area (required). The reference to the cells that you are testing for empty cells
Background. This function counts not only the empty cells but also the cells containing empty strings. Cells containing the value 0 are not counted. This function is related to the COUNTIF() function. However, the COUNTIF() function evaluates numbers and text differently.
Note
The COUNTBLANK() function is also included in Chapter 12.
Example. Perform a few tests to get familiar with this function. Assume cells B2 through B8 contain a, b, =””, nothing, 0, 1, and 2:
COUNTBLANK(B2:B8)returns2because the function counts B4 and B5 as empty cells.COUNTIF(B2:B8,">0")also returns2, because two cells contain numbers greater than 0.COUNTIF(B2:B8,">""")returns2because the cells B2 and B3 contain non-empty strings.COUNTIF(B2:B8,"=0")returns1because only B6 contains 0.
However, COUNTIF(B2:B8,"=""") returns 0, and COUNTIF(B2:B8,"=") returns the expected value 1, because cell B5 is empty.
Sample Files
Use the Misc worksheet in the Information.xls or Information.xlsm sample file. The sample files are found in the Chapter11 folder. For more information about the sample files, see the section titled Using the Sample Files.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.