calc in excel

[excel calc]

I am calculating the average of numerical values from the same cell of many
worksheets. some cells don't have available value. the calc returns error
because it substitutes the empty cell with the value 0. How do you write a
formulae that ignore the empty cell and does not include it in the calc?

 

[Mike H]

Hi,

Excel will ignore blank cells in an average formula even across multiple
sheets, try this

=AVERAGE(Sheet1:Sheet3!A1)

Mike

 

[Chip Pearson]

It depends entirely on the calculation. One way is to test each input
cell for 0 length (an empty cell) and only if all cells are filled in
would you do the actual calculation. E.g.,

=IF(AND(LEN(A1)>0,LEN(A2)>0,LEN(A3)>0),YourCalculation,NA())

Here, the YourCalculation is your original formula. If cells A1, A2,
and A3 all have data, then YourCalculation is evaluated. If any one or
more of these cells is empty, the formula returns an #N/A error.

The AVERAGE function ignores empty cells (it does not treat them as
zero), but you can use an array formula to force this. E.g,

=AVERAGE(IF(LEN(A1:A5)>0,A1:A5,FALSE))

This is an array formula, so you *must* press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this properly, Excel will display the formula
enclosed in curly braces { }. See
http://www.cpearson.com/Excel/arrayformulas.aspx for much more info
about array formulas.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 22 Oct 2008 14:43:01 -0700, excel calc <excel