Forum Discussion
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 s...
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)