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

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 ...

AVE problem.xlsx10 KB

excel

Formulas and Functions

Patrick2788

Sep 27, 2024

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))
        )
    )
)

BYROW 9.27.xlsx15 KB

Forum Discussion

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

Resources