Finding every nth value above a numeric threshold

Copper Contributor

Hello - 


I'm attempting to find every 8th value in a column of values that exceeds a numeric threshold. Any tips on functions to use would be appreciated. 

1 Reply


With 365 you could use

= LET(
    filtered, FILTER(value, value>=100),
    required, QUOTIENT(COUNT(filtered),8),
    idx,      SEQUENCE(required,1,1,8),
    INDEX(filtered, idx)

This filters your original column of values, builds a sequence that is of the correct length and increments in 8s, and finally returns the required values by index.