Hot answers tagged formulas
Two options to try: =IF(ISNUMBER(A2),INDEX(SORT(SAC!F:F,SAC!E:E,0),MATCH(A2,SORT(SAC!E:E,SAC!E:E,0),-1)),) =QUERY(SAC!E:F,"select F where E >= "&A2&" order by E limit 1",0) It's a shame that VLOOKUP doesn't accept the -1 sort parameter, otherwise this would work: =IF(ISNUMBER(A2),VLOOKUP(A2,SORT(SAC!E:F,1,0),2,-1),)
For a conditional running average, assuming all entries in A2:A are grouped: =ArrayFormula(IFERROR((SUMIF(ROW(A2:A),"<="&ROW(A2:A),B2:B)-HLOOKUP(0,SUMIF(ROW(A2:A),"<"&ROW(A2:A),B2:B),MATCH(A2:A,A2:A,0),0))/(ROW(A2:A)-MATCH(A2:A,A2:A,0)-ROW(A2)+2))) Before the update to the newest version of Sheets a number of months ago, it would have ...
Maybe something like: =IFERROR(index(B:B,match(F4,A:A,0)),index(B5:B100,match(F4,A4:A99))) where F4 contains the 6/10 value.
Only top voted, non community-wiki answers of a minimum length are eligible