Averaging function is pulling back a zero in Excel
 

I have a simple averaging formula in an excel spreadsheet that is now
providing nothing but a zero as a result of the formula. I have ensured that
the cell format is for "Number" and that all of the cells for the formula are
formatted for "number" as well. I am not getting the "DIV/0!" error so I know
it is not a dividing by zero error.

Are you using AVERAGE() or a formula you created to do the average?

Maybe the precission you chose to display is too small. i.e. if you have a
cell formatted to show no decimal places a number like .15 will be rounded to
0. Try increasing precision: Format-Cells-'Number' tab

--
Regards,
Dave


"Thennessee" wrote:

I have a simple averaging formula in an excel spreadsheet that is now
providing nothing but a zero as a result of the formula. I have ensured that
the cell format is for "Number" and that all of the cells for the formula are
formatted for "number" as well. I am not getting the "DIV/0!" error so I know
it is not a dividing by zero error.

Changing the format of the cell doesn't change the value in the cell. But the
next change you make to that cell will make it numeric.

One way to coerce these text numbers to number numbers is to:
copy an empty cell
select the range to fix
edit|paste special|check add

If that doesn't work for you, maybe it's because there's something else in the
cell. If you copied the data from a web page, you may have those HTML
non-breaking spaces in the cell.

You may want to try David McRitchie's routine to clean that stuff up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Thennessee wrote:

I have a simple averaging formula in an excel spreadsheet that is now
providing nothing but a zero as a result of the formula. I have ensured that
the cell format is for "Number" and that all of the cells for the formula are
formatted for "number" as well. I am not getting the "DIV/0!" error so I know
it is not a dividing by zero error.

--

Dave Peterson

More info, please. What exactly is the formula you are using? What is
in the referenced cells? What is the result if you use =COUNT(dataRange)?

Jerry

Thennessee wrote:

I have a simple averaging formula in an excel spreadsheet that is now
providing nothing but a zero as a result of the formula. I have ensured that
the cell format is for "Number" and that all of the cells for the formula are
formatted for "number" as well. I am not getting the "DIV/0!" error so I know
it is not a dividing by zero error.