Need help with Date Ranges for Averageifs to collect data without affecting form


I collect data everyday using forms to input the data. My problem is that If i prepopulate my formula to capture all data entered, the form only inputs to the row that has not been apart of the Formula. Can someone please help me capture the data in "Daily %" and then only collect it for certain date ranges ? I want to capture month to month so it auto updates the daily inputs as well. Please help


  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @McPhersonTim,

    I would go with this formula as a column formula in Monthly %:

    =AVERAGEIF(Date:Date, MONTH(@cell) = MONTH(Date@row), [Daily %]:[Daily %])

    This would mean you don't need to average a range as the formula will cover it for you and thus any gaps shouldn't matter.

    Example (the dates/numbers are simple to show it works):

    Hope this helps; if you've any questions etc. then just post! 😊

  • McPhersonTim

    This worked absolutely perfect. Thank you so much. If you have time, will you please explain to me why this works?