How to count number of not Null values in a row

[wenjun2016]

Sept	Oct	Nov	Dec	Average
3	3	2		(3+3+2+0)/3
2	5	4	1	(2+5+4+1)/4
3	4			(3+4+0+0)/2

How to count number of values across 4 data columns, excluding null values?

Thank you.

[MK_QSL]

RangeSum(IF(Len(Trim(Sept))<>0,1),IF(Len(Trim(Oct))<>0,1),IF(Len(Trim(Nov))<>0,1),IF(Len(Trim(Dec))<>0,1))

[surendraj]

Hi,

With reference to manish kachhia

Try this

RangeSum(Sept,Oct,Nov,Dec)/RangeSum(IF(Len(Trim(Sept))<>0,1),IF(Len(Trim(Oct))<>0,1),IF(Len(Trim(Nov))<>0,1),IF(Len(Trim(Dec))<>0,1))

it works!!

--Surendra

[anushree1]

Is there a way to do this dynamically so that in case new columns are added the expression need not be changed each time?

[wenjun2016]

Hi Surendra and Manish,

Thank you for your reply.

It works.

Ya I have the same question with Anushree below.

When data months are added, we have to change the expression again, is there a way like count dynamically?

Thanks!

[Michiel_QV_Fan]

For starters you could use 2 range functions for your expression:

rangesum(Sept, Oct, Nov,Dec) / rangenumericccount(Sept, Oct, Nov,Dec)

This eliminates the LEN and Trim functions.

For the next part please refer to this post: Re: Create script expression from variable no of fields

[wenjun2016]

Thanks! RangeNumericCount() function shorten by alot !