Average function not averaging correctly

[Former Member]

I'm on Xcelsius SP1. Say I have the following in my spreadsheet and have a simple value component displaying the result of D1.

A1: 10

B1: NA(or blank)

C1: 20

D1: =average(A1:C1)

I expect the average to be 15. But when I preview the result in Xcelisus or after an export, I get a blank result in the value component. The flash script in Xcelsius doesn't seem to be able to average this correctly. Anyone have a work around? I have a bunch of cells to average(aggregate) and don't want to include cells with "NA" or blank in my aggregation functions.

[amrsalem1983 · Accepted solution]

the function count all the rows even the value is null

you can use this work around

SUM(A1:C1)/COUNTA(A1:C1)

COUNTA, is a funtion will return the count of the NON EMPTY cells.

good luck

Amr