4 Replies Latest reply on May 15, 2018 2:20 PM by Matt Youngberg

# Average Calculation Across Columns

Hey everyone,

Preface: I've looked at similar questions to mine on this forum and I still haven't been able to solve my issue. Hopefully you can all help me.

I have some simple binary data from fields that I've calculated. I'm looking to take kind of a weird average from multiple columns, like this:

 C1 C2 C3 1 0 1 1 null 1 1 1 0 0 1 1 null 0 1 1 0 1

Obviously, an average can be calculated simply by a type of SUM/COUNT calculation as long as null values are disregarded. However, I don't want to calculate the value of every column individually, I want to calculate an average aggregately. I simply need to be able to take the reported number and drop it onto the Text mark on it's own sheet to be able to include it on a dashboard latter.

So in this example, the calculation should come out to: .6875, or 11/16.

How would I code a calculated field in this example to produce this number for me? Any help is greatly appreciated. Thanks!

• ###### 1. Re: Average Calculation Across Columns

This will work

```(sum([C1])+sum([C2])+sum([C3]))
/
(count([C1])+count([C2])+count([C3]))
```
1 of 1 people found this helpful
• ###### 2. Re: Average Calculation Across Columns

Hey Chris McClellan,

Thanks for responding. It looks like it's calculating the number I'm expecting correctly, but I'm worried about one more thing regarding this code:

If I look at my data source row by row, the calculated field is returning a null value any time that it encounters a null cell in its computation, kind of like this:

 C1 C2 C3 Calculated 1 0 1 0.666666 1 null 1 null 1 1 0 0.666666 0 1 1 0.666666 null 0 1 null 1 0 1 0.666666

When it does the calculation mentioned above row by row down the data source, I imagine that some of those SUM functions are finding null values since it's referencing just a null single cell in the column. If that's the case, my idea is that it is negating the entire equation as null when one of those SUM functions break.

Is that a correct diagnosis and is there a way to prevent that so that each row reports a value when you're asking the calculation to run at a row-by-row level? Thanks for the help!

• ###### 3. Re: Average Calculation Across Columns

Hmmm .... I haven't tested, but try this:

```(sum(zn([C1]))+sum(zn([C2]))+sum(zn([C3])))
/
(count(zn([C1]))+count(zn([C2]))+count(zn([C3])))
```
1 of 1 people found this helpful
• ###### 4. Re: Average Calculation Across Columns

Hey Chris McClellan,

I got it to work properly after removing the ZN function from the COUNT functions. Thanks so much for the help!