Help writing a formula to omit empty cells

**mknispel** · 02-16-2011, 04:27 PM

I would like to show the average of the contents of rows and columns in a worksheet. The typical formula to do this includes the whole array of cells which skews my average down.

How do I sum and average the row while not including the blank cells. or
How do write a formula to omit the unpoopulated cells in an array?

See attachment for sample

Thanks

**NBVC** · 02-16-2011, 05:30 PM

In B8, try:

=IFERROR(AVERAGEIF(B2:B7,"<>"),"")

copied across

In U2, try:

=IFERROR(AVERAGEIF(B2:T2,"<>"),0)

copied down.

**mknispel** · 02-16-2011, 05:49 PM

Thanks, that was just what I needed. I do not understand the reason for the apostophy quotations in the first formula and apostrophy 0 in the second?

Originally Posted by NBVC

In B8, try:

=IFERROR(AVERAGEIF(B2:B7,"<>"),"")

copied across

In U2, try:

=IFERROR(AVERAGEIF(B2:T2,"<>"),0)

copied down.

**NBVC** · 02-16-2011, 05:53 PM

ooops.. both should have been the same... you can choose either the "" or the 0... this is what is returned instead of an error, when there are no numbers to average... the "" means blank, the 0, well, means 0.