AverageIFS formula to take the average starting with the first positive number and last positive number and include any zeros in that range

enter image description here

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.


enter image description here

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


enter image description here

Another Note:

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

Leave a Reply