Calculating averages

[Carl Johnson]

I have 5 cells that I am trying to average, but I only want to average those
cells that contain data. Using the if statement I was able to get two cells
to work, but when I tried a nested if statement I was unable to get it to
work if there was only 1, 2, or 3 cells with data in them. What do I need
to do to resolve this? Thanks in advance.

 

[CLR]

=SUM(A1:A5)/COUNT(A1:A5)

Vaya con Dios,
Chuck, CABGx3

 

[Carl Johnson]

Hi CLR and Ken, I tried both of your suggestions, unfortunately neither
worked for me. The cells are not adjacent and the count function will not
count blank cells but it will count cells that have a zero value which mine
do. Any other suggestions will be appreciated.

 

[Myrna Larson]

COUNT isn't needed here. AVERAGE skips text and blank cells.

 

[Myrna Larson]

Select all of the cells you want to include. They don't need to be contiguous. Then assign a
name to them via Insert/Name/Define. If you used the name Sales, then you can just write the
formula =AVERAGE(Sales)

 

[Carl Johnson]

Hi Myrna I defined the range (calories) of 5 cells and entered the formula
=Average(calories). I then entered 25 in one cell and 75 in another. The
answer came back 25 when it should have been 50. When I examined the range
all five cells were defined.

 

[Myrna Larson]

Check your range definition again. It does work. To be sure, use Edit/Goto and select Calories
from the list. Then set the background color so you can see which cells are included.

 

[Tom Ogilvy]

If your source cells are showing zero, then I suspect they are produced by a
formula.

Adjust your formulas so they don't return zero

=if(formula=0,"",formula)

then you can use average.

--
Regards,
Tom Ogilvy

Hi Myrna I defined the range (calories) of 5 cells and entered the formula
=Average(calories). I then entered 25 in one cell and 75 in another. The
answer came back 25 when it should have been 50. When I examined the range
all five cells were defined.

Select all of the cells you want to include. They don't need to be contiguous. Then assign a
name to them via Insert/Name/Define. If you used the name Sales, then

you
can just write the

 

[Tom Ogilvy]

He said his cells have zero in them (at least that is what I understood):

The cells are not adjacent and the count function will not
count blank cells but it will count cells that have a zero value which mine
do. Any other suggestions will be appreciated.

although average(75,25,0,0,0) = 20, and average(75,25,0,0,"") = 25, so hard
to say.

He apparently doesn't want to include blanks or zero cells.

--
Regards,
Tom Ogilvy

Check your range definition again. It does work. To be sure, use Edit/Goto and select Calories
from the list. Then set the background color so you can see which cells are included.

Hi Myrna I defined the range (calories) of 5 cells and entered the formula
=Average(calories). I then entered 25 in one cell and 75 in another. The
answer came back 25 when it should have been 50. When I examined the range
all five cells were defined.

COUNT isn't needed here. AVERAGE skips text and blank cells.

 

[Carl Johnson]

Hi Tom, Harlan, and Myrna.
Yes Tom you are correct the zeros are being produced by formulas.
I entered the formula that you suggested =if(sumD1521 = 0,"",sumD15:d21)
but I get a naming error. What is wrong with my formula. Again thanks to all
for all of your help. Believe me when I say I really appreciate it.

He said his cells have zero in them (at least that is what I understood):

The cells are not adjacent and the count function will not
count blank cells but it will count cells that have a zero value which mine
do. Any other suggestions will be appreciated.

although average(75,25,0,0,0) = 20, and average(75,25,0,0,"") = 25, so hard
to say.

He apparently doesn't want to include blanks or zero cells.

--
Regards,
Tom Ogilvy

Check your range definition again. It does work. To be sure, use

Edit/Goto
and select Calories

from the list. Then set the background color so you can see which cells are included.