Forum Discussion

jamescosten's avatar
jamescosten
Brass Contributor
Jul 30, 2024

Calculated Column to Average numbers in column, dividing by number of columns with values

I have a SP list that will have some columns populated and some left blank. Each column is scored out of 5. If i have 3 columns with 5 and 3 columns left blank I would like the calculated column to see 3 values, add them and divide by the number of columns with values.

 

So in this case it would see 5,5,5 totalling 15 and divide by only 3 to give me an average of 5. Currently it would divide by 6 columns.

SharePoint Online

1 Reply

  • Rob_Elliott's avatar
    Rob_Elliott
    Silver Contributor
    Jul 31, 2024

    jamescosten this is quite straightforward to achieve with 3 calculated columns, 1 to add up the columns with a value, 1 to calculate the total and then 1 for the average.

     

     

    1: Completed - this adds up the number of columns with a value:

    =IF(ISBLANK(ScoreA),0,1)+
    IF(ISBLANK(ScoreB),0,1)+
    IF(ISBLANK(ScoreC),0,1)+
    IF(ISBLANK(ScoreD),0,1)+
    IF(ISBLANK(ScoreE),0,1)+
    IF(ISBLANK(ScoreF),0,1)

     

     

     

     

     

    2: Total - adds up all the scores

    =ScoreA+ScoreB+ScoreC+ScoreD+ScoreE+ScoreF

     

     

    3: Average - divides the total by the number of columns with a value:
    =TotalScore/Completed

     

     

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User.
    Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

Resources