Average formula

Deanna Vandermeer

Once again, formula genius to the rescue. I am forever grateful!!

Deanna Vandermeer

Paul - another quick question, what if folks submit requests for the same assessment on the same day, would the best way to avoid the formula not calculating properly be to use the Created column type instead of a simple date picker column type? I just noticed that it will return identical numbers in the rows if their dates are matching so it got me thinking or do you have a solution to help with this scenario as well?

Paul Newcome

How are they submitted?

Deanna Vandermeer

They will submit via a form

Paul Newcome

The Created Date column type should work for you. If not, let me know. I may have another idea as well that should work.

Deanna Vandermeer

I think we may need your other idea, today we were testing a few of the features and noticed that it isn't quite working still (might have something to do with the time function of the Created column type)? Anyway, can you share your other idea on how to make that work? Thanks.

Paul Newcome

Sure thing! Are the forms populating the bottom of the sheet or the top of the sheet?

Deanna Vandermeer

The bottom of the sheet, should I have that reverse, would that be easier?

Paul Newcome

Bottom of the sheet is actually easier.

Insert an additional text/number column. I will call it [Row #] for this example (it can be hidden after setting up if desired).

In row 1 of this column you would enter the following and then dragfill it down for the remaining rows. Autofill should grab it and keep it updated as new forms are submitted.

=COUNTIFS(Assessments$1:Assessments@row, OR(@cell = "", @cell <> ""))

This will essentially replicate the row number so that it can be referenced in formulas. We can use this in place of the dates to tell the formula to only sum rows that were entered before the current one which would be row numbers less than or equal to the current row. This would look something like...

=INDEX({Inventory Beginning Count}, MATCH(Assessments@row, {Inventory Assessment Full Range}, 0)) - SUMIFS([Assessments Used]:[Assessments Used], Assessments:Assessments, Assessments@row, [Row #]:[Row #], @cell <= [Row #]@row) + SUMIFS([Order Received]:[Order Received], Assessments:Assessments, Assessments@row, [Row #]:[Row #], @cell <= [Row #]@row)

Deanna Vandermeer

Great idea, I love "helper" columns. Thank you so very much.

Paul Newcome

Happy to help! 👍️