Posts by 2rrs

    Thanks Neale,

    I'll give it a try.
    This is charting new water for me.


    Hi Imbuzi,

    I see that Neale and Paddy have come up with a rather nice solution to your request.

    I like the drop down that you have for the calendar; but could not figure out how it was done.

    Could you or someone please describe how to go about setting that up.

    tia, 2rrs

    Hi Paddy,

    Great formula; but it is returning the sum (not the average); I changed the SUM to AVERAGE and it worked beautifully for ytd average.


    Hi, try this for the number of occurrences in the past 7 days;

    With your dates in E:E

    In F1 enter =E1-7 and drag down

    In G1 enter =COUNTIF(E:E, ">=" & F1)-COUNTIF(E:E, ">" &E1) and drag down

    HTH, 2rrs

    Hi tinka1,

    I had the very same problem that I solved with the following:
    I use E2 (header row) with new rows being added in E3. I use this for "Average", but it works for sum as well.

    =SUM(OFFSET(E2,1,0):E112) "sum of all days" E112 is dynamic and will change to E113 etc as rows are added.

    =SUM(OFFSET(E2,1,0):OFFSET(E2,5,0)) "sum of 5 days"

    =SUM(OFFSET(E2,1,0):OFFSET(E2,10,0)) "sum of 10 days"

    =SUM(OFFSET(E2,1,0):OFFSET(E2,15,0)) "sum of 15 days"

    =SUM(OFFSET(E2,1,0):OFFSET(E2,25,0)) "sum of 25 days"

    Hope this helps, 2rrs