average in non-continuous set, excluding blanks

[JJ]

I want to calculate the average of non-adjacent cells (ie "=average(A3, D3,
G3, J3)"). In some cases there are blank cells in the set. How do I prevent
these blank cells from being counted as zeros? I do not want them included
in the average. I have only found instructions on how to do this when the
set is continuous (ie "=average(A1:A10)").

Thanks for your help!

 

[Mike H]

hi,

Excel ignores blank cells in an average formula so this works

=AVERAGE(A3,D3,G3,J3)

Mike

 

[T. Valko]

AVERAGE ignores empty cells. To exclude numeric 0:

=AVERAGE(IF(A3,A3,{""}),IF(D3,D3,{""}),IF(G3,G3,{""}),IF(J3,J3,{""}))

 

[JJ]

But in practice, it did not. Are there special cases when it works and
doesn't work?

 

[Mike H]

Hi

There are no special cases I'm aware of, if the cell is really blank it's
ignored. What's in the cell is it a formula with the cell formatted to show
particular numbers as zero.

For example 0.001 formatted to 2 decimal places will display as 0.00 but the
..001 is real and would count in an average formula.

As a further example

a1 = 0.001 formatted to 2 decimal places displays 0.00
a2= 4

The average of these would display correctly as 2

Post your formula for these 'blank' cells

Mike

 

[JJ]

Oh! Yes, there are some blanks that are not truly blank (although I made
them appear that way). Here is the formula in those cells:

=IF(AL2=0,"",100*((AL2-AK2)/AK2))

Does this mean I must treat these cells as if they contain a numeric 0?

 

[JJ]

Thanks, I have it figured out. I am embarrassed to admit, I had a subtle typo.