Below I'm showing a sample of the spreadsheet I'm working with. I'm looking for a formula that
answers the Answer 2 column. I've already successfully created a formula that answers Answer 1.
In the Answer 1 column I capturing the column heading where the first "A" absence appears.
The true column heading I've highlighted in blue. I included a heading over that row just in case
I need to reference that to compare to the days the student attends class.
Answer 2 is a lot more trickier because I have to honor the days of the week each student attends the class.
In Answer 2 I need to capture the column where the first blank appears after the first "A" absence
while honoring the days of the week the student attends the class. In my table below I've answered
all the Answer 2 to show what needs to be the result.
As always any help with this will be greatly appreciated
not sure why all the grid lines are showing.. there are showing before I hit the post button.
<tbody>
</tbody>Sheet1
answers the Answer 2 column. I've already successfully created a formula that answers Answer 1.
In the Answer 1 column I capturing the column heading where the first "A" absence appears.
The true column heading I've highlighted in blue. I included a heading over that row just in case
I need to reference that to compare to the days the student attends class.
Answer 2 is a lot more trickier because I have to honor the days of the week each student attends the class.
In Answer 2 I need to capture the column where the first blank appears after the first "A" absence
while honoring the days of the week the student attends the class. In my table below I've answered
all the Answer 2 to show what needs to be the result.
As always any help with this will be greatly appreciated
not sure why all the grid lines are showing.. there are showing before I hit the post button.
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | W | H | F | M | T | W | H | F | M | T | W | H | F | M | T | W | H | F | ||||
| 2 | days of week | 3 | 4 | 5 | 8 | 9 | 10 | 11 | 12 | 15 | 16 | 17 | 18 | 19 | 22 | 23 | 24 | 25 | 26 | Answer 1 | Answer 2 | |
| 3 | Student 1 | M W | A | A | A | A | A | A | 3 | 22 | ||||||||||||
| 4 | Student 2 | T H | A | A | A | A | A | 9 | 23 | |||||||||||||
| 5 | Student 3 | H | A | A | 4 | 18 | ||||||||||||||||
| 6 | Student 4 | M T W H F | A | 10 | 11 | |||||||||||||||||
| 7 | Student 5 | W | A | A | A | 3 | 24 | |||||||||||||||
| 8 | Student 6 | H | A | A | A | 4 | 11 | |||||||||||||||
| 9 | Student 7 | M W F | A | A | A | A | A | A | 5 | 17 | ||||||||||||
| 10 | Student 8 | T | A | A | 9 | 16 | ||||||||||||||||
| 11 | Student 9 | M | A | 8 | 15 |
<tbody>
</tbody>
Last edited:
