I need a dynamic AverageIFS formula that will start taking the average with the first positive number and end the average at the last positive number. I need the formula to include zeros in the average if there are any in between the first and last positive number. Using the attached image as an example, I want the average_range to be Dec-21 through Nov-22 but I only want the Average to calculate Dec-21 through Jul-22 since that range contains the first and last positive numbers. The average number I’m looking for based on the numbers provided is 1.375. Thank you all for your help!

I tried the AverageIFS function but I’m not having any luck. I know I’m doing something wrong but not sure what.

>Solution :

we can use two indexes, and xmatch which allows us to search backwards and forwards.

=AVERAGE(INDEX(A2:L2,XMATCH(TRUE,A2:L2>0,0,1)):INDEX(A2:L2,XMATCH(TRUE,A2:L2>0,0,-1)))

---

If one does not have XMATCH the we can use MATCH for the first and AGGREGATE for the second:

=AVERAGE(INDEX(A2:L2,MATCH(TRUE,A2:L2>0,0)):INDEX(A2:L2,AGGREGATE(14,7,(COLUMN(A2:L2)-MIN(COLUMN(A2:L2))+1)/(A2:L2<>0),1)))

---

Another Note:

With older versions one may need to use Ctrl-Shift-Enter instead of Enter when exiting edit mode.