Working on a formula in Google Sheets to return True/False based on searching through an array of data using:


This formula returns the highest date in the previous 365 day period.

Next I need to identify what the largest percentage gain was during that same 365 day time period. It may or may not be during that 52 week high.

Stuck finding a solution so thought I'd ask the community to see if you can help?

Thoughts: Maybe use LAMBDA function and build a custom script like the following to identify. Just not sure how to build this within Sheets as the following example is VBA and just learning Sheets now.

Function jumpfound(rar As Range)
  rr = rar.Value
  Jlim = 1.75
  For i = 5 To UBound(rr, 1)
    maxv = 0
    minv = (2 ^ 31) - 1
    jumpfound = False
    For j = 0 To 4
      If minv > rr(i - j, 1) Then minv = rr(i - j, 1)
      If maxv < rr(i - j, 1) Then maxv = rr(i - j, 1)
      If ((maxv - minv) / minv) > Jlim Then
        jumpfound = True
        Exit For
        End If
      Next j
      If jumpfound Then Exit For
      Next i
      End Function

1 Answer 1


Use reduce() and filter(), like this:

  now, today(), 
  data, googlefinance(A2, "high", edate(now, -12), now), 
  highs, choosecols(data, 2), 
  gains, reduce(tocol(æ, 2), sequence(rows(highs)), lambda(a, i, let( 
    c, index(highs, i), 
    p, index(highs, i - 1), 
    gain, iferror((c - p) / p), 
    vstack(a, gain) 
  filter(hstack(data, to_percent(gains)), gains = max(gains)) 

See let(), edate(), choosecols(), reduce(), tocol(), sequence(), lambda(), index(), iferror(), vstack(), hstack() and filter().


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.