Every 5th Value


I have a range that is 1 row high and twenty-some-odd columns wide. I need to AverageIF() some of the values in this field together, starting by looking at only every 5th column and then if they're not equal to 0. The 0 part is easy, as is setting up the AverageIF(), but where I'm having trouble is in finding a way to get every 5th column. Theoretically, I could do something like MOD(Position(@cell),5)=0, but so far as I can tell, there is no Position() function, something that checks the position in an array of a given value. the closest I can find is Match(), but that requires a specific search value, which would negate the point of this function.

There also seem to be precious few functions that can reference non-adjacent values. Ideally, I'd use a SumIF() function and just manually divide by the number of values [ignoring for the moment the lack of an ArrayLength() function], but that requires an adjacent range, just like AverageIF().

I want to avoid doing this manually [Sum(Column5@row, Column10@row, Column15@row, ...)] as much as possible, as there's going to be, like, twenty instances of this formula each for a half dozen different sheets and I'm not likely to be the one who has to update it. I've been over the functions list [Functions List | Smartsheet Learning Center] a half a dozen times now and I'm not seeing anything that will do it. Am I just missing something? Or does anybody have a workaround?


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!