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.