I am trying to use the formula below which successfully gives me the first cell with a value great than 0 then adds the next 5 and divides by 6 to give me an average. Unfortunately the way my layout is I have two cells after 12 months and I need the formula to recognize that if those two cells are part of the next five it skips those and only counts the next five cells with a 0 or positive value. I have made a second sheet and put the values from the first copied over. The two columns between the years in sheet 2 have a value of -.05 Any ideas.
=IF(D13>0,(SUM(INDEX(N13:HH13,MATCH(TRUE,N13:HH13>0,0)):INDEX(N13:HH13,MATCH(TRUE,N13:HH13>0,0)+5)))/6, 0)
I have attached a sample but basically it is the above formula only with the +5 a conditional statement if the cell >-.01
Much thanks,
cya