sum of following 5 cells only if it is a number of 0 or greater

  • 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,


  • Re: sum of following 5 cells only if it is a number of 0 or greater

    This attachment is more specific. You will see the highlighted cells are where the customer first started with the company, For instance customer in Row 15 started just two months before the end of year data messes up the formula. The second sheet gives those two columns a value of -.05 that way it will not add them. I guess really it could add the 6 cells in the row to follow or it could use the value in the sixth cell of sheet 2 since sheet 2 keeps a running total, but either way it has to skip the two columns between the years.

    thank you,

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!