0

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:

enter image description here

1

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.

enter image description here

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.