TipH1018


NewDeal Hot Tip 1018

NewCalc

NewCalc, Averages

Question: When is use the AVG() function, it includes blank cells as if they contained zeros. I want to average only the cells that contain entries. How can I do this?

Answer: Blank cells are treated as if they contain zeros. One way to avoid this is to prefill the range of cells with apostrophes to prevent NewCalc from treating the cells as if they contained zeros.

After entering apostrophes in all the cells in the range, NewCalc will think that each cell contains text, until and unless you enter a number in the cell. Therefore, we can use the ISNUMBER() and ISSTRING() functions to test the cells and average only those in which you have entered numbers.

If the cells you wish to average are A1:A10 and are prefilled with apostrophes, then in cell B1, enter =IF(ISNUMBER(A1),A1,0). Copy and paste B1 into the range B1:B10. In Cell C1, enter =IF(ISSTRING(A1),0,1). Copy and paste C1 into the range C1:C10. Now sum the range B1:B10 and divide by the sum of C1:C10 to get your average, i.e. =SUM(B1:B10)/SUM(C1:C10).


Last Modified 18 Oct 1999
©2000 NewDeal Inc. All rights reserved


BlankHomeBlank
Eine Seite zurück

Kommentare sind geschlossen.