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

Brass Contributor

# 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.

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

@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)