Forum Discussion
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 cells <10 and move to the next cell if need be.
Any ideas??
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)) ) ) )
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)) ) ) )- phenomenal thank you 🙏
- You're welcome!
