Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Average function not averaging correctly

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.

Former Member
replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question