Average IF, but stop If

USAFRetired20

I need a formula that if I am in the "KPI Calculation Average" column (location for script result) it will look at the "Consecutive Years" column rows above (i.e., if script is in row 7 it will look in rows 7 - 1). If the row is greater than '0' then stop BUT count the first '0' in the search. So for example if it searches and in rows 2 and 3 there is a 0, then it would capture what I am seeking in row 3 but not 2. Once it goes through that it will look at the "Availability" column for those rows it identified as being greater than 0 (but also including the first 0) and then averaging them.

Note: those are dummy values

prime_nathaniel

@USAFRetired20 I am unaware of a way to do this withouting using bridge / API and JS. If I am reading this correctly you want to a variable range collection based on calculated index of a value to be averaged.

USAFRetired20

@prime_nathaniel that is correct. After to many hours of AI searches, google, and trial and error I think I have to simply manually enter or see if Excel has that capability and copy past results. Not horrible, but trying to make this project as automated as possible.