How to calculate average value with null values

[Anonymous]

Dear All,
My query is related to handling null values in calculating the average at row level. Suppose we have 4 columns.

COL1 COL2 COL3 COL4
11 12 11 NULL
24 22 NULL 33
44 NULL NULL 44
55 NULL NULL 55

My query is suppose we need to get average of all the 4 columns i.e. (COL1+COL2+COL3+COL4)/4. As some of column contains null value so we cannot use above method for finding the average i.e. col1+col2.... Also we cannot replace NULL with 0 using ISNULL() as it reduces the average value, in short we need to take only those columns in count which has value and need to hide null values i.e. in first row avg should be of (col1+col2+col3)/3. 
Please let me know if anyone has faced this scenario in past and what solution he/she opted?

[dobregon]

Hi,

I had the same problem than you and i did this:

1. Create a column with the average of the columns as you do: Average(column1,column2,column3,column4)/4 but as you know if one of the column is null as you are dividing by 4 the result will be wrong. So you can use AVERAGE and divide by the number of columns >=0 (to not take into account the null columns), for that you can use COUNTIF (depedns of the range) or if you can't do it and if there are not much columns you can modified the /4 for something like sum(if(column1>=0,1,0),if(column2>=0,1,0).....)

[v-lili6-msft]

Hi@ karthiksunku

After my research , you can do these follow my steps like below:

Step 1:

Click Edit Queries

Step 2:

Select column COL1, column COL2, column COL3, column COL4 then click Add Column->Statistics->Count Values

Result:

Best Regards,

Lin