Find the first 12 cells in a row, >=10, and take a Average

[Brirack77]

Hi all,

I have a challenge with calculating the Average within a row of cells, based on what's in those cells. I need to find the first 12 cells that are e.g.  >=10, and take an average, but ignore cells <10 and move to the next cell if need be.

Any ideas??

[#2 · Accepted solution]

@Brirack77 

I offer an Excel 365 solution using BYROW:

=BYROW(
    scenarios,
    LAMBDA(each_scenario,
        LET(
            crit, each_scenario >= 10,
            filtered, FILTER(each_scenario, crit, 0),
            AVERAGE(TAKE(filtered, , 12))
        )
    )
)

[#3]

phenomenal thank you 🙏

[#4]

You're welcome!

[#5]

@Brirack77 Simpler version of BYROW().

=BYROW(B7:P9,LAMBDA(r,AVERAGE(TAKE(FILTER(r,r>=D2),,12))))