Finding every nth value above a numeric threshold

%3CLINGO-SUB%20id%3D%22lingo-sub-3206699%22%20slang%3D%22en-US%22%3EFinding%20every%20nth%20value%20above%20a%20numeric%20threshold%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3206699%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20attempting%20to%20find%20every%208th%20value%20in%20a%20column%20of%20values%20that%20exceeds%20a%20numeric%20threshold.%20Any%20tips%20on%20functions%20to%20use%20would%20be%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3206699%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3206950%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20every%20nth%20value%20above%20a%20numeric%20threshold%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3206950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1316172%22%20target%3D%22_blank%22%3E%40srbritsch%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20365%20you%20could%20use%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20filtered%2C%20FILTER(value%2C%20value%26gt%3B%3D100)%2C%0A%20%20%20%20required%2C%20QUOTIENT(COUNT(filtered)%2C8)%2C%0A%20%20%20%20idx%2C%20%20%20%20%20%20SEQUENCE(required%2C1%2C1%2C8)%2C%0A%20%20%20%20INDEX(filtered%2C%20idx)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20filters%20your%20original%20column%20of%20values%2C%20builds%20a%20sequence%20that%20is%20of%20the%20correct%20length%20and%20increments%20in%208s%2C%20and%20finally%20returns%20the%20required%20values%20by%20index.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

@srbritsch 

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.