Find the first column with sum of <1 then add the previous six cells.

  • Basically just that.

    I need to find the last column with a value greater than 0, or the first column with a value of <1.
    Then add the previous six cells and divide by 6.

    Much thanks,


  • Re: Find the first column with sum of &lt;1 then add the previous six cells.

    okay maybe this attachment and example will help.

    The highlighted yellow column AD represents the month after the current month. The cell the formula will fit into is cell H13-150. and I guess I must add the column <1 must come after a column >1 so as not to confuse the formula and choose the very first month if blank.
    Customer Row 19. The formula first recognizes that column N in this case is >0 and so it starts looking for the next Column that is <1. It sees that Column AD <1 from customer rows 13-150. The formula then adds the previous 6 cells in row 19 and divides by 6. The only hitch is the two rows between the sets of 12 months. Because of this I have made a second sheet and those two rows values are -.05 Therefor the formula would actually have to read the previous six rows with a value >-.01

    I hope this all makes sense. If something is not right in the asking or explanation please let me know, or maybe a better way I could set this up.

    much thanks

Participate now!

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