2

Good day folks, we have a Google Sheet with training attendance. We are meeting on weekly basis and calculate attendance from the past four months, last 36 month and historical total.

Situation: Currently, we calculate the attendance by assigning 2 (double training)/1 (normal training)/0 (canceled training) to the value of the training and attendee (2 on double, 1 on normal, 0 when absent) - and get attendance percentage by comparing the person's points sum to that of the trainings during the given period (4 months, 36 months, total).

Issue: The problem is that every time we add a training (new row in the tab), we need to manually change date range in the 4 month calculation and end date in the 36 months. I would love to automate this process that every time we add a new column, we would not have to change the date range.

enter image description here

Cells:

  • C4 = maximum points available per trainings in the last 18 months
  • D4 = maximum points available per trainings in the last 4 months
  • C5 and below = user's attendance in the past 18 months (his points generated in that timespawn/total available points*100)
  • D5 and below = user's attendance in the past 4 months (his points generated in that timespawn/total available points*100)
  • E3:3 = cells with dates when we have trainings
  • E4:4 = points awarded per that specific training
  • E5:5 and below = points gained by the member per attendance

Question: is there any way count a number of training points gained in the past 4/18 months by a specific person?

Thank you kindly

player0
  • 124,011
  • 12
  • 67
  • 124
Majk Šenkeřík
  • 23
  • 4
  • Many calculations can be structured like sum(a:a) to automatically include any cell with data. – Solar Mike Aug 22 '22 at 09:02
  • are you interested in `TODAY minus 4months` or `lastdate minus 4months` ? share a copy / sample of your sheet with an example of the desired output – player0 Aug 22 '22 at 10:28
  • Basically, I need a formula for (all in one go): 1) check E4:4 for dates which are between A1 (today) and A2 (today-120) -> 2) SUM E5:5 for those columns that are within the span of point 1) -> 3) SUM E6:6 for those columns that are within the span of point 1) -> divide SUM from point 3) by SUM from point 2) and multiply by 100 – Majk Šenkeřík Aug 22 '22 at 11:18

1 Answers1

0

try:

=SUM(FILTER(E5:5; E$3:$3>=TODAY()-120; E$3:$3<=TODAY()))/D$4
player0
  • 124,011
  • 12
  • 67
  • 124