EXCEL AVERAGE FORMULA PROBLEM

[elfa]

Hello,

If the following numbers are in the following cells the average is as follows:

a1=2 a2=2 a3=2 a4=2
=Average(a1:a4)
=2

What if cells a3 and a4 are blank? the formula returns an average of 1.

Is there a formula I can use that will only calculate the average based on populated cells?

ie look at cells a1:a4 but give me an average of 2 if a3 and a4 are blank.

Thanks
Elfa.

 

[IFRs]

I think you are looking for AVERAGEA

 

[xlbo]

If 2 cells are empty, I get an average of 2
It is only if they contain physical zeros that I get an average of 1......

Rgds, Geoff

 

[fkegley]

Are you putting a blank space in the blank cells or just deleting the contents? I seem to remember similar behavior in early versions of Lotus 1-2-3. What it would do in those days is count the number of entries in the range, whether or not they were numeric entries, and use that as the divisor when computing the average. I seems to me that the behavior carried over into Excel, also. I cannot remember the first version of Excel that did not do this, but I do remember that eventually this behavior was fixed. I am using version 2000 and it is NOT doing that.

Frank kegley
fkegley@hotmail.com

 

[KenWright]

And if you wanted to ignore blanks AND 0s then you can use:-

=AVERAGE(IF(Rng,Rng))

array entered.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------