Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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
Is there a way to do this dynamically so that in case new columns are added the expression need not be changed each time?
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!
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
Thanks! RangeNumericCount() function shorten by alot !