0

I am looking for a formula that would automatically average their own row on C3 to C10 based on the last past 10 days from the current day as seen on their right side. The list doesn't have the weekend because I only need the Monday-Friday work days in the formula.

I've been trying different formulas but the date formula knocks me out

The one I have only counts the last inserted data so it does not depend on the date and it's not accurate for a more "recent" past 10-day data average.

=iferror((sum(INDIRECT(ADDRESS(row(),(max(filter(column(D3:3),len(D3:3)))))):indirect(ADDRESS(row(),(max(filter(column(D3:3),len(D3:3))))-9))))/10,"-0-")

Here's my trial with the layout

I feel that it is possible but I'm out of ideas. Hope someone can help with some solutions?

Improve this question

1 Answer 1

Reset to default
0

Since you only have 8 entries, I didn't go for a column-length array formula. I replaced your C3 formula with the following and dragged down:

=ArrayFormula(SUM(QUERY(TRANSPOSE({$D$2:$2;D3:3}),"Select Col2 Where Col1 > date '"&TEXT(TODAY()-14,"yyyy-mm-dd")&"'"))/10)

What this does is form a virtual array between the curly brackets {} of the static date row with the underneath data for the current row. These two rows are turned to columns in memory with TRANSPOSE. Then a QUERY is performed on this two-column array to limit the array only the data matching two weeks ago or greater. This limited data set is then SUMmed and divided by 10 to get your final average.

Improve this answer
4
  • Thank you very much! This works perfectly! Your explanation is very clear and I'm learning a lot just from this formula.
    – user2214013
    Commented Sep 16, 2020 at 10:59
  • I'm wondering, is there a way to make an IF condition that checks if there is a data inserted on future days, the last 10 day average would end there instead of the current date?
    – user2214013
    Commented Sep 16, 2020 at 11:13
  • I'm not sure I understand your goal. You have cells under every date that are null (empty); so how would one know where you wanted the count-backward-from date to start? However, the nature of this site and others like it is to make it easy for future users to find answers to their own similar questions. For that reason, answering extension or offshoot questions here in comments is discouraged. I would suggest that you start a new post. In that post, link your sheet again, say that you got help on the first formulas, and pose your new question clearly with an example. I'll keep an eye out.
    – Erik Tyler
    Commented Sep 17, 2020 at 6:58
  • I see. I think this would work for now and I'll make another post if I need help on another formula. Thank you so much for the help!
    – user2214013
    Commented Sep 17, 2020 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.