Solved: Calculate Moving average - Page 2

Oluwaseun · 11-23-2022 01:07 PM

Hi, I am still quite new to the use of the Google App-sheet and formulas, Pardon me.

So Let me be as basic as possible. I am trying to Find out the Moving average for a scorecard with Four (4) columns

Customer Engagement
Customer interactions
Process Compliance
Issue Resolution

I want App-sheet to give me an overall average even if I do not capture a value for any of the columns (which I regard as Not Applicable)

I currently use the formula

(AVERAGE(LIST([Customer Engagement],[Issue Resolution],[Process Compliance],[Customer Handling]))), but it does not ignore a column if I don't capture a value for it

Thanks

TeeSee1

I was curious and did a quick test.

Avg Score: This, I believe, gives the expected results by @Oluwaseun

SUM(LIST([fld1],[fld2],[fld3],[fld4]))
/ 
(
 if(isnotblank([fld1]),1,0) +
 if(isnotblank([fld2]),1,0) + 
 if(isnotblank([fld3]),1,0) + 
 if(isnotblank([fld4]),1,0)
)

This takes a score of 0 (zero) as a valid score. You can add a condition to deal with that if you want.

Avg Score 2

AVERAGE(
 LIST([fld1],[fld2],[fld3],[fld4]) - LIST("")
)

List Sub Result

LIST([fld1],[fld2],[fld3],[fld4]) - LIST("")

Apparently list subtraction reduces its resultant list to unique elements and does not fit here.

View solution in original post