Hello,
In my tables of scores, I sometimes need to know how many times each student had scores that were below or above a particular score or within a certain range. (It's a growing table such that each week I add a new column at where column B is.) All weeks are indicated per column by the Friday of that week.
I came up with this formula below that counts the scores meeting the criteria for each student. However, this formula scans the entire row:
=COUNTIFS(5:5,">80",5:5,"<100")
And if I specify a range, then I have to manually update the formulas each week because of addition of a new column each week:
=COUNTIFS(B5:AF5,">80",B5:AF5,"<100")
Given that my columns have the week identifier, is there any way I can specify a date range for my formula such that the beginning week of the range is the "current week" and the end week of the range is a week number I specify in a cell as shown below? In this example, I want the formula to scan 20 weeks starting from the current week:
<tbody>
</tbody>
Thanks a lot for any input!
In my tables of scores, I sometimes need to know how many times each student had scores that were below or above a particular score or within a certain range. (It's a growing table such that each week I add a new column at where column B is.) All weeks are indicated per column by the Friday of that week.
I came up with this formula below that counts the scores meeting the criteria for each student. However, this formula scans the entire row:
=COUNTIFS(5:5,">80",5:5,"<100")
And if I specify a range, then I have to manually update the formulas each week because of addition of a new column each week:
=COUNTIFS(B5:AF5,">80",B5:AF5,"<100")
Given that my columns have the week identifier, is there any way I can specify a date range for my formula such that the beginning week of the range is the "current week" and the end week of the range is a week number I specify in a cell as shown below? In this example, I want the formula to scan 20 weeks starting from the current week:
begin: | current week | ||||
end: | 20 | ||||
8/10/2018 | 8/3/2018 | 7/27/2018 | 7/20/2018 | etc. | |
student1 | 87 | 91 | 72 | 79 | |
student2 | 98 | 97 | 90 | 92 | |
student3 | 8 | 73 | 65 | 82 | |
etc. |
<tbody>
</tbody>
Thanks a lot for any input!