Number formatting error

[Mark Warbeck]

We're working with a workbook with 115 rows of data. Most are numbers, some
are text and some are blank. The last rows contain formulas using count and
avg. These formulas return the wrong results since, even though the cells
are formatted as numbers, Excel doesn't see them as numbers. The help file
contains a procedure to multiply the cells by 1. This seems to change things
so that Excel recognizes them as numbers and things compute correctly. The
concern is that it would be easy to miss the problem and thus work with
incorrect data. Is there a solution so that Excel always recognizes a cell
as what the formatting is set to be? This is Excel 2002 SP1.

Thanks,

 

[Earl Kiosterud]

Mark,

Here's one way. It'll accomodate "text numbers."

=AVERAGE(A2:A5+0)
=SUM(A2:A5+0)


These are array formulas. Use Ctrl-Shift-Enter instead of Enter any time
you edit them.