Using Microsoft 365, I'm trying to automate some test analysis sheets. I have students in the first column and then 50 columns after for each persons incorrect answers for each question. However, I'd like to use the same sheet for multiple tests with different number of test questions for each. So I only want to COUNTBLANK for number of test questions which is inputted in a separate cell (BE6 in my example). If the test is only 20 questions, I only want to count the number of blanks in D5:W5 and so on.
I have a working formula for the complete sheet;
=IF($BE$4="","",(IF(C5="","",COUNTBLANK($D5:$BA5)/50)))
But this is not dynamic. I tried this;
=IF($BE$4="","",(IF(C7="","",COUNTBLANK($D7:$BA7)/$BE$5)))
But it calculated the percentage off of all 50 columns.
You can use the following formular
=IF($BE$4="","",(IF(C7="","",COUNTBLANK(INDIRECT(ADDRESS(ROW(D7),COLUMN(D7))):INDIRECT(ADDRESS(ROW(D7),COLUMN(D7)+$BE$5-1)))/$BE$5))
to access a dynamic range of cells.