Find the first cell in a column >0 and last column <1 and count the rows between

  • I am trying to average out customers bills per month. I need to take the first month they started paying to the current month. Then count the months between and divide by that so that I can get the average. The only hiccup is that my layout has a set of statistics including two columns after every year.


    Much thanks,
    cya

  • Re: Find the first cell in a column &gt;0 and last column &lt;1 and count the rows between


    It is a little difficult from the data you have posted to work out what you are after. It might be easier if you just post a relevant portion of the data and highlight what you want to achieve.

  • Re: Find the first cell in a column &gt;0 and last column &lt;1 and count the rows between


    Okay I hope this looks more relevant, and I will do my best to explain.


    The hightlighted column represents the month after the current month. Below the month names you will notice column AD= 0. I want to take the total amount a customer has paid, and divide by the total months they have been a customer.
    Example:
    Customer Line 16 started with us in February cell 016. This customer is consistent but most are not. The amount may be 0 in one cell after they have started. I want to add the total from Cell O16 to the current month AC16. Then I want the formula to count the cells between the start date(o16) and the current month(AC16) and divide by that number. The answer in this case is $65 because they have been consistent with $65 every month. Customer line 19 however started at a higher amount but they missed February (AC19) of the second year.


    I hope this helps explain it a little better. Thank you for helping me.


    cya

  • Re: Find the first cell in a column &gt;0 and last column &lt;1 and count the rows between


    I think what you want is difficult but you could use a work around but it would mean you need to put a 0 value in for non-payments and leave periods prior to the customer starting blank.


    If you use the count formula it will then tell you how many payments have been made, eg if change Z12 to 08 Averages and use the formula:


    =IF(ISERROR(SUM(N13:Y13)/COUNT(N13:Y13)),0,SUM(N13:Y13)/COUNT(N13:Y13))
    in Z13 and drag down
    then in AN12 have the title 09 Averages use the formula:
    =IF(ISERROR((SUM(N13:Y13)+SUM(AB13:AM13))/(COUNT(N13:Y13)+COUNT(AB13:AM13))),0,(SUM(N13:Y13)+SUM(AB13:AM13))/(COUNT(N13:Y13)+COUNT(AB13:AM13)))


    The if formula and iserror is only necessary to avoid error results being shown. If you remove the iserror part you can use
    =SUM(N13:Y13)/COUNT(N13:Y13)


    =(SUM(N13:Y13)+SUM(AB13:AM13))/(COUNT(N13:Y13)+COUNT(AB13:AM13))


    In order to simplify further you could start using named ranges, eg if sam was the customer name in A13 you could highlight the range N13:Y13 and call it Sam08 (you do this by typing Sam08 in the cell at the top left of the screen that tells you the first cell in the current range you have selected... called the "Name Box") then highlight AB13:AM13 and call it Sam09... you could then replace the formula above with names instead of ranges... however this would mean you could not drag the formulas down without doing some additional work.


    Does any of that help?


    Cheers


    Justin

Participate now!

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