# Average function in Sharepoint - Not compute empty cell

I need the Average of the 4 variables, if one of the variable is empty, then the Average should be the Average of the 3 variables only. This is my formula.

`````` =AVERAGE(VALUE([Variable1]),VALUE([Variable2]),VALUE([Variable3]),VALUE([Variable4]))
``````

The problem is when one of the variable empty, Sharepoint gives the empty cell a value as 0 and therefore the value of the average changes.

Many thanks for your help

Example:

You can write below formula to generate in AVERAGE in SharePoint calculated column.

`=AVERAGE([Column1],[Column2],[Column3],[Column4])`

Note:- Empty cell SharePoint doesn't count in AVERAGE function

For more details about `AVERAGE` function you can refer this

You can use below formula to Calculate Average using `VALUE` function

`=SUM(VALUE([Column1]),VALUE([Column2]),VALUE([Column3]),VALUE([Column4]))/SUM(IF(ISBLANK([Column1]),0,1),IF(ISBLANK([Column2]),0,1),IF(ISBLANK([Column3]),0,1),IF(ISBLANK([Column4]),0,1))`

You can refer below screenshot. Avg column return value using `AVERAGE` function and AvgValue column return using `VALUE` & `SUM`.

• Thanks for your comment. Your comment made me realize that the problem is not the Average but the VALUE. Actually, I forget to mention that the variables are the value of a cell(Inputs from user) . And in my case, if the cell doesn't any value, the VALUE function returns it to 0. any hint on how I can solve that? – Emily evans Jan 29 at 13:25
• Why you used VALUE? You can simply use AVERAGE function. but any way I have updated code to calculate average using VALUE. May be It can help you – Bharat Khunti Jan 30 at 5:29
• Thank you so much Bharat Khunti. I really appreciate your help. It solved my problem, you are the best. – Emily evans Feb 4 at 16:26