Formula problem

[JE McGimpsey]

One way:

=IF(COUNT(B2:H2)>0,AVERAGE(B2:H2),"")

 

[Peter McCaul]

Thank you. Worked like a charm.

One way:

=IF(COUNT(B2:H2)>0,AVERAGE(B2:H2),"")

I have an excel sheet that details store sales with 7 columns of numbered
data. I would like to make an 8th column that gives me the average of
the
first 7 columns but not every cell has a number. My average formula
could
read @sum(b2..h2)/7. If one of the cells in the row doesn't have a
number
in it, I would like the average formula to read @sum(b2..h2)/6. Some of
the
rows may have only 2 numbers in it cells so in that case it would have to
be
divided by 2.

I would like to know if anyone can help me to devise a formula that will
automatically do this? Any help is greatly appreciated. Thank you.




begin 666 p.gif
[Image]

end

 

[Gord Dibben]

Peter

Why not =AVERAGE(B2:H2) which ignores blanks.


Gord Dibben Excel MVP

 

[Peter McCaul]

I have an excel sheet that details store sales with 7 columns of numbered
data. I would like to make an 8th column that gives me the average of the
first 7 columns but not every cell has a number. My average formula could
read @sum(b2..h2)/7. If one of the cells in the row doesn't have a number
in it, I would like the average formula to read @sum(b2..h2)/6. Some of the
rows may have only 2 numbers in it cells so in that case it would have to be
divided by 2.

I would like to know if anyone can help me to devise a formula that will
automatically do this? Any help is greatly appreciated. Thank you.

 

[Peter McCaul]

Thanks, that worked as well.

Peter

Why not =AVERAGE(B2:H2) which ignores blanks.


Gord Dibben Excel MVP