Every 5th Value

Nik Fuentes

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?

Genevieve P.

Hi @Nik Fuentes

I agree with @Paul Newcome. Since Smartsheet doesn't have a Position function, bringing every 5th column together in some way (whether through helper columns or by always having them together at the beginning so you can create a range) is the way to do this.

Please provide your feedback to the Product team through this form, here!

Cheers,

Genevieve

Paul Newcome

My suggestion for ease of management and overall build would be to insert helper columns (that can later be hidden) next to each other and then use direct cell references to pull the non-adjacent column data into the adjacent columns.

Nik Fuentes

@Paul Newcome

A workable idea, but unfortunately these sheets are going to be growing in width [in sets of 5 columns] in the future, so I was hoping to avoid adding more columns and it still has the problem of needing to be updated manually with every new addition.

The helper columns are probably going to end up being my fall-back if I can't find a better solution.

Genevieve P.

Hi @Nik Fuentes

I agree with @Paul Newcome. Since Smartsheet doesn't have a Position function, bringing every 5th column together in some way (whether through helper columns or by always having them together at the beginning so you can create a range) is the way to do this.

Please provide your feedback to the Product team through this form, here!

Cheers,

Genevieve